Search method for a form

[fdespoux]

I have a search form that searches for the criteria selected. I'm want to get the search method to look into multiple textboxes instead of just one. Please help I'm fairly new to Access/VBA. My code is the following but it does not work when a certain tool which is in [tool2] or {tool3] etc. It just seems like it only looks at [tool1] and if it does not find it there it just stops there.

Private Sub cmdFind_Click()
Dim varwhere As Variant
Dim rst As DAO.Recordset
    'Initialize to Null
    varwhere = Null
 
If IsNull(Me![Keyword]) Or (Me![Keyword]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criteria!"
        Me![Keyword].SetFocus
        Exit Sub
End If
 
            If SortBy = "Tools" Then
             varwhere = (varwhere + " AND ") & "[tools1] & [tool2] & [tool3] LIKE '" & Me.Keyword & "*'"
                    Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM customers WHERE " & varwhere)
                            If rst.RecordCount = 0 Then
                                Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM customers WHERE " & varwhere)
                                MsgBox "No Access Card Found.", vbInformation, gstrAppTitle
                                rst.Close
                                Set rst = Nothing
                                Exit Sub
                            End If
 
                    rst.MoveLast
                    DoCmd.OpenForm "searchresults", WhereCondition:=varwhere
                    Forms!Searchresults.SetFocus
                    DoCmd.Close acForm, Me.Name
                    'Clean up recordset
                    rst.Close
                    Set rst = Nothing
                    Exit Sub
            End If

[Scott Price]

First of all, please use the [code] tags provided! Simply select your code and click the # icon on the top of the reply window. You are unlikely to get any help at all if you simply post a block of code without the tags.

Second: take a careful look at line 14. If you notice, you have spelled [tools1] and [tool2] and [tool3]. Check to make absolutely sure that you have spelled them correctly, also, what are they? Text box controls, variables?? If they are text box controls, you will notice that the code highlighter indicates they are being treated as part of a string... You'll need to split them out from the string if you are trying to capture values from text boxes on the form.

Regards,
Scott

[NeoPa]

Subscribing ...

[fdespoux]

Private Sub cmdFind_Click()
Dim varwhere As Variant
Dim rst As DAO.Recordset
    'Initialize to Null
    varwhere = Null
 
If IsNull(Me![Keyword]) Or (Me![Keyword]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criteria!"
        Me![Keyword].SetFocus
        Exit Sub
End If
 
If SortBy = "Access Card" Then
                    varwhere = (varwhere + " AND ") & "[tool1] & [tool2] & [tool3] LIKE '" & Me.Keyword & "*'"
                    Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM customers WHERE " & varwhere)
                            If rst.RecordCount = 0 Then
                                Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM customers WHERE " & varwhere)
                                MsgBox "No tools found.", vbInformation, gstrAppTitle
                                rst.Close
                                Set rst = Nothing
                                Exit Sub
                            End If
 
                    rst.MoveLast
                    DoCmd.OpenForm "searchresults", WhereCondition:=varwhere
                    Forms!Searchresults.SetFocus
                    DoCmd.Close acForm, Me.Name
                    'Clean up recordset
                    rst.Close
                    Set rst = Nothing
                    Exit Sub
            End If

Sorry about that im new to this i don't really have much knowledge of how to post. Regarding the textboxes they are in a table called customers and basically we enter what kind of tool the customer has. If i just search for just one variable like [tool1] then the search works perfectly but not always is going to be in [tool1] it could be in [tool2] or [tool3] etc. What i would is for the search method to look at all of these fields if possible and not just one textbox.

[NeoPa]

Your WHERE clause needs to be of the form :

[Tools1] Like 'XYZ*' AND [Tools2] Like 'XYZ*' AND [Tools3] Like 'XYZ*'

& is a string concatenation character.

[fdespoux]

Your WHERE clause needs to be of the form :

[Tools1] Like 'XYZ*' AND [Tools2] Like 'XYZ*' AND [Tools3] Like 'XYZ*'

& is a string concatenation character.

Thank you so much for your help. I really appreciate it, to think that i have spent hours and hours trying to figure this out. Finally it works. Once again i thank you so much.

[NeoPa]

No worries.

There is an article (Finding Jet SQL Help) that you may find useful for similar issues. The Help's not easy to find but it does have a lot of very useful information in it.