Combination of autofill and limit-to-list for 3 fields

[KerryL]

Items in my inventory are separated into divisions. When users are entering data they do not always know which division a particular inventory item may reside in, so they may select the item based on description. For that reason, I'm trying to set up the following three fields to accommodate each way a user may find the item.

cboDivision = Inventory divisions
cboDivision Select Stmt: SELECT DISTINCT [tblProducts].[DivisionName] FROM tblProducts ORDER BY [tblProducts].[DivisionName];

txtProjNo = Project number assigned to each inventory item

cboProjDescr = Description of inventory item
cboProjDescr Select Stmt: SELECT [tblProducts].[ProjectDescr], [tblProducts].[DivisionName], [tblProducts].[ProjectNo] FROM tblProducts ORDER BY [tblProducts].[ProjectDescr];

If the user selects a Division, I'd like the selections in cboProjDescr to be limited to those items which match the Division selected in cboDivision.

When the user selects an item from cboProjDescr, I'd like the cboDivision and txtProjNo fields to be populated with the corresponding division and project numbers for that particular item.

In the After_Update event for cboDivision I entered:
cboProjDescr = cboDivision.Column(1)
cboProjDescr.Requery

In the After_Update event of cboProjDescr I entered:
txtProjNo=cboProjDescr.Column(1)
cboDivision=cboProjDescr.Column(2)


I'm missing something though, because when I select a division, the items in cboProjDescr are not limited to those within that division.

And when I select an item from cboProjDescr, txtProjNo is correctly populated with the corresponding project number for the selected item, but cboDivision remains blank.

Is it possible to create this combination of autofill and lookup options? If so, I've obviously coded something wrong, and I'd appreciate some help.

Thanks in advance,
KerryL