Using One Control to Limit Values in Another

[jgross]

I have a combo box on a form that's pulling a large number of possible entries (items in an inventory) from a lookup table. Each entry belongs to one of several categories, and the number of categories is far fewer than the total number of entries. I would like to structure the form so that the user first selects the category from a separate combo box and then the choices in the item combo box would be limited to entries in that category. I'm having trouble discerning what particular mechanism I need to make this happen. Thank yo for your help.

Jim

[June7]

This is cascading or dependent controls. Check this tutorial http://www.datapigtechnologies.com/f...combobox2.html

[jgross]

Based on the above video I changed my row source with the query builder, setting the "Totals" value of the matching field (tblCat.catID) to where and entering the name of the combo box under the "Criteria" value. The SQL statement now reads:

SELECT tblInv.itemID, tblInv.itemName FROM tblCat INNER JOIN tblInv ON tblCat.catID = tblInv.itemCat WHERE (((tblCat.catName)=[forms]![sbfrmLineItem].[cmbLineItemCat])) GROUP BY tblInv.itemID, tblInv.itemName;

In this case tblInv is a table containing data on all inventory items.
tblCat contains entries for each category.
tblCat.catID is a foreign key for tblInv.itemCat in a 1 to many relationship (one category applied to many items).
tblInv.itemID is the primary key for tblInv and is the base of the row source for the "Items" combo box (cmbLineItemID).
cmbLineItemCat is the combo box for the categories. It's row source is tblCat and it's "after update" event is set to:

Private Sub cmbLineItemCat_AfterUpdate()
    Me.cmbLineItemID.Requery
End Sub

This all results in an empty combo box with no items. If I remove the "Where" value and the criteria, I see all options as expected. Clearly something in my methods has gone horribly wrong.

Jim

[June7]

Show both of the comboboxes RowSource statements. The tutorial shows that both comboxes reference the same table in the RowSource query which is a 'lookup' table for this info, not a table for saving data records.

[jgross]

control cmbLineItemCat
control source:

lineItemCat

row source:

tblCat

control cmbLineItemID
control source:

lineItemID

row source:

SELECT tblInv.itemID, tblInv.itemName FROM tblCat INNER JOIN tblInv ON tblCat.catID=tblInv.itemCat WHERE (((tblCat.catName)=[forms]![sbfrmLineItem].[cmbLineItemCat])) GROUP BY tblInv.itemID, tblInv.itemName;

The total list of tables is as follows:
tblShows: records of all the shows in a season
tblInv: records for all of the equipment the theater owns
tblCat: lookup table of all of the applicable categories
tblBox: lookup table of all of the cargo boxes the theater owns
tblLineItems: records of what equipment is to be packed for what show

The relationship diagram is attached bellow. The intended workflow is the the user opens a form for inputing information on a given show. This form has a subform for displaying/editing records in tblLineItems that match that show. On this subform they would first choose the category they want, then select the inventory item from that category that needs to be assigned to the show. Finally they would enter the number of that item that need to be used on the show, and what box it should be packed in. They would then be able to print out a manifest listing the contents of each box for each show.

[June7]

First, do not want to use the Name field in WHERE clause.
Second, don't think table join is needed in cmbLineItemID row source. This worked for me:
SELECT tblInv.itemID, tblInv.itemName FROM tblInv WHERE itemCat=[cmbLineItemCat];

Might want to also set the value of cmbLineItemID to null in the cmbLineItemCat AfterUpdate event. In case users are changing Cat entry from previous selection.

[jgross]

Perfect. Not knowing SQL I was skeptical of the statement Access was writing after I formed my query, it just looked to complicated for what I was trying to do. I think (I've been known to be wrong) the root problem was over specifying the name of the control as:
forms![sbfrmLineItem].[cmbLineItemCat]
instead of:
[cmbLineItemCat];
Thank you again for your help.