Problem with lookup query

[stewie2699]

I have a table called tbl_Details that stores budget line item information. There is a field called WBSL3. This field derives its contents from another table called tbl_WBS_Level3. This table contains the following fields: WBSL3_ID (AutoNumber), WBSL3 (text), Description. In tbl_Details, the user selects from a combo box, a WBSL3. There is another field in tbl_Details called WBS. This field derives its values from a lookup table that contains about 200 records. It has as it's foreign key, the primary key from tbl_WBS_Level3 (WBSL3_ID). I am trying to limit the values in the WBS pull-down to those where WBSL3_ID matches the WBSL3_ID that the user selects from the pull-down. I built a query that has as the criteria the form name and field name for WBSL3_ID. I have done this sort of thing before with no problems. In this case, however, the combo box for WBS comes up with no values, no matter what the user selects in WBSL3. I don't know what I'm doing wrong. Thanks in advance for any help...

 

[Adrianna]

Sounds a little unclear, but you said that you're trying to find records in the WBS table where WBSL3_ID matches the WBSL3_ID. Could you post your query here. It would be helpful to see your JOIN, bucause your relationship in the tables seems like it should be handling the return record criteria. I could be missing something as I have a great way of visualizing things differently then they are.

 

[Pat Hartman]

Make sure that the combo's column count property accurately reflects the number of columns in the combo's RowSource query.

 

[stewie2699]

Still having problems with look-up query

Thanks to all who replied. I am still having problems and attached a rough sketch of the involved tables, which includes an explanation of what I have tried and what occurs when I apply what I have tried. Any insight or suggestions would be greately appreciated. Thanks again.

 

[Pat Hartman]

Remove WBSL3_ID from tbl_Details. It is redundant.

Cascading combos are a problem in a continuous form. Access only maintains ONE set of properties for the form. That means that each combo can only have ONE active query. So, when you select an item from the first combo and requery the second, that affects the RowSource for ALL the other records. It doesn't change the stored value, which is still correct. It just changes the RowSource. Unfortunately, if the bound value doesn't exist in the RowSource, Access can't show it so it seems to disappear.

Because of this, I try not to use cascading combos on continuous forms. If you can't avoid them, try to train your users to not be upset.

Check your table to make sure that each row really does contain the correct value. It is possible that you have a bug but it is most likely simply a display issue.