Filter on combobox problem

[Guest]

This is unanswered so I'm reposting it...

I am getting a "Type mismatch in expression" error when I try to
filter-by-selection in a combo box. I suspect the problem may have to do
with that the combobox displays column 2 of the query, the description, but
the bound column is column 1, an autonumber ID which is hidded (width-0").

How can I filter my form based on the data visible in a combobox?

 

[Guest]

If you're trying to do this with Access' built-in filter-by-selection
feature, it cannot be done. You can do it with a little code, though.

Put a new combobox in the form's header section and set it's rowsource to
the same rowsource as the combo in your detail section and format it the same
(0" width on column 1). Leave its ControlSOurce property blank. This will be
an unbound control just used to filter the form.

In the new combo's AfterUpdate event, put this code:
Me.FilterOn = True
If Not IsNull(Me.MyNewHeaderCombo) Then
Me.Filter = "[MyFieldName] = " & Me.MyNewHeaderCombo.Column(0)
Else
Me.Filter=""
End IF

MyFieldName refers to the field that's the control source of the detail
section combobox.

Barry

Barry

 

[Guest]

Run-time error '2770':
The object you referenced in the Visual Basic procedure as an OLE object
isn't an OLE object."

The code:

Private Sub Combo139_AfterUpdate()

Me.FilterOn = True
If Not IsNull(Me.Combo139) Then
Me.Filter = "[Company] = " & Me.Combo139.Column(0)
Else
Me.Filter = ""
End If

End Sub

The yellow highlight in debug mode is on the "Me.Filter = "[Company]... line.
What am I missing, Barry?

If you're trying to do this with Access' built-in filter-by-selection
feature, it cannot be done. You can do it with a little code, though.

Put a new combobox in the form's header section and set it's rowsource to
the same rowsource as the combo in your detail section and format it the same
(0" width on column 1). Leave its ControlSOurce property blank. This will be
an unbound control just used to filter the form.

In the new combo's AfterUpdate event, put this code:
Me.FilterOn = True
If Not IsNull(Me.MyNewHeaderCombo) Then
Me.Filter = "[MyFieldName] = " & Me.MyNewHeaderCombo.Column(0)
Else
Me.Filter=""
End IF

MyFieldName refers to the field that's the control source of the detail
section combobox.

Barry

Barry

This is unanswered so I'm reposting it...

I am getting a "Type mismatch in expression" error when I try to
filter-by-selection in a combo box. I suspect the problem may have to do
with that the combobox displays column 2 of the query, the description, but
the bound column is column 1, an autonumber ID which is hidded (width-0").

How can I filter my form based on the data visible in a combobox?