MSFlexGrid Question regarding filtering. 1

[wraygun]

MSFlexgrid1 is populated with data from an access database using DAO. I have 1 field named 'ACTIVE' which indicates whether an employee is 'active' or 'inactive' via a 'YES' or 'NO' in the appropriate field. I would like to filter the display in the flexgrid based on a setting in a combo box with these options (All, Active, Inactive).

Any pointers will be greatly appreciated.

I've attempted to loop through the 'ACTIVE' column and determine which ones contained a 'no' and do a .RemoveItem but I can't seem to get it quite right.

Thanks,
Harold


***You can't change your past, but you can change your future***

 

[wraygun]

This is the solution I came up with. Any comments or constructive criticism are welcome. There may very well be a better way to do this.

Public Sub InActiveEmployees()

Data1.Refresh
MSFlexGrid1.Refresh
Dim Cnt As Integer
For Cnt = MSFlexGrid1.Rows - 1 To 1 Step -1
With MSFlexGrid1
.Row = Cnt
.Col = 11
If .Text = "Yes" Then .RemoveItem (Cnt)
End With
Next

End Sub

Thanks,
Harold

***You can't change your past, but you can change your future***

 

[VDavid]

wraygun, if you are using a recordset, you can try to use the Filter property. It will look something like:

recordset.Filter = "ACTIVE='YES'"

Hope this helps,

David.

 

[wraygun]

Hi there,

Thanks for the quick reply. I tried using

Data1.Recordset.Filter = "Active='Yes'"

and received this error:

Object Variable or with block Variable not set

Any input will be appreciated.

Thanks again,
Harold

***You can't change your past, but you can change your future***

 

[VDavid]

Sorry for the confusion,
if Data1 is your recordset, the you use

Data1.Filter = "Active='Yes'"

Tell me if it works for you,

David.

 

[wraygun]

Hi again,

Data1 doesn't seem to have a method called .Filter. Here's a little more info to maybe help clarify the situation. I have an access database(sonic.mdb) and table(EmployeeInfo) set at design time to populate a MSflexgrid.(Msflexgrid1)

Please let me know if you need more information.

Thanks again,
Harold

***You can't change your past, but you can change your future***

 

[VDavid]

Ok, the thing is what is Data1?, is a Command object in the DataEnvironment or is a New Recordset object?, also how are you retrieving the data from the database?, with a DataEnvironment and a command, a table?

David.

 

[wraygun]

Data1 is one of the Data controls that shipped with Visual Basic.

I populate the flexgrid by setting(during design time)the databasename property to 'sonic.mdb' and the recordsource to 'EmployeeInfo' (which is simply a table in an access database0. The grid is populated with the appropriate data when I run the application it in the IDE.

Please let me know if need anything else.

***You can't change your past, but you can change your future***

 

[VDavid]

Sorry, my mistake again, I dont work much with the Data control or with DAO (I use the DataEnvironment and ODBC).
Anyway, i look around and the Data1.Recordset.Filter should work fine. But, since the Data1.recordset is a different type of recordset maybe thats the problem.
Give me sometime and i'll get back to you.

David.

 

[VDavid]

Ok, i search and look around and it seems that for some reason the recordset in the Data control doesnt like Filter, but i didnt get that error you talked about.
anyway, I found another way, it's not 100% efficient but it works, see if it this helps you.
Using the Data control, but instead of setting the RecordSource directly to the table(EmployeeInfo) in the database, you can put an SQL string, the first time could be all the records, ie.

Data1.RecordSource = "select * from EmployeeInfo"
Data1.Refresh

then when the user selects the option in the ComboBox, you change the query, for example for 'Active' records

Data1.RecordSource = "select * from EmployeeInfo where Active='Yes'"
Data1.Refresh

You can even set the SQL string in the RecordSource property at design time.
The negative thing is that it requerys the database every time. But if your working locally it doesnt matter.

There most be a way using the Filter or other method, maybe cloning the recordset and working with it, but cant find it. I'll keep looking later.

Hope this helps,

David.