How to filter the values use in a lookup?

[davidd31415]

I have a database with the following tables:

MainTable
Products
Packaging

The MainTable and Products table are both related to the Packaging table to display the "Package Type" field.

The MainTable is related to the Products table to select a list of various products. I would like the list presented in the drop-down lookup to be limited by the Package Type that first needs to be selected in the MainTable.

After running the lookup wizard for the Product field in the MainTable, the Row Source was:

SELECT Products.[Product ID], Products.[Product Name] FROM Products;

I changed this SQL statement to:

SELECT Products.[Product ID], Products.[Product Name] FROM Products WHERE Products.[Packaging] = LU_Packaging;

If I use MainTable.LU_Packaging or some other value at the end of the statement, a dialog pops up when I open the table asking for the value so I am assuming LU_Packaging contains the correct value.

However, the lookup drop-down has no items in it even when the packaging fields are all the same...

Am I doing something wrong here? Is there a way to do what I am trying to do ?



Also, I'm wondering about my general approach here; in most cases do the forms modify several tables simultaneously (rather than having a "MainTable" like I do here)?

THanks,

Dave

 

[dhookom]

I think you want to create cascading combo boxes where the combo box is limited by some other value or selection.
Recommendations:
1) don't ever use lookup fields in tables

http://www.mvps.org/access/lookupfields.htm

2) use forms for all of your data entry where you can use combo boxes
3) check Roger Carlson's samples at

http://www.rogersaccesslibrary.com/TableOfContents3.asp#C.

4) find and use a good naming convention. One example here

http://www.granite.ab.ca/access/tablefieldnaming.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davidd31415]

Thanks for the links, I'll take a look at each of them.

I accomplished what I was trying to do a few minutes ago. I did use a form instead of the actual table, as your first recommendation states.

Aside from that it was as simple as adding a WHERE statement onto the Row Source property of the combo box that does the constraining along with an After_Update event handler to requery the the combo box that is constrained.

Thanks!