Access 2003 Filter before displaying table

[Guest]

Inexperienced user but working on fun? database: inventory, 'on order?', 'ID
the recv'd items as orders come in & add to inventory', etc. I hope
eventually to use a switchboard as users won't take time to manually search
database. Starting with "Find Item in Product table". Product table has
both Chemical + non-chemical items, (Y/N field). User chooses Y/N to
filter out the rest. I then need a query to prompt for "search item" in
Product. All non-filtered items should display alpha starting at 1st Name
found so we can still check other item names in Product.
Do I start with a query with the PRODUCT-'Chemical' field only? How can I
then apply a filter based on Y/N reply before finding item? If I then use a
subquery (1st Q applies filter, subQ prompts user to enter item Name, subQ
finds item then lists all (Chemical or non-Chemical) in Product, starting
with 1st found record), what is the syntax? I don't know SQL. (As user
types 1st letter, is it possible to fill in rest of the prompt with item Name
that matches the typed letters; can enter or finish typing as in Excel
lists?) Thanks. Any help appreciated.

 

[Allen Browne]

Use forms as the interface for this kind of thing in Access.

1. Create a query using this table.
Type True in the Criteria row under your yes/no field.
Add the other fields relevant for chemicals.
Choose Ascending in the Sort row under the product name.
Save the query with a name such as qryChemical.

2. Create a similar query with False as the criteria.
Add the other fields, and sort as you wish.
Save it with the name qryNonChemical

3. Create a form based on qryChemical, and another based on qryNonChemical.
Use Continuous View (showing one row per product.) You open the appropriate
form depending on the first answer the user gives.

4. Download the example database in this article:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
On each form, add the combo and text box, and set the form's property.

The forms will now show the relevant chemical/non-chemical, and filter as
the user types into the text box.

If chemicals and non-chemicals use the same fields in your table, and you
understand about code, you can get away with one form and programmatically
change the RecordSource to the other query like this:

If Me.RecordSource = "qryChemical" Then
Me.RecordSource = "qryNonChemical"
Else
Me.RecordSource = "qryChemical"
End If

 

[Guest]

thanks for the suggestions Allen. Very much appreciated.
dennis