Microsoft Access - Form that will populate data from Table A and Table B to an Associative table C

Question

I am building a database that keeps track of customer subscriptions in Access. I have table A that tracks customer information, table B that tracks product information and table C that is associating the customer with the product.

Currently table C has to be manually filled out which can be slightly annoying because you have to get the customerID and the productID separately then put them in. I was looking to create a form that could add them just by selecting the customer and then the product from lists and then use a macro to save them to table C.

I have tried various ways of doing it but have yet to find one that works. Any suggestions?

Answer 1

Whether you use a subform within a Customers parent form, or a stand-alone form bound to the table which models the many-to-many relationship type, the key to making the form user friendly is to bind a combo box to the foreign key column, setting up the combo box so that the numeric bound column is hidden, and the text value from the referenced table is shown in the control.

The only real difference between a subform and a stand-alone form is that the latter would have combo boxes bound to both foreign key columns, whereas the former would have only one combo box, bound to the ProductID column.  The correct CustomerID vale would be inserted automatically into the row in the table by virtue of the LinkMasterFields and LinkChildFields properties of the subform control each being set to CustomerID.

Taking a customer combo box as an example it would be set up like this:

ControlSource:   CustomerID

RowSource:     SELECT CustomerID, FirstName & " " & LastName FROM Customers ORDER BY LastName, FirstName;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource:     SELECT CustomerID, LastName & ", " & FirstName FROM Customers ORDER BY LastName, FirstName;

A products combo box would be set up in the same way, other than that the visible value would be that of a single Product column, not a concatenation of the values of two columns.

Answer 2

Download the new Northwind Starter Edition.
Check the database design: it has the same many-to-many relation, but between Orders and Products, with OrderDetails being the junction table.

Then check the OrderDetails form: it is bound to Orders, with the subform bound to OrderDetails, and the Products dropdown bound to Products table.

That should get you started. This is an important pattern in Access, so it is worth spending time with.

Answer 3

It sounds like you have what is referred to as a many-to-many relationship. A customer subscribes to one or more "products" and a "product" can be subscribed to by one or more customers. The third table -- table C in your description -- is called a junction table. It contains two foreign keys, one from customer subscriptions and one from products, along with any other required details for that transaction.

The most effective, most efficient method for handling this situation is a main form/sub form design.Here is an example of this in this download. It models the same sort of relationship, using Singers (customers in your scenario) and Songs (products in your scenario) and shows two approaches to creating the appropriate main form/sub form interface.