Clever Geek Handbook

Using a filter on a subform

I am trying to use a filter on a subform. I have a combo box on the main form.

If I selected a name in the combo box and click the search button, I want the matching data to appear in the substring.

If I set the source of the row in the combo box as SELECT [table].[name] FROM table

, the values ​​are not displayed in the combo box, but I get the desired data in the subform using the following code.

Private Sub SearchName_Click()
    If IsNull(Me.ComboName.Value) Then
        Me.SubList.Visible = False
        MsgBox "Please select name。", vbOKOnly + vbCritical
    Else
         Me.SubList.Visible = True
         Call frm_Enter

    End If End Sub

Private Sub frm_Enter()
Dim CustName As String
CustName = Me.ComboName.Value
With Me.SubList.Form
    .Filter = "[name]='" & CustName & "'" 
    .FilterOn = True
End With
End Sub

If I change the source of the row in the combo box to SELECT [table].[Id], [table].[name] FROM table

, the values ​​in the combo box are displayed, but I don't get the desired result in the subform.

I tried changing the filter to .Filter = "[Id]='" & CustName & "'"

, but the result didn't change. Any suggestion is greatly appreciated.

+3


source to share


1 answer


OK, the combo box has two columns, so your query is:

SELECT ID, [Name] FROM table is fine


But to use this syntax, you need to refer to the second column (base zero) in the combo box.

.Filter = "[name]='" & me.comboname.column(1) & "'"
0


source






More articles:


All Articles