Get the current subform value inside a form

Hi All,

I have created a form called [Stock Reserved Activation]. Inside this form, it contains a subform called [Child0]. This subform shows all the records in a table called [Stock Transaction]. This table contains a field [RefNo].

What I want to do is when the user scroll to the specific record in the subform and click a button [Detail Update], then another form will be opened to show the corresponding record with the same [RefNo] as in the subform. How can I get the [RefNo] in the subform and pass it to the other form?

Thanks!

Rgds,
Gloria

Gloria,

You have two options:

1.
Using WhereCondition argument of OpenForm statement.

include the following code into your Click or DblClick event handler of the subform's field (the convention is DblClick):

DoCmd.OpenForm [form_name], , , [where_condition]

where [form_name] is the name of the form you wish to open, and [where_condition] is a string expression that filters the opening form.
WhereCondition argument is just like a WHERE clause of an SQL statement, but without the WHERE keyword.

2.
Using OpenArgs optional argument of OpenForm statement.
In this argument you can pass any value to an opening form.
To do this, use:
DoCmd.OpenForm [form_name], , , , , , [openargs]
where [openargs] is any value or a variable storing the value you wish to pass to the openng form. This can be the control containing RefNo.

Then, when the form is opening, use this in OnOpen event handler to find the record (if RefNo is numeric):

'****************
If Not IsNull(OpenArgs) Then
Me.RecordsetClone.FindFirst "[RefNo]=" & OpenArgs
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
'****************

If RefNo is a string, use:
'****************
If Not IsNull(OpenArgs) Then
Me.RecordsetClone.FindFirst "[RefNo]='" & OpenArgs & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
'****************

I hope this makes sense.

Hi TBÁrpi,

I found another solution myself and seems it works! In the form that contains the subform, there is a [Update Detail] button. When this button is clicked, I write the following event:

Private Sub Detail_Update_Click()

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "ReservedDetail"
strLinkCriteria = "RefNo = [forms]![Stock Reserved Activation]![Child0]![RefNo]"
DoCmd.OpenForm strDocName, , , strLinkCriteria

End Sub

[Stock Reserved Activation] is the form name, [Child0] is the subform name, [ReservedDetail] is the new form I want to open. Is it correct for me to do it like this?

Thanks and Best Wishes,
Gloria

Yes, that's correct.
This is the same as the first solution I posted.
The difference is that it puts the WhereCondition into a string variable first.
It seems like a wizard-created code.