Open child form to new record and populate fields based on parent form

NashVegas,

Try to put the code in the On Current event of the form.

Sincerely,

Ed

btw, you mentioned, child form, is frmevent a subform of form frmSOP?

Try putting a new field on your form txtTrngID and set its row source to =[Forms]![frmSOP].TrnId.

then use
DoCmd.OpenFrom "frmEvent", acNormal,,,acFormAdd

When you open the form you should see the correct TrnID in the new field.

Now try to set the value of your field.
Private Sub form_load()
Me.TrnId = Me.txtTrngID
End Sub

TLH

Capricorn,

frmEvent is not a subform. I used child form to mean it was called into existence from the frmSOP form. When I step through your code, Me.newrecord = 0 so it skips the openargs statement. Was this what you expected?

it  should be Me.newrecord = -1

check if the form frmEvent Allow additions property is set to YES

Capricorn and TLH,

I still get the "Can't assign values" error trying either of your suggestions.

Ed,

The "On Current" event doesn't execute when the form loads. I put breakpoints in the code and the breakpoints are never addressed if all I do is open the form with the "Schedule Event" button.

John

Cap,

Yes the allow additions property is set to Yes for frmEvents.

can you upload a copy of your db..

do a compact and repair first

You could create a new record in the child form's record source, populating the field from the value on the calling form, then open the child form to the last record.

Cap,

I have it uploaded. From the main menu, select SOP Profile and then click "Schedule Event" to open the Event Details form. Thanks for taking a look!

TLH,

Thanks for your help! This got me closer in some ways, but in others it is creating more problems. It creates 2 records on the tblEvt table and neither record gets updated with the trainer or attendee list after the form is saved and closed. The second record gets created when the DoCmd.GoToRecord , , acNewRec code executes. This happens before the user has any opportunity to interact with the form.

Helen,

I made an attempt at interpreting your suggestion. I tried setting the child form's recordsource using an INSERT INTO statement, but I got an error message that this kind of SQL statement is not allowed to be used as a "row source". How do I create a new record in the child form's record source?

Thanks!
John

TLH,

Correction: I went back to your original code and noted that the 2nd of the pair of records that gets created every time the "Schedule Event" button is clicked does save the attendee, trainer, etc. If we can figure out how to prevent the first record from getting created we'll be laughing.

Cheers!

Okay, I've got it working now. I changed the tblEvts.TrnId field to display the traning name rather than the trnID value (although it is still bound to the trnID column). This simplified matters somewhat. I set the form's recordset type to dynaset and then added the "DoCmd.GoToRecord , , acNewRec" code to the child form's load event. This event also handles pre-populating the attendees based on training deficiencies. The form opens and is editable and all the data saves on the tblEvt table and only one record per event is created. If you open the Event Detail form on its own (rather than from the SOP Profile form) then the form shows all training events and is editable. All this is working as I intended.

I am not exactly sure what the silver bullet was that fixed my issue, but I am attaching the working prototype if anyone is interested. I'll split the points since several suggestions were helpful. Thanks!

This file replaces the file attached to comment 33721621

ViewTrainLite.accdb

This file replaces the file attached to comment 33722019
ViewTrain---TLH.accdb

This file replaces the file attached to comment 33735246
ViewTrainLite-2.accdb