Inputting data from a form into a junction table Microsoft Access

Question

In access I want a single form which can input data into a junction table. The relationships are as follows.

One table has plant names and basic information on the plants, another table has soil moisture content and another table has county names in which certain plants may be found. One plant may be found in many counties and many plants may be found in one county. Same goes for moisture. Thus, this database needs to many to many relationships. How can I make a form which takes a plantID as well as the counties and moistures of that plant and forms an entry in the junction table? Am I approaching this problem in an appropriate way?

Answer 1

Assuming that you only need to add records to the junction table from this form, build a form bound to the junction table.

If you use the wizard, your new form should list each field as a textbox.

For each field that is a foreign key, change the textbox to a combobox. Use the row source property to pull the information from that field's devoted table where the PK resides.

So for example, MoistureID will become a combobox whose row source would be:

SELECT MoistureID, Moisture
FROM Moisture;

The ID field will then be the output of the combobox but will display the Moisture field when the list opens. You can hide the ID field from the user by changing the "column width" property to 0";1" but make sure the "column count" property = 2.

Then you can change the form's data entry property to "yes" to hide the existing junction records from the view of the form.

In form view, choose your plant ID and other selections and once you tab past the end of the form's tab order, your junction record should append to the junction table and the form's controls should clear.

If you need to add records to other tables from this form then you will need to build an unbound form and handle the appends using macros or VBA.