Filter form on drop down box selection on button click (1 Viewer)

[lotarugg]

Hi, I have a form and I need to be able to filter it from the selection of a drop down list on the form. I don't what it to filter automatically once a selection is made from the list but I need it to filter on button press using the selection from the list and to only show records that include the value selected from the list.

I'm ok linking queries to filter forms linked to a button but having problems understanding how I tell the query to take the value from the selection in the drop down list.

Thanks

 

[boblarson]

In the AFTER UPDATE event of the combo box you would use

If the field is numeric:

Me.Filter = "[FieldNameHere]= " & Me.YourComboBoxNameHere
Me.FilterOn = True

If the field is Text:

Me.Filter = "[FieldNameHere]= " & Chr(34) &  Me.YourComboBoxNameHere & Chr(34)
Me.FilterOn = True

If the field is a Date:

Me.Filter = "[FieldNameHere]= #" & Me.YourComboBoxNameHere & "#"
Me.FilterOn = True

 

[lotarugg]

Thanks Bob for the 3 examples - I should have said it is for a text field (staff names) sorry. I pick the name from the combo and it filters straight away, I need it to filter only when another button is pressed. Is there any way to pipe the data selected from a standard lookup list into an expression or query linked to a filter button beside the list and then only filter the form when the button is pressed? Think I'm out of my depth a bit.

Cheers

 

[boblarson]

Just move it to the Click event of the button from the After Update event of the combo box.

 

[lotarugg]

Ok, I think I have a problem in that the feild I'm focusing on is a lookup feild so it is grabbing the id number rather than the name. Any idea how I shift focus to the name?

 

[boblarson]

Ok, I think I have a problem in that the feild I'm focusing on is a lookup feild so it is grabbing the id number rather than the name. Any idea how I shift focus to the name?

GET RID OF THE LOOKUPS AT TABLE LEVEL!!!!!

See here for why:
http://www.mvps.org/access/lookupfields.htm
Once you do that, then we can continue.

 

[lotarugg]

Thank Bob, hoping there was an easier way. It's a pretty simple database so I've based it on one table with lookups.

Cheers though I'll probably have to restructure it.

 

[DRG]

Bob,
I have a drop down combo box that selects the name from list:
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustName] = '" & Me![Combo89] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
I want to create a filter. Where do I add your code? now I'm doing two steps: first I select the first custname on the list with the above code and then I right click on it to filter it... what shall I do to avoid the second step?
Thank you

 

[boblarson]

Bob,
I have a drop down combo box that selects the name from list:
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustName] = '" & Me![Combo89] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
I want to create a filter. Where do I add your code? now I'm doing two steps: first I select the first custname on the list with the above code and then I right click on it to filter it... what shall I do to avoid the second step?
Thank you

Remove the other code and put this in:

Me.Filter = "[CustName] = '" & Me![Combo89] & "'"
Me.FilterOn = True
End Sub

 

[CanWest]

In the AFTER UPDATE event of the combo box you would use

If the field is a Date:

Me.Filter = "[FieldNameHere]= Between #" & Me.YourTextBoxNameHere & "#" and #" & Me.YourSecondTextBoxNameHere & "#"


Me.FilterOn = True

Would this work for a date range

 

[boblarson]

Would this work for a date range

What do you mean? Two fields?

 

[tharukan]

In the AFTER UPDATE event of the combo box you would use

If the field is numeric:

Me.Filter = "[FieldNameHere]= " & Me.YourComboBoxNameHere
Me.FilterOn = True

If the field is Text:

Me.Filter = "[FieldNameHere]= " & Chr(34) &  Me.YourComboBoxNameHere & Chr(34)
Me.FilterOn = True

If the field is a Date:

Me.Filter = "[FieldNameHere]= #" & Me.YourComboBoxNameHere & "#"
Me.FilterOn = True

Can you please describe how to use this filter for the exact word that in the combo box, not the parts. Because after filter it shows other records that match some letters. Thank you.

 

[tharukan]

Can you please describe how to use this filter for the exact word that in the combo box, not the parts. Because after filter it shows other records that match some letters. Thank you.

Also, I have used it in a button click.

Private Sub Searchdivision_Click()
Me.Filter = "[3_div_office]= " & Chr(34) & Me.divisional & Chr(34)
Me.FilterOn = True

End Sub

 

[bob fitz]

tharukan

This thread is over nine years old and the person helping the OP has not posted here for about three years.

It might be better to start a new thread with your own problem and questions

 

[kokowawa]

in the filter afterupdate the dropdown list i usually use ,

DoCmd.OpenForm "Formname", , , "Fieldname= Forms!Formname!DropDownName", , acNormal