[RESOLVED] Advanced Search on MSFlexGrid vb6

[bibob2011]

Hello sirs

I wants Advanced Search functions on the MMSFlexGrid in vb6.

Currently iam using a combobox to select which Subject i want to use to search.


I want advanced search function.
It should be possible to select which settings i want then only what i want should be listed on the MMSFlexGrid.

currently i am using the folowwing code to search and load the database.

Private Sub cmdFind_Click()
    makeGrid (cmbMode.Text)
    If MSFlexGrid1.TextMatrix(1, 0) = "" Then
        MsgBoxXP "Sorry !!Could not find carpart.", vbCritical, "Error!"
    End If
    txtSearch.SetFocusEx
End Sub

Private Sub makeGrid(qryField As String)
On Error Resume Next
    
    If rs.State = 1 Then
        rs.Close
    End If
    Select Case qryField
        Case "Nr":
            strQry = "SELECT * FROM AddressBook ORDER BY ID"
        Case "Fabrikat":
            strQry = "SELECT * FROM AddressBook WHERE Fabrikat LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Fabrikat"
        Case "Model":
            strQry = "SELECT * FROM AddressBook WHERE Model LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Model"
        Case "Variant":
            strQry = "SELECT * FROM AddressBook WHERE Variant LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Variant"
        Case "Årgang":
            strQry = "SELECT * FROM AddressBook WHERE Årgang '%" & Trim(txtSearch.Text) & "%' ORDER BY Årgang"
        Case "Motor":
            strQry = "SELECT * FROM AddressBook WHERE Motor LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Motor"
        Case "Gear":
            strQry = "SELECT * FROM AddressBook WHERE Gear LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Gear"
        Case "Karosseri":
            strQry = "SELECT * FROM AddressBook WHERE Karosseri LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Karosseri"
        Case "Side":
            strQry = "SELECT * FROM AddressBook WHERE Side LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Side"
        Case "Km":
            strQry = "SELECT * FROM AddressBook WHERE Km LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Km"
        Case "Farve":
            strQry = "SELECT * FROM AddressBook WHERE Farve LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Farve"
        Case "Pris":
            strQry = "SELECT * FROM AddressBook WHERE Pris LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Pris"
        Case "Antal":
            strQry = "SELECT * FROM AddressBook WHERE Antal LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Antal"
    End Select
  
    rs.Open strQry, conn, adOpenKeyset, adLockReadOnly
    If rs.RecordCount < 1 Then
        MSFlexGrid1.Clear
        MSFlexGrid1.Rows = 2
        gridHeader
    Else
        rs.MoveFirst
        MSFlexGrid1.Rows = rs.RecordCount + 1
        rowcnt = 1
        For I = 1 To rs.RecordCount
            MSFlexGrid1.Row = rowcnt
            
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = "" & rs.Fields("ID")
                        
            MSFlexGrid1.Col = 1
            MSFlexGrid1.RowHeight(rowcnt) = 350
            MSFlexGrid1.Text = "" & rs.Fields("Fabrikat")
            
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = "" & rs.Fields("Model")
            
            MSFlexGrid1.Col = 3
            MSFlexGrid1.Text = "" & rs.Fields("Variant")
            
            MSFlexGrid1.Col = 4
            MSFlexGrid1.Text = "" & rs.Fields("&#197;rgang")
            
            MSFlexGrid1.Col = 5
            MSFlexGrid1.Text = "" & rs.Fields("Motor")
            
            MSFlexGrid1.Col = 6
            MSFlexGrid1.Text = "" & rs.Fields("Gear")
            
            MSFlexGrid1.Col = 7
            MSFlexGrid1.Text = "" & rs.Fields("Karosseri")
            
            MSFlexGrid1.Col = 8
            MSFlexGrid1.Text = "" & rs.Fields("Side")
            
            MSFlexGrid1.Col = 9
            MSFlexGrid1.Text = "" & rs.Fields("Km")
            
            MSFlexGrid1.Col = 10
            MSFlexGrid1.Text = "" & rs.Fields("Farve")
            
            MSFlexGrid1.Col = 11
            MSFlexGrid1.Text = Format(rs.Fields("Pris"), "##0.00")

            MSFlexGrid1.Col = 12
            MSFlexGrid1.Text = "" & rs.Fields("Antal")
            
            MSFlexGrid1.Col = 13
            MSFlexGrid1.Text = "" & rs.Fields("Title")
            
            MSFlexGrid1.Col = 14
            MSFlexGrid1.Text = "" & rs.Fields("Note")
            

            rs.MoveNext
            rowcnt = rowcnt + 1
  
        
        Next I
    End If

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long 'MOUSE WHEEL SCROLL

