Creating Data Entry Forms for Multiple Related Tables RRS feed

  • Question

  • I am trying to create a form for data entry that replicates the ease of entering data for one-to-many relationships when using the drop-down arrow in a table. This allows a user to enter data in the next related table without having to type the primary or foreign key field over and over.

    For example, if I had a table for Vacation linked to a Month table that is linked to a Destination table (All with one to many relationships). I would want to make data entry form that allows me to input the Vacation 'name' key field and other data and then be able to add data to the Month table without having to repeatedly type the same value of the key field 'name'.

    I would like to make a data entry form with sub forms that will auto-populate the designated key fields when entering records in each table. I've been trying to use the master and child field to enable this functionality with no success. So my thought is to create a button that contains Macros. Does anyone have a recommendation on how to do this?

    Thursday, August 3, 2017 6:06 PM

All replies

  • Hi,

    Based on your description, I'll move your question to the MSDN forum for Access

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact

    Friday, August 4, 2017 7:56 AM
  • I'm not sure where you are going wrong, but setting the LinkMasterFields and LinkChildFields properties of a subform control to the primary and foreign keys of the parent and subform's tables respectively will automatically insert the correct value into the foreign key column in a subform's table when a row is inserted in the subform.

    Where you have a linear hierarchy of three tables, as in your case, the Months subform can either be in single forms view, with a Destinations subform 'nested' in it, or the Months subform can be in continuous forms view, and the Destinations subform can be a separate correlated subform in the parent Vacations form.  The Destinations subform would be correlated with the Months subform by making the RecordSource of the former a query which references the primary key of the Months subform as  a parameter.  The Destinations subform would be requeried in the Current event procedure of the Months subform.

    With correlated subforms the foreign key of the Destinations subform would not be inserted automatically.  To do so code is included in the Destinations subform's BeforeInsert event procedure which does so by referencing the primary key of the Months subform's current record.

    You'll find examples of both nested and correlated subforms in in my public databases folder at:!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file uses Northwind customers, orders and order details data as an example.  In the demo the code for the order details subform's BeforeInsert event procedure is:

    Private Sub Form_BeforeInsert(Cancel As Integer)

        On Error GoTo Err_Handler
        Me.OrderID = Me.Parent.frmsubOrders.Form.OrderID
        Exit Sub

        MsgBox Err.Description & " (" & Err.Number & ") ", vbExclamation, "Error"
        Resume Exit_Here
    End Sub

    Ken Sheridan, Stafford, England

    Friday, August 4, 2017 10:42 AM
  • Hi database,

    Did the suggestion from Ken work for you?

    If it did, I would suggest you mark the suggestion as answer which is the way to close a thread here.

    If not, please feel free to keep following and share us your issue.



    Help each other

    Wednesday, August 9, 2017 7:24 AM