Unanswered: Data Entry Form: Using a Combobox to Select Data from Multiple Tables
I have two tables, Customers and Transactions.
Customers has the following three columns:
CustomerID LastName FirstName
Transactions has the following three columns:
TransactionID Amount CustomerID
I have created a one to many relationship from Customers.CustomerID to Transactions.CustomerID.
I would like to create a data entry form for the Transactions table. I want to use a combobox that selects a CustomerID for the Transaction table, but displayes FirstName and LastName from the Customers table.
I have tried creating a query that includes Transaction.CustomerID, Customer.FirstName, and Customer.LastName and indicating that the Bound Column should be Transaction.CustomerID. I encountered two problems using this method:
1. Each CustomerID in the Transaction table has multiple entries, so the combobox shows each name multiple times.
2. The CustomerID is does not show up in the Transaction table.