Multiple text boxes and one search

[andrewraharjo]

Folks,

I need help for implementing / new idea how to perform search in access. Here are some descriptions:

1. Goal:
Implement compound search or we can say as multiple box and one search command button. Assume there are three text boxes name txtModelNum, txtEngineer, and txtManager. Let's say the user enters the keyword into these textboxes in order to search the record.

In this form there will be subform that generated from Model Table. This model table contains more in depth information, such as name of the tools, location and etc.

The user will only input those three boxes and once he hits enter the record should show up. This record can be pulled either from the Model table or Model subform.

2. What I've done
I've created small routine / vb script from this tutorial
youtube.com/watch?v=M6XIU5_z-UU

The tutorial seems give better description for what I'm trying to do. However I encountered error while run the search button.

The error I got it's from

Me.Model_search.Form.RecordSource = "SELECT * FROM Model" & BuildFilter

I've tried to use apply filter method but I can't make it better.

3. Snippet of the code

Private Sub CmdSearch_Click()
'On Error GoTo err
Me.Model_search.Form.RecordSource = "SELECT * FROM Model" & BuildFilter
Me.Model_search.Requery
Exit Sub
'err:
' MsgBox err.Description
End Sub

(General)
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"

varWhere = Null

If Me.txtModel = "" Then
varWhere = varWhere & "[ModelNumber] like " & tmp & Me.txtModel & tmp & " AND "
End If

If Me.txtManager = "" Then
varWhere = varWhere & "[Manager] like " & tmp & Me.txtManager & tmp & " AND "
End If

If Me.txtEngineer = "" Then
varWhere = varWhere & "[Engineer] like " & tmp & Me.txtEngineer tmp & " AND "
End If

BuildFilter = varWhere

End Function

4. Question
Is there any better way and easier to implement search from multiple text boxes ? I'm looking for compound search implementation Ms Access example.

If this code wouldn't work, then I believe there is a better solution. Currently I'm in dead end of finding solution for this. Please let me know if you have better idea to implement this.

Any comments, review or help will be highly appreciated

Thanks,
Andy

 

[spikepl]

Too much in one go. Use this recipe: http://www.baldyweb.com/immediatewindow.htm You NEED to see what you do, not just blindly add a lot of stuff together and then just cry when it doesn't work

Once you get the methodology of just doing one bite at a time, then you'll be allright - your code looks fine.

 

[MarkK]

This looks like a good solution. I see three minor problems . . .
1) you need a space here . . .

Me.Model_search.Form.RecordSource = "SELECT * FROM Model " & BuildFilter

. . . see where I added a space? You didn't say what error you get at that line either.

2) You BuildFilter routine, if it returns anything, will return a WHERE clause with a dangling "AND " operator. See this code . . .

varWhere = varWhere & "[Engineer] like " & tmp & Me.txtEngineer tmp & " AND "

See the " AND " at the end? Where does that get removed? Yes, it's essential if there is more than one clause included, but the last one has to be removed.

3) Your SQL is missing the word WHERE in the where clause. Your BuildFilter routine should add this word.

Hope this helps,

 

[Uncle Gizmo]

I did a series of videos recently Building Advanced Search Criteria 0 explaining how to create yourself an advanced search form this video shows how it works and the other links take you to the playlists.

MS Access - Building Search Criteria

MS Access - Building Advanced Search Criteria

 

[andrewraharjo]

This looks like a good solution. I see three minor problems . . .
1) you need a space here . . .

Me.Model_search.Form.RecordSource = "SELECT * FROM Model " & BuildFilter

. . . see where I added a space? You didn't say what error you get at that line either.

2) You BuildFilter routine, if it returns anything, will return a WHERE clause with a dangling "AND " operator. See this code . . .

varWhere = varWhere & "[Engineer] like " & tmp & Me.txtEngineer tmp & " AND "

See the " AND " at the end? Where does that get removed? Yes, it's essential if there is more than one clause included, but the last one has to be removed.

3) Your SQL is missing the word WHERE in the where clause. Your BuildFilter routine should add this word.

Hope this helps,

Yes this is really helpful, I figured out what the problem is. Indeed, that was just simple problem to debug after print out the SQL statement

Thanks folks

 

[MarkK]

Indeed, that was just simple problem to debug after print out the SQL statement

Yes! This is the key! Check the SQL you are producing, and sometimes I'll even copy it into a new query and test it there.
Nice job!