1

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?

Relationships

James Eckhardt
  • 11
  • 1
  • 7

1 Answers1

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.

MoondogsMaDawg
  • 1,704
  • 12
  • 22
  • Thanks. Is there a way this could apply to adding multiple county entries with one form? For example, I want to add a Rose that is found in both Johnson and Anderson counties in both dry and wet moisture. Can one form add all this information? If not, is there another solution? – James Eckhardt Jan 10 '17 at 15:33
  • If the counties, moistures, and plant id already exist, this form would require one entry per combination. To append multiple combinations at once would require using macros or vba with an unbound form. It's possible but fairly complex. Using multiselect listboxes for the moisture and county fields could give you the data and functionality you need, but the coding logic would need more info about the rules you use in the data model so I can't really prescribe a solution. – MoondogsMaDawg Jan 10 '17 at 15:45