Fill in text box based on value from main form

[Guest]

Hello;

I have a form with several command buttons. When clicked, these buttons
call up various subforms. All forms are related by one field in my main
table (represented on the main form), "Hole_ID". All other tables (i.e.
subforms) have a "Hole_ID" field on them. What I want to do is auto-fill the
"Hole_ID" field on all new records entered in on these subforms. I'm under
the impression that the way to do this is to define an event procedure in the
subforms' command buttons. This is what I have written for one of the
subforms:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrm_DDH_Structure"

stLinkCriteria = "[Hole_ID]=" & "'" & Me![Hole] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub

I'm a newbie when it comes to this vb stuff, so I'm not sure what I'm
missing here. Basically when I run the main form and click on a button for a
subform, it pops up alright but the new record has a blank field where the
Hole_ID should be. What should I do?

Thanks,
Casa

 

[Nikos Yannacopoulos]

Casa,

One easy trick to do the job is to use some simple code in the
"subform's" Current event to look up the hole id from the main form in
new records. Assuming the main form is called frmMain, and the hole id
control is named [Hole] in both forms (text field, as implied by your
code), then the code in the On Current event of the subforms would be
something like:

Private Sub Form_Current()
If Me.NewRecord Then
Me.Hole = Forms!frmMain!Hole
End If
End Sub

HTH,
Nikos

 

[Nikos Yannacopoulos]

I forgot to say why I enclosed "subform" in quotes: the reason is, a
real subform is one that lives inside another form (which you can link
to the parent form on a field set of fields, and achieve the automatic
Id population for new records); what you are doing instead is open a
second, independent form, using a value form the first one for
filtering, so that's not a real subform in Access terms.

HTH,
Nikos