How to filter data/rows in MSFlexgrid 2

[jozino01]

Hi,
I have msflexgrid populated with names and want to filter it based on the first letter.
Is there any other way than just delete whole content and populate it with new data?
I think it's pretty common task but couldn't find any sample yet...

 

[Andrzejek]

If you state of how you populate the msflexgrid, it will be easier to help you.

Show some code, don't be shy.....

Have fun.

---- Andy

 

[BobRodes]

Yes, there is. Use the recordset's Filter property.

 

[BobRodes]

I was just going to say that before I make such an off the cuff pronouncement, I really ought to know how you're populating the grid.

 

[jozino01]

how is populated:

ssql = "SELECT * From Vendors Order by [Name];"
Set rs1 = New ADODB.Recordset
rs1.Open ssql, cn, adOpenKeyset, adLockOptimistic

MSFlexGrid2.Rows = 0

While Not rs1.EOF
MyVendor = rs1("ID") & vbTab & rs1("Name") & vbTab & rs1("Address1") & vbTab & rs1("Address2") & vbTab & rs1("Address3") & vbTab & rs1("Address4") & vbTab & rs1("Address5") & vbTab & rs1("Contact") & vbTab & rs1("Phone") & vbTab & rs1("Fax")
MSFlexGrid2.AddItem MyVendor
MSFlexGrid2.CellAlignment = Left
rs1.MoveNext
Wend

 

[BobRodes]

Ok, so no there isn't, if you do it that way. (Feel free to correct me, Andy!) If you're populating it "manually" as you are, then you have to erase it, apply the filter to your recordset object, and repopulate it by hand.

However, you don't have to redo your recordset. You can just use the filter property.

 

[jozino01]

Thanks. What would be a better way to populate flexgrid (and do filtering)?

 

[Andrzejek]

I agree, Bob.

I would create rs1 once, and then Filter it.
If you have a text box where user can type the first letter of the name, you can:

txtSearch.Text = "N"

rs1.Filter = " [Name] like '" & txtSearch.Text & "%'"

With MSFlexGrid2
    .Clear
    .FormatString = "ID|Name |Address1 ..."
    rs1.MoveLast
    .Rows = rs1.RecordCount
    rs1.MoveFirst
    For i = 1 To .Rows - 1
        .TextMatrix(i ,0) = rst1!ID
        .TextMatrix(i ,1) = rst1!Name
        .TextMatrix(i ,2) = rst1!Address1
        .TextMatrix(i ,3) = rst1!Address2
          ....
        rs1.MoveNext
    Next i
    .CellAlignment = Left
End With

rs1.Filter = ""

(code not tested!)

I like TextMatrix a lot better than AddItem because I am not left with first Row empty at the top of the Grid.

Have fun.

---- Andy

 

[jozino01]

thanks for your help!

 

[Andrzejek]

Actually, this star should go to BobRodes. That was his idea and you can blame him for that , I just showed some code to you.

But, thank you.

Have fun.

---- Andy

 

[jozino01]

OK, one for Bob too

 

[BobRodes]

Your other alternative is to use a data control and bind your grid to it. Most of the time data binding and data controls are not to be recommended in VB6, but if you are only using them to display data I don't find a problem with their use. I would NOT recommend their use in any situation that involves changing data.

If you adopt this way of doing things, you only have to change the filter and refresh the control and your filter will be applied to your grid.