Amateur Question About Linking Tables (1 Viewer)

[LRBV]

Okay, this question will likely appear laughably obvious to many of you but I would be grateful if someone could help me out.

I have a database with a main table (TBL_Main) that requires a Customer Name and Customer Number (p-key). I am linking to an external database/table (tblCustomerData) that already has the same information within it.

On my main form I want to have the "Customer Name" field as a drop down that fills from the external table (tblCustomerData). That part is easy (and already accomplished). Here is where I am having trouble: I want the "Customer Number" field to auto-fill based upon the "Customer Name" I choose in the drop-down box.

So, if I have the following entries in the external table: [Customer Name : Customer Number], [Jason : 0001] [Jack : 0002], [Joe : 0003], [John : 0004], etc., when I choose "John" in the "Customer Name" drop down the "Customer Number" field auto-fills with "0004". Nothing is being created here, just pulling already existing information from the external table to populate multiple fields in my database. This also does not need to be a "snap shot in time". If info cahnges in the external database it is okay for it to follow suit in my database.

Thanks in advance for any help that can be offered!

 

[neileg]

When you say drop down, I presume you are using a combo box. These can be configured to have more than one column, so you need to add the customer number to the query that populates the combo. Then you can either change the format of the combo to show both columns, or leave the customer number hidden by reducing the column width to 0 and using this hidden colum as the data source for your custmer number text box.

 

[LRBV]

Thank you for your response. I follow your answer up to a point but can anyone help me understand how to use the hidden column in the Customer Name combo box as the data source for my Customer Number text field?

Thanks again!

 

[LRBV]

Can anyone help me understand how to use the hidden column in the Customer Name combo box as the data source for my Customer Number text field?

Thanks!

 

[neileg]

Suppose you combo is called cboCustomer and the hidden column is the second one. Columns are numbered from 0 so the second one is 1 (confusing I know).

So in the datasource of your text box you would enter
=cboCustomer.Column(1)