Using filter on sub form

Question

I'm trying to use filter on sub form. I have a combo box on the main form.

If Name is selected in the combo box and hit search button I want the respective data to be displayed in the sub form.

If I set the row source of the combo box as SELECT [table].[name] FROM table, the values are not displayed in the combo box but I get desired data in subform using 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 row source of the combo box to SELECT [table].[Id], [table].[name] FROM table, the values in combo box are displayed but I don't get the desired result in the sub form.

I tried changing the filter to .Filter = "[Id]='" & CustName & "'", but there was no change in the result. Any suggestion is very much appreciated.

Have you tried running another query to obtain the name? Something like Select name From table Where Id = CustName, because the value you have in your CustName is the Id. — EngJon
The simplest hack would be to change the row source of the combo box to be: SELECT [table].[name], [table].[name] FROM table - yes same column twice. The combo box seems to be configured to be using the first column as the comboname.value and the second column as what's displayed. An alternative would be to look at the properties of the combo box, there may be a column width property that sets the first column to 0, so it doesn't show. It might look something like "0;2.5" or "0,2.5" or ",2.5" or similar. — GregHNZ
Changed to SELECT [table].[name], [table].[name] FROM table & it worked!!! Thanks!! — Emi

dbWizard dbWizard · Accepted Answer · 2016-01-28T19:05:06

OK, your combo box has two columns defined, so your query:

SELECT ID, [Name] FROM table is fine

But to use that syntax, you need to refer to the 2nd column (zero based) of the combo box.

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

Using filter on sub form

1 Answers