Looking up data from a specific record in ANOTHER table

[Bigred33]

Ok, I know there's got to be a simple way to do this. I'm using Access 2002 (XP) to design a simple database to manage a customer/order database. I have a table called "Customers" and another called "Orders". There is a one-to-many relationship between these two tables using the CustomerID field (an autonumber field/primary key for the "Customers" table)--this is obvious, since one customer could have several orders.

Here's the problem: I want to have a drop-down combo box on the form for the "Orders" table which lists all the Customer ID numbers. This part I have done, but this next part is what I'm stuck on... When a person selects a customer ID number, I want to use that ID number to look up the first and last names of that specific customer and use that information on the form (in a field).

How do I "access" that data for that specific record? For example, if my customer's name is "John Doe" and his CustomerID is 5, when I select 5 with the dropdown combo box, how do I print out his name on the form as well? I'm basically trying to bind data from one table to a textbox on a form for another table. Do I have to use an SQL query somehow or is it possible to use some Access wizard or something?

Thank you in advance for any help you can offer me.

 

[SBendBuckeye]

There are a couple ways you can do this. Others may have other ideas as well.

1. Create a query as the record source for your form instead of the order table. If you create it with joins instead of where clauses it will still be updatable. Then you just setup the text boxes on your form. If you make the text boxes Locked and Disabled they will look like normal except the user won't be able to even click anywhere in them.

Select tblOrders.*, tblCustomer.FName, tblCustomer.LName
From tlbOrders Inner Join tblCustomer
On tlbOrder.CustomerID = tblCustomer.CustomerID
Order By YourSortFields, etc

2. Use DLookUp in the OnCurrent code to retrieve the data from the Customer table.

txtCustomerName = DLookUp("CustomerName", "tblCustomer" _
"CustomerID = " & Me.YourFormControlWithCustomerID)

Good Luck!