Apply value of a combo box as a filter

  • Thread starter Thread starter HockeyBeast
  • Start date Start date
H

HockeyBeast

i would like to filter through records on a form using a combo box list. I
want the user to be able to select an item in the combo box and have the
records filtered to that value. I am able to do this with a Value List
RowSourceType and a finite RowSource of items. However, I want the RowSource
to be an SQL statement so it will dynamically update as new items are added
to the table. The RowSource works fine. However, when I try to use the
DoCmd.ApplyFilter statement, it pops up a Enter Parameter Value box. Here's
the code I am using with the ApplyFilter command:

Dim stEquipID As Double

stEquipID = Me!MachineFilter

DoCmd.ApplyFilter , "EquipmentID = stEquipID"

Please help! Thanks!
 
i would like to filter through records on a form using a combo box list. I
want the user to be able to select an item in the combo box and have the
records filtered to that value. I am able to do this with a Value List
RowSourceType and a finite RowSource of items. However, I want the RowSource
to be an SQL statement so it will dynamically update as new items are added
to the table. The RowSource works fine. However, when I try to use the
DoCmd.ApplyFilter statement, it pops up a Enter Parameter Value box. Here's
the code I am using with the ApplyFilter command:

Dim stEquipID As Double

stEquipID = Me!MachineFilter

DoCmd.ApplyFilter , "EquipmentID = stEquipID"

Please help! Thanks!
Click to expand...

Don't use the ApplyFilter event, use the Combo Box AfterUpdate event.

Replace ComboName with the actual name of the Combo Box
I'll assume the value of the Combo Box's bound column is a Number
datatype.
All you need is:

Me.Filter = "EquipmentID = " & Me.ComboName
Me.FilterOn = True

However, if in fact EquipmentID is a Text datatype, then use:

Me.Filter = "EquipmentID = '" & Me.ComboName & "'"
Me.FilterOn = True
 
fredg said:
Don't use the ApplyFilter event, use the Combo Box AfterUpdate event.

Replace ComboName with the actual name of the Combo Box
I'll assume the value of the Combo Box's bound column is a Number
datatype.
All you need is:

Me.Filter = "EquipmentID = " & Me.ComboName
Me.FilterOn = True

However, if in fact EquipmentID is a Text datatype, then use:

Me.Filter = "EquipmentID = '" & Me.ComboName & "'"
Me.FilterOn = True
Click to expand...

Thank you for the help! It worked great. I am going to go back through all
my filters and use this technique instead of the DoCmd.ApplyFilter method.
 
I must also show my gratitude here, as I was trying to do this same thing and
it worked perfectly!

One more thing that I would like to incorporate within this same filter, is
to prompt some kind of message box to the user if the filter does not return
any results. Any suggestions?

Thanks in advance.
 
Back
Top