Pop-up form that creates record that links 1:1 to main form

Question

I have a pop-up form that opens using a button on the main form in a MS Access database. The pop-up form creates a new record (in a separate table) that needs to link to the current record in the main form. How do I set this up properly?

The two forms (frmMain, frmWB) are based on 2 separate tables (tblMain and tblWB). The records have a 1:1 relationship (each main record can only have one WB1 record).

The main form (frmMain) has the primary key [ID]

The pop-up from (frmWB) has the primary key [WBID] (autonumber) and [MainID] (number).

Data entry personnel will enter the main info first (this will have to be required to avoid orphans), then click a button that saves the main record and opens the pop-up form to add the detailed info. When done, they click the close button which saves the pop-up record, closes the pop-up, and returns to the main form (which stayed open in behind). Navigation has been disabled in the pop-up and it is set to cycle the current record.

I have tried linking using the primary IDs for the two tables, but that doesn't seem to reliably link the correct ID numbers when adding a new record in the pop-up. I tried using subforms, but had problems if the navigation or 'new record' buttons on the main form were accidentally hit when entering data in the subform - that created records in tblWB that had the wrong ID numbers.

Answer 1

If frmWB is opened modal, so that the current record of frmMain can't be changed while the popup is open, you would have this in the BeforeInsert event procedure of frmWB:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me!MainID = Forms!frmMain!ID
End Sub

This automatically assigns the tblMain ID to the newly created tblWB record.

MainID should be an invisible textbox on frmWB, bound to the table field MainID. While debugging you can make it visible, but it should be locked.

If frmWB isn't modal, I would use OpenArgs to pass the ID from frmMain to frmWB. And then use that in Form_BeforeInsert instead of Forms!frmMain!ID

Answer 2

Contrary to Andre's comment, the usual way is to create a relationship between the two tables, enforce referential integrity and use cascading updates.

While this article is specific to Office 2003, the concept is the same for all versions of Access. If you do this, the popup will automatically create a new record linked to the main form's record.