Searching fields from a form

[philljp390]

(I had posted this in the forms section, but its more of a VBA problem)

Im using a search from the sample database:

http://www.access-programmers.co.uk...ead.php?t=62853

I have adapted one form so that it has 2 text boxes:

txtDetails and txtCity

The current code, which works, is this:

If Not IsNull(Me.txtDetails) Then
strWhere = strWhere & " (tblInfo.Company) Like '*" & Me.txtDetails & "*' AND"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " (tblInfo.City) Like '*" & Me.txtCity & "*' AND"
End If

However, I would like txtDetails to search more than one field. Something along the lines of:

If Not IsNull(Me.txtDetails) Then
strWhere = strWhere & " (tblInfo.Company) OR (tblInfrganisation) Like '*" & Me.txtDetails & "*' AND"
End If

This does not work, but hopefully someone can suggest a solution?

 

[Brianwarnock]

I haven't tried this and VBA is not my strong point,what is , but I think that you will have to separate your search from the build of strWhere something like below

strtemp = strWhere & " (tblInfo.Company) Like '*" & Me.txtDetails & "*' "

If IsNull(strtemp) Then
strtemp = strWhere & " (tblInfrganisation) Like '*" & Me.txtDetails & "*' "

strWhere ="strtemp AND"

Hope this helps

Brian

 

[philljp390]

I managed to work out the solution here

strWhere = strWhere & " (tblInfrganisation) Like '*" & Me.txtDetails & "*' And " & " (tblInfo.Town) Like '*" & Me.txtTown & "*' OR " & " (tblInfo.Company) Like '*" & Me.txtDetails & "*' And " & " (tblInfo.Town) Like '*" & Me.txtTown & "*' AND "

The only bug I can see now it that when the search button is clicked and someone presses escape, it comes up with:

"Run-Time error '2501'"

The OpenForm action was canceled.

Just need some code on that button to stop the query when escape is pressed. Suggestions?