Add new record to master and subform at the same time.

G

GeorgeAtkins

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons related to
another form, I need to ensure that a new record is added to the subform when
a new record is created in the master form. Obviously, I'm having an issue.
Setup: The forms are locked down. The user must click an custom Add button
to add a new record in the master form. I'm trying to fit the additional code
into this routine. The user can also click another Add button to manually add
a record into the subform, but I also need to do this automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it gets the
primary key value necessary for the related subform record. Then write code
to add record into subform. This almost works. It adds the master form
primary key value into a new subform record, but the subform record does not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the subform
to add a new record. This fails with error "2465: Application-defined or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we can also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top