Capture information from Combo Box use as Filter

[Babs W]

I am using Access 97. I want to display a combo box on of form. When a
selection is made I want to use the combo box value to filter a form's
records. The form is based on a query. I have figured out a way to do it
with command buttons, however, the data list is more than 50 items long and
grows as the database is used.

The option I have used is a Macro applied to command buttons. I assume that
what I want to do is best done with VBA. What I don't understand is how
to get the value of the Combo Box into VBA and then apply it to the form.
The combo box displays words (like Plumber, Electrician, Mechanic) but holds
the numeric values for the records.

Ideally I would like the data to display on the same form as the combo box
but not until AfterUpdate on the combo box selection.

Thanks for the help

 

[Emilia Maxim]

I am using Access 97. I want to display a combo box on of form. When a
selection is made I want to use the combo box value to filter a form's
records. The form is based on a query. I have figured out a way to do it
with command buttons, however, the data list is more than 50 items long and
grows as the database is used.

The option I have used is a Macro applied to command buttons. I assume that
what I want to do is best done with VBA. What I don't understand is how
to get the value of the Combo Box into VBA and then apply it to the form.
The combo box displays words (like Plumber, Electrician, Mechanic) but holds
the numeric values for the records.

Ideally I would like the data to display on the same form as the combo box
but not until AfterUpdate on the combo box selection.

Babs,

here's some code (beware, untested!). If you use the AffterUpdate
event of the combo, the filter will be applied automatically right
after choosing a value from the combo. Another solution would be to
apply the filter by pressing a command button. Then you would place
the code in the button's OnClick event procedure.

Private MyCombo_AfterUpdate()

Dim strFilter As String

'Check if the combo is empty
If IsNull(Me![MyCombo]) Then
'No value chosen, so reset the form filter
Me.FilterOn = False
'...and delete the former filter
Me.Filter = ""
Else
'A value was chosen, so put together the filter
strFilter = "[MyFormFieldWithNumericValue]=" & Me!MyCombo

'Set the form's filter
Me.Filter = strFilter

'Apply the filter
Me.FilterOn = True
End If

End Sub

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Barbara]

Thanks for the code. I think it might work, though I just figured out that
my original subform idea works when the link is set correctly.

BW

|
| >I am using Access 97. I want to display a combo box on of form. When a
| >selection is made I want to use the combo box value to filter a form's
| >records. The form is based on a query. I have figured out a way to do
it
| >with command buttons, however, the data list is more than 50 items long
and
| >grows as the database is used.
| >
| >The option I have used is a Macro applied to command buttons. I assume
that
| >what I want to do is best done with VBA. What I don't understand is
how
| >to get the value of the Combo Box into VBA and then apply it to the form.
| >The combo box displays words (like Plumber, Electrician, Mechanic) but
holds
| >the numeric values for the records.
| >
| >Ideally I would like the data to display on the same form as the combo
box
| >but not until AfterUpdate on the combo box selection.
|
| Babs,
|
| here's some code (beware, untested!). If you use the AffterUpdate
| event of the combo, the filter will be applied automatically right
| after choosing a value from the combo. Another solution would be to
| apply the filter by pressing a command button. Then you would place
| the code in the button's OnClick event procedure.
|
| Private MyCombo_AfterUpdate()
|
| Dim strFilter As String
|
| 'Check if the combo is empty
| If IsNull(Me![MyCombo]) Then
| 'No value chosen, so reset the form filter
| Me.FilterOn = False
| '...and delete the former filter
| Me.Filter = ""
| Else
| 'A value was chosen, so put together the filter
| strFilter = "[MyFormFieldWithNumericValue]=" & Me!MyCombo
|
| 'Set the form's filter
| Me.Filter = strFilter
|
| 'Apply the filter
| Me.FilterOn = True
| End If
|
| End Sub
|
| HTH
|
| Best regards
| Emilia
|
| Emilia Maxim
| PC-SoftwareService, Stuttgart
| http://www.maxim-software-service.de