Skipping Over a Form, with an If-Then Statement, With OpenArgs (1 Viewer)

[pitt_ph]

Hey all,

Although I have a general understanding of the openargs property, I'm still confused on if openargs can be used over multiple instances, between multiple forms, in the case that a form is indirectly passing parameters to another form, a couple of forms down the line.

As I have it in the attached database (which is a copy of my actual database with fields renamed for confidentiality purposes), I have three forms.

The user would go from the Switchboard Form, to Form #1 (=frm1), then to Form #2 (=frm2), Form #3 (=frm3), and return back to the Switchboard Form. The idea is that the user would go through a series of forms, entering in data, before returning back to the "main" form. There are buttons that allow the user to go back and forth between forms, should they choose to change a entry later on in the form-filling process.

(I also have autonumbered fields in the tables to denote unique entries, but these do not show up on the forms themselves.)

On the first form, I have the FormID denoted for the overall form (as all of these individual forms are linked through the FormID. I also have a combo box with a variable that should be changing whether or not controls on both frm1 and frm3 are enabled or disabled. This is denoted as "VariableToChange" in the sample database. I also have a field on frm1 that shows the effect of changing VariableToChange -- it enables/disables the second field.

The second form just has a field, right now, for FormID.

The third form is a continuous item form, with a FormID in the corner. FormID has been defaulted to stay exactly the same (as it is not the FK), so I'm confident that I'm recording multiple records from the same form. (This defaulting has been done with the module LastComboRow.)

I want to pass an If-Then statement, triggered by VariableToChange, that would change whether or not specific controls on frm1 and frm3 - but not frm2 - are enabled or disabled.

I found that if I somehow kept both frm1 and frm3 open at the same time (and uncommented the pertinent lines in my VBA), the enabling/disabling worked like a charm. However, the moment I close frm1, I no longer can enable/disable the controls on frm3.

I know that the openargs argument works when there is a button from a calling form to a receiving form -- that is, openargs will automatically load upon that button being clicked and the form loading.

However, in cases, where openargs isn't being immediately used -- but is going to be used a couple of forms into the future -- should we just 'chain' openargs, and make sure that we mention openargs in every button between each form?

I hope that this post hasn't been terribly long/confusing -- I just don't know how to properly work with openargs, and all the sample databases I've seen so far only work with two forms, and not three (or four, or five...).

Thank you in advance!!!!

 

[pitt_ph]

For what it's worth, these are the sources I used so far in these forums to look up openargs:

• Pass Current Record OpenArgs
• Passing recordset in OpenArgs
• Using OpenArgs to Pass a Variable's content

 

[John Big Booty]

You need to test the OpenArgs in the On Load event of each form. If OpenArgs have been passed to the first form then they can be appended to the OpenForm method for subsequent forms.

 

[pitt_ph]

Hm, alright. So if I understand this correctly, OpenArgs has to be used in BOTH when the form is being opened and when the form is being loaded every time someone switches from one form to another? So, if there are three forms, OpenArgs has to be used twice?

Thank you for the feedback! I certainly didn't think of it that way...

 

[John Big Booty]

Correct, OpenArgs is specific to the form that is being called in the OpenForm method. So unless you use it in the OpenForm call it will return a Null result if you test it (OpenArgs) in that form.

 

[pitt_ph]

Hmmm... I guess all of this makes sense. However, I'm wondering -- if I have an if-then loop which can enable/disable controls on a form, how would I reference that loop?

I know I'm missing some fundamental VBA basics with this, but how do you parameterize (or 'tie' a variable name to) a particular loop? I have NO idea how to work with this.

The VBA that I put in the tie-in (without the OpenArgs argument) is (parameterization attempts are in bold):

Private Sub VariableToChange_Change()[B]

Dim {VarName} As String[/B]

'Showing the beginning of an if-then loop trying to pass arguments *over* a form.
'This is on the first form, denoted as frm1 in this example.
'I want this if-then loop to be applicable to the third form, frm3
'as I'd like to enable/disable fields based on previous form's responses.

[B]{VarName} = [/B]If Me.VariableToChange.Value = "0" Then
    Me.Label1.ForeColor = vbBlack
    Me.Field1.ForeColor = vbBlack
    Me.Field1.Enabled = True
'   Forms!frm3!Field1.Enabled = False
'   Forms!frm3!Field2.Enabled = False
'   Forms!frm3!Field3.Enabled = True
'   Forms!frm3!Field4.Enabled = True
'   Forms!frm3!Field5.Enabled = True
'   Forms!frm3!Field6.Enabled = True
Else
    Me.VariableToChange.Value = "1"
    Me.Label1.ForeColor = vbWhite
    Me.Field1.ForeColor = vbWhite
    Me.Field1.Enabled = False
'   Forms!frm3!Field1.Enabled = True
'   Forms!frm3!Field2.Enabled = True
'   Forms!frm3!Field3.Enabled = False
'   Forms!frm3!Field4.Enabled = False
'   Forms!frm3!Field5.Enabled = False
'   Forms!frm3!Field6.Enabled = False
End If

I know that, in some way, the VBA comprising this loop is severely flawed, but I'm not sure how to parameterize the loop in the first place to pass into OpenArgs. Does this make sense?

 

[John Big Booty]

The following schema will loop through all controls on a form;

  Dim ctl As Control

  For Each ctl In Me.Controls  
   [COLOR="DarkGreen"] 'your test code here[/COLOR]
  Next ctl

 

[pitt_ph]

Hey John,

If I understand this correctly, this code will loop for ALL controls, so that the if/then loop is related to every control, right? (There's no way of being selective and only applying the if/then loop with some controls?)

(Sorry for asking so many clarifying questions -- I just want to make sure I understand what's going on in each piece of VBA that I use!)

 

[John Big Booty]

Within that loop you could put a logical test and check for specific type(s) of controls and only change those as required.

For example;

    Dim ctl As Control
   
    For Each ctl In Me.Controls
        If TypeOf ctl Is acTextBox Then
            ctl.Locked = True        [COLOR="SeaGreen"]'Or whatever other property you wish to set[/COLOR]
            ctl.Enabled = False      [COLOR="SeaGreen"]'Or whatever other property you wish to set[/COLOR]
        End If
    Next ctl

 

[pitt_ph]

...I hate to admit it, but although the advice written here so far is sound for other purposes, it doesn't appear to be applicable to this particular case.

I know that, despite looking through a lot of websites telling me what openargs are, it hasn't come any closer to showing me how they can be applied -- or how I can parameterize an if-then statement itself (instead of specifying a set of controls in a separate loop) so the WHOLE statement can be explicitly passed to subsequent forms.

I don't want to specify a specific control type -- because there are *other* combo boxes or text boxes in the real form besides that being affected by the 'enabling' if-then loop. I'm only trying to affect a small subset of controls with this If-Then enabling loop, but I want to affect the controls on multiple forms. I guess that's where I'm tripping up.

I know that I am still woefully unclear on declaring variables in general, and just the thought of implementing OpenArgs is paralyzing me because I don't understand what is going on. I tried the following codes on my sample database, but as you'll see, there are parameterization issues from the very beginning...

On the button going from the first form to the second form, we see:

Option Compare Database
Public Sub cmd_testopenargs_Click()
                
' This is my lame attempt at trying to do OpenArgs. It's not working.'
' The method or dataframe "ctl" cannot be found, apparently.'

DoCmd.OpenForm "frm2", acNormal, , , acFormAdd, acWindowNormal, Me.ctl

End Sub

Private Sub VariableToChange_Change()

'Showing the beginning of an if-then loop trying to pass arguments *over* a form.
'This is on the first form, denoted as frm1 in this example.
'I want this if-then loop to be applicable to the third form, frm3
'as I'd like to enable/disable fields based on previous form's responses.

Dim ctl As Control

  For Each ctl In Me.Controls
    If Me.VariableToChange.Value = "0" Then
        Me.Label1.ForeColor = vbBlack
        Me.Field1.ForeColor = vbBlack
        Me.Field1.Enabled = True
'       Forms!frm3!Field1.Enabled = False
'       Forms!frm3!Field2.Enabled = False
'       Forms!frm3!Field3.Enabled = True
'       Forms!frm3!Field4.Enabled = True
'       Forms!frm3!Field5.Enabled = True
'       Forms!frm3!Field6.Enabled = True
    Else
        Me.VariableToChange.Value = "1"
        Me.Label1.ForeColor = vbWhite
        Me.Field1.ForeColor = vbWhite
        Me.Field1.Enabled = False
'       Forms!frm3!Field1.Enabled = True
'       Forms!frm3!Field2.Enabled = True
'       Forms!frm3!Field3.Enabled = False
'       Forms!frm3!Field4.Enabled = False
'       Forms!frm3!Field5.Enabled = False
'       Forms!frm3!Field6.Enabled = False
     End If

  Next ctl

End Sub

On the second form, where I tried to pass the openargs correctly, I got a message box to pop up, but I ran into Run-time Error #91 when I attempted to actually pass over the forms. After that, I added the whole "If Not IsNull(Me.OpenArgs) Then" statement, but I had absolutely no idea what to include after that statement.

Option Compare Database

Private Sub Form_Load()

'Here, a message box is loading, which I *tried* to name "ctl"'
'but I received the error, "Run-time error '91': Object variable or With block variable not set'
'and I guess I just don't know how to specify the correct variables in this case...'

Dim ctl As Control

    ctl = MsgBox("Lookatme!")

    If Not IsNull(Me.OpenArgs) Then
    '' this is where I'm actually stuck''
    
End Sub

I know that I'm sounding completely clueless here, but I guess.... if it's possible for me to PM someone about this, or if someone could look at the sample database I've attached, it'd be tremendously helpful.

 

[pitt_ph]

I hacked through the OpenArgs entries and came up with something that *appears* to have passed the OpenArgs between the first two forms, but which I've been receiving an error on in the third form.

First Form's Code:

Option Compare Database

Private Sub cmd_testopenargs_Click()

If Me.Dirty Then Me.Dirty = False

DoCmd.OpenForm "frm2", acNormal, , , acFormEdit, acWindowNormal, "VariableToChange"
Close acForm
                
End Sub

Private Sub VariableToChange_Change()

'Showing the beginning of an if-then loop trying to pass arguments *over* a form.
'This is on the first form, denoted as frm1 in this example.
'I want this if-then loop to be applicable to the third form, frm3
'as I'd like to enable/disable fields based on previous form's responses.

If Me.VariableToChange.Value = "0" Then
        Me.Label1.ForeColor = vbBlack
        Me.Field1.ForeColor = vbBlack
        Me.Field1.Enabled = True
'       Forms!frm3!Field1.Enabled = False
'       Forms!frm3!Field2.Enabled = False
'       Forms!frm3!Field3.Enabled = True
'       Forms!frm3!Field4.Enabled = True
'       Forms!frm3!Field5.Enabled = True
'       Forms!frm3!Field6.Enabled = True
    Else
        Me.VariableToChange.Value = "1"
        Me.Label1.ForeColor = vbWhite
        Me.Field1.ForeColor = vbWhite
        Me.Field1.Enabled = False
'       Forms!frm3!Field1.Enabled = True
'       Forms!frm3!Field2.Enabled = True
'       Forms!frm3!Field3.Enabled = False
'       Forms!frm3!Field4.Enabled = False
'       Forms!frm3!Field5.Enabled = False
'       Forms!frm3!Field6.Enabled = False
     End If

End Sub

Upon entering the second form, the loading code is:

Private Sub Form_Load()

Dim VariableToChange As String

VariableToChange = Me.OpenArgs

If Me.Dirty Then Me.Dirty = False

End Sub

I tried to create a button on the second form, called "nextform" that would pass the OpenArgs (which *was* supposed to be "VariableToChange") to the third form. The VBA for that was:

Private Sub nextform_Click()

DoCmd.OpenForm "frm3", acNormal, , , acFormEdit, acWindowNormal, "VariableToChange"
Close acForm

End Sub

However, when this code is run (i.e., this button going from the second to the third form is clicked), I got the error message: "Run-time error '2501': The OpenForm action was canceled."

My question is -- did I not delineate the OpenArgs correctly, and is that why it can't be passed between the second set of forms (between "frm2" and "frm3")? Or is there something entirely different that I'm missing?

Thank you in advance!

 

[pitt_ph]

...Now, I just feel silly. *Really* silly.

As it turns out, I wasn't using OpenArgs correctly the first place. (Duh.) Furthermore, besides OpenArgs, it was a matter of declaring a public versus a private sub, and then referencing the field correctly, to get it to work correctly.

In the first form, what I ended up doing for the button going into the second form was:

Public Sub cmd_testopenargs_Click()

If Me.Dirty Then Me.Dirty = False

DoCmd.OpenForm "frm2", acNormal, , , acFormEdit, acWindowNormal, "VariableToChange"
Close acForm
                
End Sub

I did a similar string in the OnClick event going between the second and the third form.

Since "VariableToChange" became, at that point, a public variable that could be called by any form in the database, I was then able to use VariableToChange in the following manner:

Private Sub Form_Open(Cancel As Integer)

'I know that I'm going to have to put the openargs argument under this event no matter what.
' This whole argument is going to be commented out until I figure out what's going on.

If Forms!frm1.VariableToChange.Value = "0" Then
    Me.Field1.Enabled = False
    Me.Field2.Enabled = False
    Me.Field3.Enabled = True
    Me.Field4.Enabled = True
    Me.Field5.Enabled = True
Else
    Forms!frm1.VariableToChange.Value = "1"
    Me.Field1.Enabled = True
    Me.Field2.Enabled = True
    Me.Field3.Enabled = False
    Me.Field4.Enabled = False
    Me.Field5.Enabled = False
    
End If

End Sub

Surprisingly, to me, this worked wonderfully! This worked like a dream.

THANK YOU to everyone who helped me with this issue! I think this thread should be marked as closed...