3

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Emi
  • 484
  • 2
  • 16
  • try selecting any other column than id,issue persist ? – Tharif Jun 30 '15 at 09:42
  • 1
    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 Jun 30 '15 at 09:43
  • 2
    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 Jun 30 '15 at 10:19
  • Changed to `SELECT [table].[name], [table].[name] FROM table` & it worked!!! Thanks!! – Emi Jul 01 '15 at 02:25

1 Answers1

0

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) & "'"
dbWizard
  • 122
  • 8