Search/find method.

[mmb.nbn]

I have created a user form having around 10 text field using ADO,now i want to search /find the record method,user can enter any data in any of the fields in this form and press search button so that query can be formed using the data entered in the fields and then all records wil be displayed.

[freewilly]

Hi mmb

Here's some code I wrote to form SQL from user input. My form had three text boxes: txtCustomerFilter1, txtCustomerFilter2 and txtCustomerFilter3.
The code below is called by a Find button.

Private Sub Search()
    Dim strSQL As String
    Dim strWhereClause
    Dim strElementPrefix As String
    Dim strElementSuffix As String
    'cache these to make it easier
    strElementPrefix = " Like " & Chr(34) & "*"
    strElementSuffix = "*" & Chr(34)
    
    'if txtCustomerFilter1 has a value in it then add it
    If txtCustomerFilter1 <> "" Then
        strWhereClause = "FieldName" & strElementPrefix & txtCustomerFilter1 & strElementSuffix
    End If
    'if txtCustomerFilter2 has a value in it then add it
    If txtCustomerFilter2 <> "" Then
        If strWhereClause <> "" Then 'Do this incase no value has been placed in txtCustomerFilter1
            strWhereClause = strWhereClause & " Or " 'you could make this And if you wanted.
        End If
        strWhereClause = strWhereClause & "FieldName" & strElementPrefix & txtCustomerFilter2 & strElementSuffix
    End If
    'if txtCustomerFilter3 has a value in it then add it
    If txtCustomerFilter3 <> "" Then
        If strWhereClause <> "" Then
            strWhereClause = strWhereClause & " Or "
        End If
        strWhereClause = strWhereClause & "FieldName" & strElementPrefix & txtCustomerFilter3 & strElementSuffix
    End If
    'Now put it together
    strSQL = "SELECT * FROM tblCustomers "
    If strWhereClause <> "" Then
        strSQL = strSQL & "Where " & strWhereClause
    End If
End Sub

Hope it helps

FW

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

There is an explanation of the kind of thing freewilly posted in the "Further Steps" article in our Database Development FAQs/Tutorials (at the top of this forum). There is a direct link in my signature.