Main form ID to subform using Access 2010 vba code

[Sunigahj]

I have a form with a sub form that is loaded by code. Everything works except the main form ID does not appear on the subform. When new records are added manually everything works fine. All realtionships and links are in place. This is all done in Access 2010 vba code. Any and all help would be greatly appreciated. I know it has to be something simple I am missing.

Here is the code I am using:

Private Sub Command35_Click ()
Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set dbs = CurrentDb()
Set rst1 = dbs.OpenRecordset("InventoryRequestQuery", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("Inventory Request Details", dbOpenDynaset)
 
rst1.MoveFirst
 
Do Until rst1.EOF
 
rst2.AddNew
 
rst2!Nomenclature.Value = rst1!Nomenclature
rst2!ProductCode.Value = rst1!ProductCode
rst2!NSN.Value = rst1!NSN
rst2!UnitPrice.Value = rst1!UnitPrice
rst2!txtDate.Value = rst1!txtDate
rst2!Quantity.Value = rst1!Quantity
rst2.Update
rst1.MoveNext
 
Loop
 
Me.Form.Refresh
 
rst1.Close
rst2.Close
 
 DoCmd.GoToControl "SiteID"
 DoCmd.RunCommand acCmdCopy
DoCmd.OpenForm "InventoryRequest5A", acNormal
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "SiteID"
DoCmd.RunCommand acCmdPaste
 
End Sub

This code works perfect except for not placing the mainform ID on the subform. I beleive it has to be ssomething simple I am missing. This is my first question
submitted so ask questions if I didn't make my issue clear.
I want say thank you for all the help.
I like to think positive.

[NeoPa]

I'm not sure what the code tells us, or why it's included, but you say the sub-form's ID control isn't populated automatically when the Master/Child links are not defined, yet they are when the links are defined.

That describes how things work and are expected to work. Are you saying you want a sub-form but don't want to define the Master/Child links?

[zmbd]

(UPDATE 2016-05-03: See my next post... learned something new; however, using the direct methods as originally mentioned the following may still have some merit.)

AFAIK: This is because, behind the scenes, MS adds the related field entries on a bound form; however, VBA doesn't have the same scope (i.e. the table relationships) - therefore, you have to provide the related value information yourself when working directly with the underlying record source(s).

Doesn't matter if you are accessing the underlying table/query or the form's recordset under VBA (I've tried both ways in the past), in either case, you have to pull the related information and supply it to the new record because VBA has no knowledge of how the tables are related.

You'll find this true about a lot of things in databases, where the SQL engine will have scope about one thing yet nothing about the form's information (or the reverse) amongst other situations.

-Z

[zmbd]

I have been playing with this in one of my subform databases and discovered that there is a way to accomplish what I believe you were trying to do - learned something new!

What I found is that, if you set the focus to the subform container, use the DoCmd.GoToRecord record:=acNewRec to move to a new record in the subform, you can then set the control's value to whatever is valid for the control, finally check the subform to see if it is "dirty" and if so save the newly created record.

Normally I don't just post code; however, this is something I've never tried before and appears to have some value:

In this case, I was simply entering an arbitrary value for a new "child" in the child subform for the current parent using a command button...

Private Sub zctrl_cmd_addachild_Click()
    Dim zSfrm As Form
    '
    'strictly speaking, one doesn't need to create the form object; however, this way there's less chance for typos
    '
    Set zSfrm = Me!zctrl_sfc_Child.Form
    '
    'Here You have to reference the sub-form-control that contains the actual sub-form... I always rename these controls
    Me!zctrl_sfc_Child.SetFocus
    '
    'now that the sub-form has the focus, move to the new record
    DoCmd.GoToRecord record:=acNewRec
    '
    'enter the value you want for the control (or the field in the recordset of a bound form)
    'I suggest referencing the control - this way we're sure that we're acting on the form!
    'probably should add code to verify that we're actually on a new record in the subform; however, I leave that for the developer.
    zSfrm.zctrl_txt_child_name = "cmd_" & Format(Now(), "yymmddhhnnss")
    '
    'check to see if the subform has an edit and save if so
    If zSfrm.Dirty Then zSfrm.Dirty = False
    '
    'NORMALLY, you'd want error trapping in this code
    'however, just for the demo, I'll simply clear the object
    Set zSfrm = Nothing
End Sub

I've tried the above code and it works in my database (Access2013 both in Win7 and Win8.1) I believe that the reason this works is because we're acting directly upon the form which still has scope for the Master/Child field relationships established at the form level; thus, Access knows to create the related field entries for the new record.