Dim X As Integer 'MOUSE WHEEL SCROLL
Dim topRow As Integer 'MOUSE WHEEL SCROLL
Dim ctl As Control 'MOUSE WHEEL SCROLL
Dim lngResult As Long 'MOUSE WHEEL SCROLL


Dim rs As New ADODB.Recordset

Dim FrameMain As String
Dim FrameNew As String
Dim framePrint As String


Private Sub Main()
    dbConn
    rs.Open "select * from ports", conn, adOpenKeyset, adLockOptimistic
    rs.MoveFirst
    portNo = rs.Fields(0)
    If portNo < 1 Then
     '   frmSetPort.Show 1
    End If
    rs.Close
    Set rs = Nothing
    frmMAIN.Show
End Sub

[RhinoBull]

So, what is your question?

Anyway, in the meantime I would simplify your makeGrid procedure by doing something like this:

Private Sub makeGrid(qryField As String)

    Select Case qryField
        Case "Nr":
            strQry = "SELECT * FROM AddressBook ORDER BY ID"
        Case Else
            strQry = "SELECT * FROM AddressBook WHERE " & qryField & " LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY " & qryField
    End Select
    
    Set rs = Notihng
    Set rs = New ADBDB.Recordset
    rs.Open strQry, conn, adOpenKeyset, adLockReadOnly
    
    If Not rs.EOF Then
        MSFlexGrid1.Rows = 1
        MSFlexGrid1.Cols = rs.Fields.Count
        
        For i = 0 To rs.Fields.Count - 1
            MSFlexGrid1.TextMatrix(0, i) = rs.Fields(i).Name
        Next i
        
        Do While Not rs.EOF
            MSFlexGrid1.AddItem ""
            For i = 0 To rs.Fields.Count - 1
                MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
            Next i
            rs.MoveNext
        Loop
        
        MSFlexGrid1.FixedCols = 0
        MSFlexGrid1.FixedRows = 1
    End If

End Sub

Note: code above was not tested.

[bibob2011]

Hello thank you.

okay look at that eg.

i want to search for a Bmw, 2.0 and from year 2001.
Now i can only search for one thing at time, eg i can search on bmw, but i will then get all other bmw cars, also bmws from 1999(example).

so i want to filter the search by entering eg bmw, then year(2001) and engine 2.0)like this..


i hope it was clear know.

[RhinoBull]

...so i want to filter the search by entering eg bmw, then year(2001) and engine 2.0)like this...

Offer more filters on screen and build sql dynamically based on whether or not particular filter (or texbox, combo, etc) value is provided.

You will have base query (i.e, select * from table1 where ...) to begin with.
You will append each condition for each available filter.
Here mis a very quick sample:

Private Sub makeGrid(Optional f1 As String, Optional f2 As String, Optional f3 As String)
Dim strsql As String

    strsql = "select * from table1 where 1=1"
    
    If Not f1 = "" Then
        strsql = strsql & " and field1 = '" & f1 & "',"
    End If
    
    If Not f2 = "" Then
        strsql = strsql & " and field2 = '" & f2 & "',"
    End If
    
    If Not f3 = "" Then
        strsql = strsql & " and field3 = '" & f3 & "'"
    End If
    
    If Right(strsql, 1) = "," Then strsql = VBA.Left$(strsql, Len(strsql) - 1)
    
    '...

End Sub

