From a subform, creating new record in another subform

[simonliu149]

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:
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:
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

 

[gemma-the-husky]

when you say add a new record to the second subform, what you really need to do, to do this programatically is

a) add a record to the subforms underying datatable (with sql, or by executing a query)and then
b) requerying the other subform

also you can requery the subform from the first subform simply by

parent!secondform.requery

where secondform is ther name of the secondform object, embedded in the parent form.

-------
if you are just trying to move the focus to the newline in the second subform to manually insert a row, then it will be something like

parent!secondform.setfocus

and i am sure you can then get it to goto the newrow, but i am not sure exactly what command to use
maybe a docmd or a runcommand object

 

[Thinh]

what type of data is it your subform 2? is the subform 2 editable meaning that it doesnt contain any group by clause in your query. if it is editable you can use the following command 'Forms!frmMain!container_form.Form.recordset.addNew' to add a new record.

 

[simonliu149]

Thanks Gemma, that worked like a charm! I first executed a SQL INSERT INTO statement that inserted a new record into the underlying table, changed the second subform's RecordSource to point to this newly inserted record (via a SQL SELECT statement), and then requeried the second subform.

So the final solution is:

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

Thanks for all the help!

when you say add a new record to the second subform, what you really need to do, to do this programatically is

a) add a record to the subforms underying datatable (with sql, or by executing a query)and then
b) requerying the other subform

also you can requery the subform from the first subform simply by

parent!secondform.requery

where secondform is ther name of the secondform object, embedded in the parent form.

-------
if you are just trying to move the focus to the newline in the second subform to manually insert a row, then it will be something like

parent!secondform.setfocus

and i am sure you can then get it to goto the newrow, but i am not sure exactly what command to use
maybe a docmd or a runcommand object