In MS Access, how can a form provide a drop-down populated with data from a related form?

(Note: the customer field has a one to many relationship with id) I want to create form where I put in the product, and select which customer is ordering this from some kind of dropdown menu. How do you achieve this in Access? From my research all I can do is create a form that contains placeholders for the fields in the current table but nothing that takes advantage of the relationship to help me fill out the customer field.

1 Answer 1

Yes, you absolutely can do this. There are a few tricks, but not many.

1. Create a new form and set the Record Source to be the "Orders" table. To see the form properties window, right click in the form and choose "Form properties"
2. From the Form Design ribbon, drag a new combo box onto the form
3. I'd cancel the wizard if it comes up and do it all manually, but you can use this if you like.
4. Make sure the combo box is selected and look at its property sheet
5. Set the control source to the field "Customer"
6. Set the row source to be "SELECT Customers.ID, Customers.Firstname, Customers.LastName FROM Customers; " or click on the ". " button to create this simple query. Note that the FIRST field is the ID, which is the link between the form and the combo box.
7. This is now working, BUT it will only show the ID, which is useless. You need to fix up the formatting of the combo box. Go to the format tab in properties, then look for "Column count". Change this to 3.
8. Under this is Column widths. You can use this to hide the ID field by putting in "0,3,3". You may want to play with these values to make them fit nicely to the length of the names.
9. You might want the "Column heads" turned on or not - up to you.
10. View the form now in "Form View" and enjoy!

I normally save the query behind the combo separately. Then in step 6 you select the query rather than paste the SQL. The reason for this is that you can potentially re-use data sources for common combo boxes. Naming conventions are a very good thing when your database grows, so be logical about what you call things.

On this note, I would change the name of your table variables to below. To me, this is clearer. Orders needs an ID as well. If the ID's have the same name, you just play "match the name" when joining tables.

**Customers** => (CustomerID, firstname, lastname, address) ^ \ **Orders** => (OrderID, CustomerID, product, total) 

You could also combine the First/Last into one field rather than have them separate (in the query have a field Custname: [Firstname] & " " & [Lastname]). You can make the combo boxes really powerful in access, but watch out if they have a LOT of rows in them - they will slow down.

For the products on the order, you really need to create another table called "Order Lines" which has OrderLineID, OrderID, ProductID and Qty as fields. Then a single order can have multiple products. This is beyond the scope of the question, but I thought I'd point you in the right direction there too. You need a product table as well.