Design issue

[Accessuser67]

I have 3 tables, tbl1 is for receipts, tbl2 is for category and aisle and tbl3 is for store and address. What I want is a form to add data to the receipts table. In the form I have a lookup box based on tbl2, where when I enter the receipts I lookup the category for the item and I want the associated aisle to show up in the form after I select the category.

how can I show this in the form?

Regrds,


ekmoorhead

[jzwp11]

Include the aisle in the combo box along with the category. If you look at the row source property of that combo box it should look something like this:

SELECT category, aisle FROM tbl2

Now, add another textbox control to your form. Set the control source so that it references the combo box. You can pull out any information you want from any column of that combo box using the following expression

=comboboxname.column(x)

where x= the column # of the field in the combo box's row source that corrsponds to the information you want to display. Access starts counting at zero, so if you want to display the aisle & assuming that the row source looks like I have shown above, the expression would be

=comboboxname.column(1)

[Accessuser67]

Thanks.

However, I'm not good with VBA. Is there another way to show both values from the drop down box?

regards
ekmoorhead

[jzwp11]

There is no VBA in the solution I proposed. The SELECT...FROM.. is a query that can be built using the combo box wizard. The =comboboxname.column(x) is just an expression that you would type in the control source property of the textbox. You will have to substitute your actual combo box name in place of comboboxname