How to pass a variable in one form to a variable in another form?

[Jamen98]

I'm trying to pass the "ADA_Member_Fac tor" variable from on form to another. The first form that puts a value in the variable is below:

Option Compare Database
Dim ADA_Member_Factor As Double
Dim final_amount As Currency

'---------------- ^is in the General Dec's section----'

Private Sub Calc_Final_Rate_Btn_Click()
On Error GoTo Err_Calc_Final_Rate_Btn_Click

    Dim db As Database
    Dim ws As Workspace
    Dim ADA_Member As Recordset
    Dim sql As String
    
    Dim Risk_Mgmt_Credit As Recordset
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.Databases(0)
      
    sql = "SELECT Membership_Credit_Table.FILING_STATE, Membership_Credit_Table.Membership, Membership_Credit_Table.Membership_Rate FROM Membership_Credit_Table WHERE (((Membership_Credit_Table.FILING_STATE)='" & [Forms]![Credit_Debit_OCC_Form]![FILING_STATE] & "' ) AND ((Membership_Credit_Table.Membership)=" & [Forms]![Credit_Debit_OCC_Form]![OCC_ADA_Combo] & "));"
    Set ADA_Member = db.OpenRecordset(sql)
    ADA_Member_Factor = ADA_Member!Membership_Rate
    MsgBox ADA_Member_Factor, vbOKOnly, "Test text" 

' successfully populates msg box with the appropriate value.

    Dim stDocName As String
    stDocName = "Final_Rate_Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, ADA_Member_Factor

final_amount = CDbl(Me.Base_Rate_Occ_Step3_txt) * ADA_Member_Factor

Exit_Calc_Final_Rate_Btn_Click:
    Exit Sub

Err_Calc_Final_Rate_Btn_Click:
    MsgBox Err.Description
    Resume Exit_Calc_Final_Rate_Btn_Click

End Sub

---------------

The second form (which should pull up a message box with the variable, show's up nothing).

Option Compare Database

'-----------^in the General Dec's section-----------'

Private Sub Form_Load()
    MsgBox Forms!Credit_Debit_OCC_Form.ADA_Member_Factor, vbOKOnly, "Test text"
End Sub

Inside the first form, the msg box properly displays the variable value. But the second form msg box is blank... I have searched several forum's and tried a few different approaches, but none have worked... Any thoughts, recommendations are greatly appreciated!

[Steven Kogan]

One solution is to place your variable declaration in a module instead of a Form, using Public.

Option Compare Database
Option Explicit

Public ADA_Member_Factor As Double

This puts the variable scope beyond a single form.

[NeoPa]

You're some way there already, but not correctly passing or accessing the value passed. The thing to look up for the full details is OpenArgs, but let me see if I can take you through some of the basics here.

We're talking first about your line #28 :

DoCmd.OpenForm stDocName, , , stLinkCriteria, ADA_Member_Factor

You're passing the value through in the fifth parameter in the call (which should really be DataMode). OpenArgs is the seventh parameter. I generally recommend that you use the form of the call that uses the parameter names explicitly whenever there are many parameters and exspecially if any in the sequence are left out (as here) :

Call DoCmd.OpenForm(FormName:=stDocName, _
                    WhereCondition:=stLinkCriteria, _
                    OpenArgs:=ADA_Member_Factor)

If you must use the positional parameters though then :

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , ADA_Member_Factor

When handling the value coming into the form, it seems it is available only in the Form_Open event procedure (as Me.OpenArgs). I believe that once this procedure has finished executing the property is no longer availabe. Your code should then be :

Option Compare Database
 
'-----------^in the General Dec's section-----------'
 
Private Sub Form_Open()
    MsgBox Me.OpenArgs, vbOKOnly, "Test text"
End Sub

[Jamen98]

NeoPa - Thank you! That worked!!!

[NeoPa]

It's always a pleasure to help :)