Note: f1, f2, f3 are optional values for each field you wish to search (name them accordingly so you don't get confused).

[bibob2011]

oh thank you very much, but i am really not good to program in Databases, please explaing more, please try to use my code and give me just some examples with my code

[RhinoBull]

Well, I suppose you can start learning by reading ADO
In the meantime try implementing something similar to following sample:

Private Sub makeGrid(Optional sFabrikat As String, _
                     Optional sModel As String, _
                     Optional sVariant As String, _
                     Optional sArgang As String, _
                     Optional sMotor As String, _
                     Optional sGear As String, _
                     Optional sKarosseri As String, _
                     Optional sSide As String, _
                     Optional sFarve As String, _
                     Optional sPris As String, _
                     Optional sAntal As String)
'======================================================Dim strQry As String

    strQry = "SELECT * FROM AddressBook Where 1=1"
    
    If Not sFabrikat = "" Then
        strQry = strQry & " And Fabrikat = '" & sFabrikat & "',"
    End If
    If Not sModel = "" Then
        strQry = strQry & " And Model = '" & sModel & "',"
    End If
    If Not sVariant = "" Then
        strQry = strQry & " And Variant = '" & sVariant & "',"
    End If
    If Not sArgang = "" Then
        strQry = strQry & " And Argang = '" & sArgang & "',"
    End If
    If Not sMotor = "" Then
        strQry = strQry & " And Motor = '" & sMotor & "',"
    End If
    If Not sGear = "" Then
        strQry = strQry & " And Gear = '" & sGear & "',"
    End If
    If Not sKarosseri = "" Then
        strQry = strQry & " And Karosseri = '" & sKarosseri & "',"
    End If
    If Not sSide = "" Then
        strQry = strQry & " And Side = '" & sSide & "',"
    End If
    If Not sFarve = "" Then
        strQry = strQry & " And Farve = '" & sFarve & "',"
    End If
    If Not sPris = "" Then
        strQry = strQry & " And Pris = '" & sPris & "',"
    End If
    If Not sAntal = "" Then
        strQry = strQry & " And Antal = '" & sAntal & "'"
    End If

    'you may need to decide which field to use for the Order By clause;
    'perhaps it could be option on screen for users to select (checkbox, option button, dropdown, etc...)    
    
    If Right(strQry, 1) = "," Then strQry = VBA.Left$(strQry, Len(strQry) - 1)
    
    Set rs = Nothing
    Set rs = New ADBDB.Recordset
    rs.Open strQry, conn, adOpenKeyset, adLockReadOnly
    
    If Not rs.EOF Then
        MSFlexGrid1.Rows = 1
        MSFlexGrid1.Cols = rs.Fields.Count
        
        For i = 0 To rs.Fields.Count - 1
            MSFlexGrid1.TextMatrix(0, i) = rs.Fields(i).Name
        Next i
        
        Do While Not rs.EOF
            MSFlexGrid1.AddItem ""
            For i = 0 To rs.Fields.Count - 1
                MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
            Next i
            rs.MoveNext
        Loop
        
        MSFlexGrid1.FixedCols = 0
        MSFlexGrid1.FixedRows = 1
    End If

End Sub

'typical usage - I presume you may need textbox (or combo, etc) for each field you wish to use for your database search
Private Sub Command1_Click()
    makeGrid Trim(txtFabrikat.Text), _
             Trim(txtModel.Text), _
             Trim(txtVariant.Text), _
             Trim(txtArgang.Text), _
             Trim(txtMotor.Text), _
             Trim(txtGear.Text), _
             Trim(txtKarosseri.Text), _
             Trim(txtSide.Text), _
             Trim(txtsFarve.Text), _
             Trim(txtPris.Text), _
             Trim(txtAntal.Text)
End Sub

Because all arguments in the procedure header are optional you don't have to provide all of them but it really doesn't hurt to do so.

[bibob2011]

yes okay it works now, thank you very much (:

[RhinoBull]

In that case please change thread status to "resolved" from the Thread Tools menu.

btw, I'm a bit puzzled how could possibly managed to apply my last sample in 9 (?!) minutes.

[bibob2011]

iam new here, where i do that?
heh niice

[si_the_geek]

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).

[bibob2011]

okay thank you sir

[bibob2011]

hello again

i got a problem again.

in you last code you have posted:

    If Not rs.EOF Then
        MSFlexGrid1.Rows = 1
        MSFlexGrid1.Cols = rs.Fields.Count
        
        For i = 0 To rs.Fields.Count - 1
            MSFlexGrid1.TextMatrix(0, i) = rs.Fields(i).Name
        Next i
        
        Do While Not rs.EOF
            MSFlexGrid1.AddItem ""
            For i = 0 To Fields.Count - 1 <-I have changed Fields.Count with 14 becouse i didnt wants all the rows to be shown. as i have some hidden rows, but it still appear(actually the content of therse tables dont appear when i change to 14) but i dont want the hidden fields to appear in the msflexgrid table.

                MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
            Next i
            rs.MoveNext
        Loop
        
        MSFlexGrid1.FixedCols = 0
        MSFlexGrid1.FixedRows = 1
    End If

another thing was, before i could click on a specific column on the table, and a new windows will appear like that:http://dl.dropbox.com/u/17796755/1.JPG


but when i used that code upstais, now the fields is embty on the new form
http://dl.dropbox.com/u/17796755/2.JPG


I used that code for msflexgrid

Private Sub MSFlexGrid1_Click()
On Error Resume Next
id = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 0)
End Sub

