Multi-Field Search Using a Combo Box

**amkp711** · 03-02-2015, 11:59 AM

Hello!

I am currently working on enabling a combobox search on one of my forms [frm_Participant Info Quick View]. The complication on this is that I am hoping to allow users to specify which of 4 fields ("DB ID", "Study ID", "Last Name" and "First Name") they would like to search for a given value. Currently, I have this set up via two combo boxes and VBA code to define how they interact.

The first box, Me.Search_In, is a simple list of the four fields they can select ("DB ID", "Study ID", "Last Name" and "First Name"). The second box, Me.Search_For, is set to display all values for these 4 fields from [tbl_Participant Info]. The idea is that if you select "DB ID" in Search_In then Search_For will search for participant with a given DB ID. On the other hand, if you select "First Name" in Search_In, Search_For will search by First Name.

The issue I am having is that only the DB ID option works. For all the others, I get an error saying that the value in Search_For "isn't an item in the list" even though I know that it is.

My code is as follows:

Code:

Private Sub Search_For_AfterUpdate()
    
    Dim rs As DAO.Recordset


    If Not IsNull(Me.Search_For) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
            If Me.Search_In = "DB ID" Then
                rs.FindFirst "[DB ID] = " & Me.Search_For.Column(0)
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
            ElseIf Me.Search_In = "Study ID" Then
                rs.FindFirst "[Study ID] = " & Me.Search_For.Column(1)
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
            ElseIf Me.Search_In = "Last Name" Then
                rs.FindFirst "[LName] = """ & Me.Search_For.Column(2) & """"
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
            ElseIf Me.Search_In = "First Name" Then
                rs.FindFirst "[FName] = """ & Me.Search_For.Column(3) & """"
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
        End If
        
    End If


End Sub


Private Sub Search_In_AfterUpdate()


If Me.Search_In = "DB ID" Then
    Me.Search_For.BoundColumn = 1
ElseIf Me.Search_In = "Study ID" Then
    Me.Search_For.BoundColumn = 2
ElseIf Me.Search_In = "Last Name" Then
    Me.Search_For.BoundColumn = 3
ElseIf Me.Search_In = "First Name" Then
    Me.Search_For.BoundColumn = 4
End If


End Sub

I do not know if this is the best way to accomplish my goal, so please let me know if you have a better method. Thank you so much!!!!

**June7** · 03-02-2015, 12:30 PM

I have a similar setup.

Combobox 1 is list of fields.

Combobox 2 is list of values that can be found in the field selected in combobox 1.

I have code behind combobox 2 that modifies the RowSource dependent on value selected in combobox 1.

**amkp711** · 03-02-2015, 12:50 PM

Can you give me some more advice with that. I think my issue is that 2 of the fields users can choose are number type (IDs) and 2 are text (names). I tried entering code to change the record source as you suggested and I am now getting a type mismatch error.

The code I have now is as follows:

Code:

Private Sub Search_For_AfterUpdate()
    
    Dim rs As DAO.Recordset


    If Not IsNull(Me.Search_For) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
            If Me.Search_In = "DB ID" Then
                rs.FindFirst "[DB ID] = " & Me.Search_For
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
            ElseIf Me.Search_In = "Study ID" Then
                rs.FindFirst "[Study ID] = " & Me.Search_For
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
            ElseIf Me.Search_In = "Last Name" Then
                rs.FindFirst "[LName] = """ & Me.Search_For & """"
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
            ElseIf Me.Search_In = "First Name" Then
                rs.FindFirst "[FName] = """ & Me.Search_For & """"
                If rs.NoMatch Then
                    MsgBox "Record Not Found. Please Search Again."
                    Me.Search_For = ""
                Else
                    'Display the found record in the form.
                    Me.Bookmark = rs.Bookmark
                    Set rs = Nothing
                End If
        End If
        
    End If


End Sub


Private Sub Search_In_AfterUpdate()


If Me.Search_In = "DB ID" Or "Study ID" Then
    Me.Search_For.RowSource = "SELECT [tbl_Participant Info].[DB ID], [tbl_Participant Info].[Study ID] FROM [tbl_Participant Info] ORDER BY [Study ID] DESC;"
ElseIf Me.Search_In = "Last Name" Or "First Name" Then
    Me.Search_For.RowSource = "SELECT [tbl_Participant Info].[LName], [tbl_Participant Info].[FName] FROM [tbl_Participant Info];"
End If

**June7** · 03-02-2015, 02:07 PM

Which line errors?

The RowSource still has multiple columns so you will need to reference column index.

The If would have to be:

If Me.Search_In = "DB ID" Or Me.Search_In = "Study ID" Then

or

If Me.Search_In Like "*ID" Then

Consider:

RowSource for Search_In
"[DB ID]"; "DB ID"; "[Study ID]"; "Study ID"; ["LName]"; "Last Name"; "[FName]"; "First Name"
ColumnCount: 2
ColumnWidths: 0";1.0"

Code to set RowSource for Search_For
Me.Search_For.RowSource = "SELECT " & Me.Search_In & " FROM [tbl_Participant Info] ORDER BY " & Me.Search_In & IIf(Me.Search_In LIKE "*ID", " DESC", "") & ";"

Then the recordset search
If Me.Search_In Like "*ID" Then
rs.FindFirst Me.Search_In & " = " & Me.Search_For
Else
rs.FindFirst Me.Search_In & " = '" & Me.Search_For & "'"
End If

**amkp711** · 03-03-2015, 08:17 AM

June7, Thanks so much for your help!!! I am trying to take you suggestions and put in the updated code. It seems to be still coming up with errors though.

When I search for an *ID the debugger marks a type mismatch on:

Code:

rs.FindFirst Me.Search_In & " = '" & Me.Search_For & "'"

When I search for a *Name I still get the same error as before...

The full code is now as follows:

Code:

Private Sub Search_In_AfterUpdate()


Me.Search_For.RowSource = "Select " & Me.Search_In & " FROM [tbl_Participant Info] ORDER BY " & Me.Search_In & IIf(Me.Search_In Like "*ID", " DESC", "") & ";"


End Sub


Private Sub Search_For_AfterUpdate()


 Dim rs As DAO.Recordset


    If Not IsNull(Me.Search_For) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
          If Me.Search_In Like "*ID" Then
                rs.FindFirst Me.Search_In & " = " & Me.Search_For
            ElseIf Me.Search_In Like "*Name" Then
                rs.FindFirst Me.Search_In & " = '" & Me.Search_For & "'"
            End If
            
          If rs.NoMatch Then
                MsgBox "Record Not Found. Please Search Again."
                Me.Search_For = ""
            Else
                Me.Bookmark = rs.Bookmark
                Set rs = Nothing
            End If
     End If
     
End Sub

**June7** · 03-03-2015, 10:07 AM

Search for *ID is hitting the wrong FindFirst.

Step debug. Refer to link at bottom of my post for debugging guidelines.

That code looks good. Did you set Search_In combobox properties as suggested? Code to set Search_For RowSource?

If you want to provide db for analysis, follow instructions at bottom of my post.

Multi-Field Search Using a Combo Box

Thread Tools

Multi-Field Search Using a Combo Box

Similar Threads

Multi field search forms

Multi-field Search

Multi-Field Search Query not working

Multi-value Text Field Search

Multi Field Combo search Form

Posting Permissions