Setting the RecordSource dynamically with a global variable

[sheetsumon]

How do I pass a global variable defined by the first form to set the
RecordSource of the second form?

I have two forms "MasterForm1" and "MasterForm2." Both of these are linked
to the same table "MasterTable."
The first form's 'Data Entry' property is set to "YES" since it always
accepts new data.
The purpose of the second form is to continue entering data for the same row
in the MasterTable. Therefore, I'm trying to pass the current record number
(in this case, 'masterEntryNo'...Primary Key, AutoNumber) as a global
variable (as defined in a separate VB Module) to dynamically set the
RecordSource for the second form.

I tried the following,

MasterForm1 code:

Private Sub NextForm_Click()

Dim stDocName As String
Dim stLinkCriteria As String

lastRecord = Me.masterEntryNo.Value
prodCategory = Me.prodCatID.Value

stDocName = "MasterForm2"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

MasterForm2 code:

Private Sub MasterForm2_OnLoad()

Dim strSQL As String
Dim strSQL2 As String

strSQL = "SELECT * FROM MasterTable WHERE masterEntryNo = " & lastRecord
strSQL2 = "SELECT productName, prodNameID FROM ProductName WHERE
prodCatID = " & prodCategory

Me.RecordSource = strSQL
Me.productName.RowSource = strSQL2

End Sub

The MasterForm2_OnLoad() doesn't do anything to the RecordSource, no matter
what I try. (My form shows up with #Name? all over it). If I put the above
code into a button on MasterForm2, it seems to set the RecordSource for the
form, but it's always for the next row in the table!?

Can anyone help with this?

 

[Douglas J. Steele]

MasterForm2 doesn't know anything about the variables lastRecord and
prodCategory that you defined in MasterForm1.

To accomplish what you're trying to do, you can use the stLinkCriteria
variable to specify the Where clause for MasterForm2's recordsource (strSQL
in your MasterForm2_OnLoad routine). The OpenForm method has an OpenArgs
argument that can be used to pass anything you want to the form you're
opening, and you can then refer to the form's OpenArgs property to retrieve
that value.

In MasterForm1:

Private Sub NextForm_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "masterEntryNo = " & Me.masterEntryNo.Value

stDocName = "MasterForm2"
DoCmd.OpenForm stDocName, , , stLinkCriteria, _
OpenArgs:=Me.prodCatID.Value

End Sub

In MasterForm2:

Private Sub Form_Load()

Dim strSQL As String

If IsNull(Me.OpenArgs) = False Then
strSQL = "SELECT productName, prodNameID FROM " & _
"ProductName WHERE prodCatID = " & Me.OpenArgs
Me.productName.RowSource = strSQL
End If

End Sub

 

[Albert D. Kallal]

Why do you need to set the record source in the 2nd form?

Why not bind it, and use the where clause. The ONLY code you need is

me.Refresh ' you need this if you allow editing BEFORE
' you launch the 2nd form. Remove this line
' if you dont' allow editing

docmd.Openform "masterForm2",,,"MasterEntryNo = " & me!MsterentryNo

that is ALL you need. The link criteria will filter the 2nd form you open.

Really, this is only one line of code, and you should not need to set, or
run any code in the masterForm2....

The "where" clause of the open form was designed to solve your exact
problem, and you are now writing code to do the same thing!!

 

[sheetsumon via AccessMonster.com]

This works brilliantly.

Albert - the Me.Refresh is definitely needed, isn't it. Thank you for the
advice.
Doug - the OpenArgs information killed two birds with one stone. Much
appreciated!!

Thanks guys