Private Sub MSFlexGrid1_DblClick()
On Error Resume Next

    If id <= 0 Then
        MsgBox "Please choose a carpart", vbCritical, "Basic 1.0"   
        MSFlexGrid1.SetFocus
        Exit Sub
    End If
    frmNew.id = id

    frmNew.Show 1
End Sub


AND THE FORM_ACTIVATE ON THE ANOTHER FORM THAT OPENS IS:
Private Sub Form_Activate()
On Error Resume Next

If rs.State = 1 Then
    rs.Close
End If
rs.Open "select * from AddressBook where ID = " & id, conn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
    fillData
End If


end sub

i hope someone would help

[si_the_geek]

Ouch... I strongly recommend that you read the article What is wrong with using "On Error Resume Next"? from our Classic VB FAQs (in the FAQ forum). The way you have used it is not appropriate, and it should be removed - which might cause VB tell you what the problem is.

In terms of checking if a recordset has got data, you should not use .RecordCount (which is slow and unreliable), instead you should check the .EOF and .BOF properties, eg:

If Not(rs.BOF And rs.EOF) Then
    fillData
End If

Making this change might fix the problem.


If it doesn't work after those changes (and you aren't getting errors), debug to make sure that Form_Activate has the correct value of id. If you don't know how to debug, there is a tutorial about it in the "General" section of our Classic VB FAQs (in the FAQ forum)

[bibob2011]

Og thank you sir, it worked now,

But the problem is, the fields in the new form that opens is not sorted right , so i found out it is becouse that loop function i used it just place the columns automatic in the msflexgrid (but i like that loop becouse it is faster than that one i used before

        
Do While Not rs.EOF
            MSFlexGrid1.AddItem ""
            For i = 0 To 14  'rs.Fields.Count - 1'******************'
                MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
            Next i
            rs.MoveNext
        Loop

i mean before i used that to sort them like what i want

        rs.MoveFirst
        MSFlexGrid1.Rows = rs.RecordCount + 1
        rowcnt = 1
        For i = 1 To rs.RecordCount
            MSFlexGrid1.Row = rowcnt
            
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = "" & rs.Fields("ID")
            
            MSFlexGrid1.Col = 1
            MSFlexGrid1.RowHeight(rowcnt) = 350
            MSFlexGrid1.Text = "" & rs.Fields("Fabrikat")
            
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = "" & rs.Fields("Model")
            
            MSFlexGrid1.Col = 3
            MSFlexGrid1.Text = "" & rs.Fields("Brændstof")

see the picture example please here http://dl.dropbox.com/u/17796755/ega.JPG
the first one is what i want tha columns to be sorted, but now it is the bottom picture that happens, it just sort them automaticly


thanks sir

[si_the_geek]

That loop is only valid if the fields are in the order you want.

There are two ways you could solve it, the first is to get the fields in the correct order, eg:

rs.Open "select ID, Fabrikat, model, ... from AddressBook where ID = "

The other is to use the longer code as in the second half of your post, but instead of using the .Row and .Col and .Text properties, just use .TextMatrix

[RhinoBull]

That loop is only valid if the fields are in the order you want.

That loop is always "valid" - what may not "appear" right is as you said fields order. However, how is it difficult to control it via sql (someone should never use "select * ..." anyway).

[si_the_geek]

Indeed, I certainly wasn't clear there... it would have been better if I'd written "apt for what you want" rather than "valid".

Specifying the field order via SQL is easy enough, and would certainly be my preference.

[bibob2011]

Thank you both of you sirs, iam not good to programming databases :/ but now i have tried to add about 10.000 records and when i search no result appear! , normally if there is some hundred records it works, but now like what i said, when i have tested and added 10.00 0 records the does not search they stops, the vb6 program dont frezze or any thnig, it just dont want to search and no error msg appear? have someone tried that before?

[si_the_geek]

The amount of records is not a problem (databases can search everything they store), which probably means that you are either searching for something (or combination of things) that isn't in the database, or your code is not correct.

[RhinoBull]

Thank you both of you sirs, iam not good to programming databases...

The sooner you stop saying that the better you will do.
Learn from the samples, read tutorials (you can start from reading our very own Database FAQ), books and of course ask questions.

[bibob2011]

Yes RihnoBull i understand you, but it is becouse i started programming and i have never intersted me with database so now i regret that, but you said right i should do that and iam glad for people who help other it is very niice