Relationships in Access 2010

Question

I have two tables in a 2010 Access Database. One for Customers and One for Invoices. I created a form with a lookup field at top for a new invoice.

What I'm trying to do is when you look up a customer and choose them, their information will automatically populate into the customer information on the invoice field (so you don't have to retype it). It would then have to copy over to the invoice table when you save.

I'm assuming this requires a one to many relationship? It seems like this is a pretty basic step but having a tough time finding the answer any help would be greatly appreciated :)

Answer 1

You'll want to set up your tables like this:

Customers

• ID
• CustName
• Other fields...

Invoices

• ID
• CustomerID (this is a foreign key to your Customers table)
• Other fields...

Then on your form you will want to use your Invoices table (primarily) for the form Recordsource:

1. Add a combobox and set its ControlSource to the CustomerID field from the Invoices table.
2. Set the RowSourceType of the control to Table/Query.
3. Set the RowSource to SELECT ID, CustName FROM Customers ORDER BY CustName
4. Set BoundColumn = 1
5. Set LimitToList = Yes
6. Set ColumnCount = 2
7. Set ColumnWidths = 0; 1 (the second number is less important than the initial 0; the initial 0 tells access to hide the ID column)

The user will then choose a customer to associate with an invoice from the combobox (pulldown). There are no fields to copy. When you want the customer info for an invoice you just join the customer table with the invoice table like so:

SELECT Customers.*, Invoices.* 
FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.ID

Note that I've used the asterisks for simplicity in the example. It's good practice to explicitly specify which columns you want to use in a SELECT query.