Filter subform by using combo box

Question

I have a combo box on a form that I want to filter a subform (SubSearchMaster_frm).

I am receiving:

Runtime error 3464: data type mismatch in expression.

The code is below:

Private Sub CboNIIN_AfterUpdate()
    Me.SubSearchMaster_frm.Form.Filter = "[NIIN] = " & Me.CboNIIN
    Me.SubSearchMaster_frm.Form.FilterOn = True
End Sub

The subform is a query.

I have also tried:

Private Sub CboNIIN_AfterUpdate()
    Dim sql As String
    sql = "Select * from SubSearchMaster_frm where ([NIIN] = " & Me.CboNIIN & ") From subsearchmaster_frm"
    Me.SubSearchMaster_frm.Form.RecordSource = sql
    Me.SubSearchMaster_frm.Form.Requery
End Sub

But I'm getting an error on that too.

What is the datatype of the NIIN field? And does the bound column of your combobox correspond to this field? — Lee Mac
the datatype is text, but the data is like this 00033422. So the combo box has the datatype as numbers. — hIlary

Lee Mac Lee Mac · Accepted Answer · 2019-12-29T22:50:43

Since you have stated that the NIIN field is of Text datatype, you will need to surround the filter value with single or double quotes else you will receive the familiar datatype mismatch error message.

For example:

Me.SubSearchMaster_frm.Form.Filter = "[NIIN] = '" & Me.CboNIIN & "'"

Without the quotes, a numerical value is being supplied, thus resulting in a data type mismatch.

Filter subform by using combo box

1 Answers