Creating new record from subform

simonliu149 · February 11th, 2009, 01:27 AM

Hi guys,

I've got a problem with programmatically creating a new record in a subform from a different subform. Currently, the main form (frmMain) alternatively displays either subform 1 (sfrmSubform1) or subform 2 (sfrmSubform2), depending on what the user has selected. This switching is done via:

Code:

Forms!frmMain!container_form.SourceObject = "sfrmSubform1"

Where container_form is the name of the container holding either subform 1 or 2.

From subform 1 (i.e.: the VBA code-behind for subform 1), I'm trying to add a new record to subform 2. I've tried:

Code:

Forms!frmMain!container_form.SourceObject = "sfrmSubform2"
Forms!frmMain!container_form.Form.SetFocus
DoCmd.GoToRecord acActiveDataObject, , acNewRec

But this does not work. The runtime error I get is that sfrmSubform2 is not open.

Does anyone have any ideas how I can solve this?

Thanks,

Simon

mmcdonal · February 12th, 2009, 08:24 AM

Where are you getting the error?

I think the problem is that the form is NOT open by the time your last line runs.

You could troubleshoot this by creating a 3rd variation of your form based on sfrmSubForm2 (call it sfrmSubForm3) and then do this:

Code:

Forms!frmMain!container_form.SourceObject = "sfrmSubform2"
Forms!frmMain!container_form.Form.SetFocus

And then on the on open event of sfrmSubForm3, put this line:

Code:

DoCmd.GoToRecord acActiveDataObject, , acNewRec

Or something similar.

I think the setfocus will also give you problems

Did that help?

simonliu149 · February 12th, 2009, 06:09 PM

Hi mmcdonal, thanks for your help but I found a better way to work around the problem.

By first manually inserting a new record into the table (via a SQL INSERT INTO statement), I then set subform 2's RecordSource to point to the new record (via a SQL SELECT statement). So the final code is:

Code:

CurrentDb.Execute ("INSERT INTO tblTask (storycard_id, task_id, description, " & _
            "estimate) VALUES ('" & storycard_id & "', '" & task_id & "', " & Chr(34) & _
            " " & Chr(34) & ", '0')")
      Forms!frmMain!tree_form.SourceObject = "sfrmSubform2"
      Forms!frmMain!tree_form.Form.RecordSource = "SELECT * FROM tblTask WHERE " & _
            "storycard_id = " & storycard_id & " AND task_id = " & task_id

Thanks for your help anyway!

Simon