CODEWITHSUNDEEP
vbams-accesscombobox
TerrorPenguin
TerrorPenguin

Reputation: 11

Pass value of combobox as field

Apologies if I have the wrong terminology. I am trying to build a form where the user selects the field from a dropdown and then enters search text into a text box. The form should then search the table for records that match the text in the field specified in the combobox.

However what I currently have is not working. I know where the error is but cannot fix it.

The current code is:

Private Sub btn_Search_Click()

Dim strSearchTerm As String
Dim strComboField As String

strComboField = Me!cmb_src

strSearchTerm = "SELECT Models.ID, Models.[Model Name], Models.[Model Brand], Models.[Model Category] " _
    & "FROM Models " _
    & "Where Fields(strComboField) LIKE '*" & Me.txtSearch & "*' " _
    & "ORDER BY Models.[Model Name]; "

Me.sub_ModelList.Form.RecordSource = strSearchTerm
Me.sub_ModelList.Form.Requery

End Sub

I believe the error is in the line

 & "Where Fields(strComboField) LIKE '*" & Me.txtSearch & "*' " _

As replacing Fields(strComboField) with a field name [Model Name] resolves fine.

Any help greatly appreciated

Thanks

Upvotes: 0

Views: 81

Answers (2)

Sergey S.
Sergey S.

Reputation: 6336

Try to use this:

"Where [Model Name] LIKE '*" & Me.txtSearch & "*' " _

or just create base query with WHERE like this:

Where [Model Name] LIKE '* & Forms![frm_YourForm]![cmb_src]  & *'

and requery the form by click on Search button, the rest of VBA code not required

UPDATE: sorry, missed the point about selecting the field for search. In this case WHERE should be

"Where [" & strComboField & "] LIKE '*" & Me.txtSearch & "*' "

After

Me.sub_ModelList.Form.RecordSource = strSearchTerm

additional Requery not required, changing Recodsource automatically requeries the form

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

Shouldn't it be:

strSearchTerm = "SELECT Models.ID, Models.[Model Name], Models.[Model Brand], Models.[Model Category] " _
    & "FROM Models " _
    & "Where [" & strComboField & "] LIKE '*" & Me.txtSearch & "*' " _
    & "ORDER BY Models.[Model Name]; "

Upvotes: 2

Related Questions