Newbie: Populate field based on lookup's neighbor

[NerdyOne]

Heres the breakdown:

Two tables Receipts_table and Categories.

Within the receipts_table are several fields, two of which are called For and For_Account. The For field is a combo box lookup bound to a field in the Categories table called Category_Name. I need the For_Account to be populated with the account number associated with Category_Name. The field is called Account_NO.

Here's the question:

How do I setup my table so if the user selects a value in the combo box called "For" the "For_Account" is populated with the value's account number.

Example:

The Categories table looks like this.

Category_Name Account_NO
------------- ----------
Game Room 001-579-423-233
Pool Charges 001-730-220-001

When the user selects Game Room from the combo box I need the field For_Account to be populated with the account number.

Whew, thanks for any help you might have!

 

[dhookom]

I question the reason why you want to store the Account_No in the Receipts_Table when you can always join the tables to find the Account_No value. This goes against some basic relational database wisdom.

It can be done with code in a form but I hesitate to provide a solution that should be unnecessary and ill-advised.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NerdyOne]

Well I am a newbie, I must apologize. I welcome any solutions that you may have. If it can be done with a relational database, then please let me know how!

 

[dhookom]

I would create an Autonumber primary key [CategoryID] field in the the Categories table. Then store only this value in the Receipts_Table. When you create a report that requires the Account_No or Category_Name, include the Categories table in your record source or use a combo box to display these.

If you have a form that selects the category in a combo box, you can use a row source like
SELECT CategoryID, Account_No, Category_Name
FROM Categories_Table
ORDER BY Account_No;
Set the Column widths to:
0";1";2"
This will hide the categoryID and display the Account_No. If you also want to see the Category_Name on your form:
Add a text box on your form
set its control source to:
=YourComboName.Collumn(2)


Duane
MS Access MVP
Find out how to get great answers faq219-2884.