Filtering on Combo Box value

[utzja1]

I have a form that is bound to Table1 and I am using the value from a Combo box to filter the records. The Combo Box is populated with values from a different table.

The SetFilter macro is triggered using the AfterUpdate event. When the macro fires, Access asks for a parameter, so I know it's not getting what it needs to complete the macro action. When I provide a value, the macro works fine and returns the appropriate subset of records. I think the problem might be in the WHERE clause of the SetFilter statement; here is what I have.

[ComboBox value on Form]=[Table1]![Field]

Any ideas? Could it possibly be anything to do with using a different table to source the Combo box?

Many thanks for your consideration.

 

[rzw0wr]

I don't use Macros much.
I do know they don't like to search/filter form table to table.
Try putting the following in the underlying query.

Forms!FormName!ControlName in the field you want to filter.

Dale

 

[utzja1]

Thanks for the feedback.

I gave up on the idea of making that work, went back to writing code and did away with the Combo Box. In its place on the form, I added a text box with a Command button located directly beside it. The User enters the value in the field then clicks the button. The macro launches On Click, validates that the data is in the list, then applies the filter to the source table. It looks like this.

Private Sub FilterData_Click()

Dim NumRecord As Integer

NumRecord = DCount("[DistCode]", "CodesT", "[DistCode] = '" & Forms!FormName!SearchVal & "'")

If NumRecord = 0 Then
MsgBox "Value does not match District in the database. Please check and re-enter."
Forms!FormName!SearchVal = Null
Else
DoCmd.SetFilter WhereCondition:="[DistCode] Like '" & Forms!FormName!SearchVal & "'"
End If

End Sub