How to use a combo box to filter a forms data

[tmd63]

I have a table that has a Part Number field. I wish to filter a form by using a combo box. I want to have the combo box display the part number of the entry and then filter the item using the part number, adding either <All> and <NEW> to the list to allow selection.
Is this possible? Or do I have to use a separate Part field?

 

[Gasman]

You would need an unbound combo.
Use a union query to add your extra values All being 0 Autonumber
Then in the AfterUpdate event of the combo set the filter accordingly.

 

[theDBguy]

You could start with using the combobox wizard and selecting the third option. I would suggest using two separate buttons to clear the filter (All option) and going to a new record (New option).

 

[Uncle Gizmo]

This solution on my website might be a bit too comprehensive, but it might give you some ideas of how to go about it..

There is a nice little function, which you might be able to employ....

Combo Filter < All> or Some - Nifty Access

Combo Filtering You can set this up yourself by following the instructions below, or you can study the example file which can be downloaded from the Nifty Digital downloads site for a few dollars.. will normally appear as the first entry in your list because the angle bracket puts it at the...

niftyaccess.com

 

[Pat Hartman]

I bind my forms to queries that reference the combo:
Select ...
From ...
Where (MyField = Forms!MyForm!cboMyField OR Forms!MyForm!cboMyField Is Null) Or (MyDate > IIf(Forms!MyForm!NewOnly = True, Date() - 7, #1/1/1900#)

The "OR" part of the first clause handles the "all" part so I don't add anything to the combo. The second clause handles the "new" requirement. I'm assuming a checkbox and hard coded dates. 1/1/1900 seems old enough but use what makes sense. Date() -7 seems new enough but use what makes sense.

Since there is more than one field to consider, I add a search button. In the click event of the search button. I use:
Me.Requery

The form will open to the "all" option since that seems to be what you want. If your BE is SQL Server, this is inefficient so let us know and someone will adjust the criteria so the form opens empty instead.