How to Use ActiveControl with ActiveForm to Know Where to Return a Value in Microsoft Access

Hope this helps,

-Raymond

Raymond:  thank you for responding. I guess I wasn't clear enough with my original question. I don't have any problem calling the form I need, just how - if possible - to pass a parameter to that form. The problem is that each user click/choice has a different numeric parameter that needs to get passed to the form that open. VBA behind that form uses some input fields on the form, plus the parameter it expects to determine which actual report to open. I'm not sure the method in the video you suggested - which I did watch - is the answer I'm needing. The other possibility, of course, is that I'm just not smart enough in Access 'ways and means' to recognize it.

Use OpenArgs to pass a value to the form being opened.
Custom MsgBox 2

The OpenArgs function has gotten me part of the way to my goal. Thanks for those suggestion to both of you.
However, I'm not quite there.  Here's what I've done. The first few lines are from the calling menu form.

1   Dim OpenWhat As String, Args As String
2   OpenWhat = MenuList.Column(4)
3   If OpenWhat = "" Then
4       OpenMenu MenuList                                   '   open another menu
5   Else
6       Args = "ReptNum=" & MenuList.Column(5)
7        DoCmd.OpenForm FormName:=OpenWhat, OpenArgs:=Args
8   End If

OpenArgs is passed to form "InputDatesF" (from MenuList.Column(4))
with a value like "21" (from MenuList.Column(5))

OpenArgs does get passed as "ReptNum=21"
So far, so good.

[ReptNum] is a General Number text box field on the opened form [InputDatesF] that expects the number 21 in my example. Additional code in the Form_Load() event uses that number to open the correct report.

1Private Sub Form_Load()
2Dim NameValue As Variant
3Dim X As Long
4Dim Args As Variant
5Args = Split(Me.OpenArgs, "=")
6For X = LBound(Args) To UBound(Args)
7   NameValue = Args(X)
8   If UBound(NameValue) = 1 Then
9       Select Case NameValue(0)
10          Case ReptNum
11              Me.ReptNum = NameValue(1)
12          Case Else
13              MsgBox "Unknown value"
14      End Select
15  End If
16Next
...
End Sub

A Debug.Print of OpenArgs shows as ReptNum=21 which, to me, looks right.

However, when executed, I get a Run-time Error #13 Type Mismatch with the cursor pointing to line #6

So, I'm still stuck not knowing how to load the number 21 into the text box [ReptNum] on form [InputDatesF] ?  I suppose it's something extraordinarily simple, but it's sure beyond my VBA knowledge level.

A shot in the dark, maybe try on line 4:

Dim Args() as Variant

This will expressly tell Access/VBA that you want "Args" to be an Array of type variant.

Donald: I didn't know that () was an option. Unfortunately, in this case, it didn't make any difference as I'm still getting the mismatch error, but thank you.

What data type is being returned for Args?

in For X = LBound(Args) To UBound(Args) Args should be numeric.

Kevin:  thanks for responding, and I apologize for my delay (a bit of a flu bug).

On my form ReportsControlF there is a text box named MenuList. It is populated from table ReportsMenuT. When MenuList is clicked, here's the VBA:

Private Sub MenuList_Click()
    Dim OpenWhat As String, Args As String
    OpenWhat = MenuList.Column(4)
    If OpenWhat = "" Then
        OpenMenu MenuList
    Else
        Args = "ReptNum=" & MenuList.Column(5)              
'   column 5 is a number field in table ReportsMenuT named Rept2Open
        DoCmd.OpenForm FormName:=OpenWhat, OpenArgs:=Args
    End If
End Sub

In the above Sub:
FormName:=OpenWhat equates to form InputDatesF (column 4)
OpenArgs:=Args equates to "ReptNum=21" (as an example)

When InputDatesF opens, here's the On Open VBA:

Private Sub Form_Load()
Debug.Print "OpenArgs= ", OpenArgs  (shows ReptNum=21 in the Immediate window)
'--
    Dim NameValue As Variant
    Dim X As Long
    Dim Args() As Variant
    Args = Split(Me.OpenArgs, "=")           << flagged as Type Mismatch >>
Debug.Print "Args = "; Args
    For X = LBound(Args) To UBound(Args)
        NameValue = OpenArgs(X)
        If UBound(NameValue) = 1 Then
            Select Case NameValue(0)
                Case ReptNum
                    ReptNum.DefaultValue = NameValue(1)
                Case Else
                    MsgBox "Unknown value"
            End Select
        End If
    Next

Form InputDatesF has a text box field named ReptNum formatted as General Number
The goal is to load that number 21 (from my example) into ReptNum on form InputDatesF. That number is used later to select the correct report to run.  However, the sub bombs out on line Args = Split(Me.OpenArgs, "=", so I don't know how to answer your questin, Kevin, but again, thank you for taking time to reply.  I admit to being totally stumped.  I just don't have enough VBA experience to master this, it appears.

My thanks to all who responded. I know you don't have to do that, so I really appreciate your time & thoughts.
I did get the issue resolved by finally succumbing to wiles of CoPilot. It took several "that didn't work" responses before CoPilot finally figured it out, but I do now have Richard's multi-level menu working ... almost.
My menu choices load the InputDatesF, passing the report number to run. I fill in the fields, and run the report, all as it should be.
However, if I then click on a different report to run, InputDatesF re-opens but the previous report number is still showing. I can cancel the report, then click on the (2nd) report choice again, and now when InputDatesF opens, the proper report number is showing.
This is not a big deal as my Cancel button clears the previous data, but I do have to make a couple extra clicks to print the next report. All the Cancel button does is execute a DoCmd.CancelEvent. I have that same command as the last thing the OK button does, but the effect is somehow different.
As I said, not a big deal...

You're very close, but you're making this harder than it needs to be.

Since you're only passing a single value, you don't need to build a name=value string or split anything apart. OpenArgs can just carry the number directly.

On your calling form:

DoCmd.OpenForm FormName:=OpenWhat, OpenArgs:=MenuList.Column(5)

Then in your form's Load event:

If Not IsNull(OpenArgs) Then
    ReptNum = CLng(OpenArgs)
End If

That's it. No Split, no loop, no parsing.

The error you're getting is also coming from a couple small issues:

You're indexing OpenArgs instead of your Args array, and you're treating a single value like it's an array with UBound.

Fixing those would help, but honestly, you don't need any of that code in this case.

As for the previous value sticking around, that's because the form is likely not being fully reset between uses. Easiest fix is to explicitly set ReptNum every time in Form_Load like above, or clear it in Form_Current or when the form opens.

Richard:  thank you very much for that simplified method to pass the report number to form InputDatesF. My lack of VBA skills is evident (but I am trying)
I tried clearing the form fields in the Form_Open event, but that does not do the job - for whatever reason. I still have to Cancel and select the report again.  
It doesn't work in On_Current either.
For example:  if I select the first report on ReportsControlF, form InputDatesF opens, I fill in a date, click OK, and the report runs as a PrintPreview. I close that preview, and ReportsControlF is visible. I click on the 2nd report, form InputDatesF opens, and all the values from that first report are still visible, not for the report I just selected.  I click Cancel, select the 2nd report again, and InputDates is ready to run the 2nd report.