Search code examples
vbams-access

Creating a Search Form using a Combo Box in ms-Access

Goal: I want to create a custom form to filter data from a table. I have a combo box which is used to select filter criteria and then generate a subform with the desired filtered data.

Here is my code for populating the subform with the filter criteria only if a specific field matches my selected combo box value.

Private Sub comboBox_selection_AfterUpdate()
Dim Filter_Function As String
Filter_Function = "SELECT * FROM mainTable WHERE ([Specific_Field] = " & Me.comboBox_selection & ")"
Me.Some_subform.Form.RecordSource = Filter_Function
Me.Some_subform.Form.Requery
End Sub

Results: When I select criteria from the combo box, I receive the message

"Syntax error (missing operator) in query expression '([Specific_field] = comboBox_selection)'"

When I try to debug the code, it highlights the line

Me.Some_subform.Form.RecordSource = Filter_Function

with a yellow arrow pointing to it.

Additional Info:

  1. The table that is used to generate the combo box values comes from a different table "Another_Table", not the mainTable
  2. The mainTable field that I am using as the field to check the matching combo box selection is also a combo box pulling options from the same "Another_Table"

I am very new to VBA and coding in general, so I can tell that the error is probably something basic that I just have not learned yet. I triple checked all my spelling and punctuation, so I am quite confident that is not the issue (unless there is some weird rules saying I cannot capitalize my table/form names).

I tried to find the answer on my own for the last couple of days, but failed.

If someone could point me in the right direction, It would be much appreciated.

Solution

  • As @June7 suggested you can try FilterOn property. I would also suggest you to use after_update event.

    Dim Filter_Function As String

Filter_Function = "[Specific_Field] ='" & Me.cboSearch & "'"

Forms![MyFormName]![Some_subform].Form.FilterOn = False
Forms![MyFormName]![Some_subform].Form.Filter = Filter_Function
Forms![MyFormName]![Some_subform].Form.FilterOn = True