CODEWITHSUNDEEP
ms-accessms-access-2010
user4181107
user4181107

Reputation: 401

How can I create one form in Access 2010 to insert records into two related tables?

I have three tables: one parent table and two children tables. The two child tables have a one-to-one relationship with the parent table. The reason for this structure is because the two children tables share some characteristics, but they also have their differences. What I would like to accomplish, is avoid direct entry into the parent table and provide forms for the children tables that would then also populate the fields in the parent table (fields which are not in the children tables themselves).

P.S. I'm pretty new to Access 2010.

Upvotes: 0

Views: 1091

Answers (2)

Wiz
Wiz

Reputation: 121

This is quite easy to do. I'll show the case of 1 child. Then you can replicate it many times Let's assume this structure

Parent Table

  • IDParent --> Key
  • PField1
  • PField2 ...

Child Table

  • IDChild1 --> Key
  • CField1
  • CField2
  • ...
  • Parent_ID --> Link to Parent table (IDParent)

Create a relationship between Parent and child through IDParent-->Parent_ID.

So this is the code for managing record adding. In the AfterInsert event of child form you can put a code like this

Private Sub Form_AfterInsert()
  Dim rsParent As DAO.Recordset
  Dim lngParentID As Long

  '
  ' Creates a recordset from Parent table
  '
  set rsParent = CurrentDb.OpenRecordset("SELECT * FROM tbParent",dbOpenDynaset)                    

  ' Creates a new record in parent table
  With rsParent  
      .AddNew
      .Fields("PField1") = ...              ' Populate fields here
      .Fields("PField2") = ...
      .Update

  End With

  rsParent.MoveLast
  lngParentID = rs.Fields("IDParent)        ' Get IDParent of recently added parent record

  rsParent.Close                            ' Close recordset

  Me.Parent_ID = lngParentID                ' Assign to current record inserted in child table the link to parent record just created  

  Me.Requery                                ' Refresh recordset under child form

End Sub

Let me know if it was what you were looking for.

Bye, Wiz

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8402

You can either do it through VBA code, or you can set up a proper relationship (a database relationship) so that the information gets automatically updated.

You'll need to make sure that all tables have Primary Keys that associate the tables to each other, otherwise there's no reference point to use to update the tables.

You might want to take a look at the MS KB article, and pay close attention to the section on enforcing referrential integrity.

Upvotes: 0

Related Questions