Search code examples
databasems-access

microsoft access enter data for two tables (one to many relation)

----Update----

as comment mentioned, a subform will do the thing

In microsoft Access, say I have two tables,

one is "project" table, including,

  • projectID,
  • project description

the other one is "part" table, including

  • partID,
  • projectID (the project that the part belongs to)
  • part description

this two tables is connected by one-to-many relation, one project could have one or more parts.

In data entry form, when user enter a project entry, how could I let the user to:

  1. add a project entry, and
  2. add one or more part associate to the project, and
  3. auto fill the part.projectID with the same value of current entry

I was struggling to find relevant tutorial, any suggestions is appreciated.

Solution

  • You can use a form. In the form you must have all the Required fields of both the Projects and Parts Tables, so the form can successfully create both records. You have to associate VBA functions to the "OnUpdate" event of the "project.ProjectID" field, so when it is updated, the field "part.pojectID" is filled-in with the same value. The following function is one I use, and will give you an idea of how is it:

      Private Sub CalYear_AfterUpdate()
    Me.First_day_of_year = DateSerial(Me.CalYear, 1, 1)
    Me.Last_day_of_year = DateSerial(Me.CalYear, 12, 31)
  End Sub

    Regarding a relevant tutorial, you may check LightningGuide.net

    If you want more detailed information about my answer above, let me know.