Module level variable not holding its value

[Seth Schrock]

I've got a weird situation that I can't figure out what is happening. On Form1, I have a button to open another form using the following code:

DoCmd.OpenForm FormName:="frmAuthorizedPeople", _
               WhereCondition:="CustomerID_fk = " & Me!CustID_pk, _
               OpenArgs:=Me!CustID_pk

Since I don't like to count on the openargs variable for a long time with the form open, I created a module level variable to store the value in OpenArgs.

Private Sub Form_Open(Cancel As Integer)
lngCustomerID = Me.OpenArgs

End Sub

So I put the break point on the Docmd.OpenForm. With that highlighted, I entered the following in the immediate window and ran it.

?Form_frmAuthorizedPeople.lngCustomerID

Since the form isn't opened yet, I get the invalid use of null error as I would expect. I then step to the setting of the variable and that runs fine. So with the End Sub from the On_Open event highlighted, I run my immediate window command again and I know get the value that I passed through the OpenArgs parameter. Again, as I would expect. I press F8 once more and now I have the End Sub from the button's event highlighted and I run my immediate window again and now it returns 0. Why did the value of lngCustomerID change? frmAuthorizedPe ople is a split form, it isn't modal, isn't popup. I can't think of any other details that might help solve this. I have done this many times and never had a problem. I also checked to see what the form's openargs value was and it stayed correct even while the variable changed to 0.

[zmbd]

how did you declare: "lngCustome rID"

[Seth Schrock]

Dim lngCustomerID As Long

at the top of the form's module. I also tried it as a string, but that didn't work either.

[zmbd]

This should be at the top of your form's code module

Option Compare Database
Option Explicit

Private lngCustomerID As Long
'Change to public if you need access to the variable outside of the form while it's open

Private Sub Form_Load()
'or the open event etc....
End Sub

[Seth Schrock]

That is how I have it, and why I'm so confused as to why it isn't working.

[zmbd]

... do you have DIM
or
Private/Public?

You state DIM in your reply.

[Seth Schrock]

I did catch that difference later and I tried Private, but it does the same as just Dim. I almost always use Dim and it has always worked in the past.

[Seth Schrock]

Now to throw in another reason to scratch your head (or pull out your hair). I have moved on to another section of the database that needs create and I realized that I was going to need this again, so I did it just like I did the form that isn't working (using dim not public/private) and it works perfectly. The new form is not a split form. Could this be the issue? I'll test it just to find out, but I wouldn't think that this would effect anything.

Testing Results:
I changed the form to Single Form and the variable worked. I changed it back to Split Form and it went back to not working. What a PAIN! Now I will have to see if I can create a work-around that won't be broken by the split form.

Thanks for trying to help me out Z.

[zmbd]

Use the Public/Private in the split form...

Be aware of the fact that the split form is a special construct of Microsoft and it expects certain things, using DIM to declare at the module level is non-standard; thus, more than likely to be reset when the scope of the calling code is closed.

[Seth Schrock]

Well, I changed it to Private and that didn't help. In looking around online I found some that had the same issue, but never a good solution. However, they did mention that split forms at least act like they create two instances of the form. However, I couldn't ever get anything to work that would allow me to change which instance I was referencing. So I made a work around inspired by one of the posts that I found.

Public Sub LoadVariable()

If lngCustomerID = 0 Then
    If Not IsMissing(Me.OpenArgs) Then
        lngCustomerID = Me.OpenArgs
    End If
End If

End Sub

This seems to make it work. I call it inside my subs that use the variable just to make sure that it has a good value and now the value seems to be lasting while the form stays open like I want.

[Rabbit]

I have not used split forms so I can't speak to the main issue but I thought the whole point of this is to avoid using OpenArgs? If you're going to use this workaround, you might as well just use OpenArgs.

[Seth Schrock]

Duh. I guess I'm back where I started in regards to that. Thanks for pointing this out Rabbit.

[NeoPa]

Have you tried using Public yet? I've read through the thread and that was explained very early on but without you ever seeming to respond.

If you are testing it from the immediate pane while the code of the form object is not active then your test would have to :

1. Refer to the particular (open) instance of the form (IE. Not the class).
2. Refer to a Public variable.

Only the code running within the object module itself has access to Private variables of that instance of the form.

The Immediate Pane can only access those objects when the code has broken (Paused not crashed.) within the object module.

NB. Dim and Private in the main area (Outside of a procedure) are equivalent. However, for general consistency of code it is recommended (If by no-one else then certainly by me.) that Private is used outside of the procedures (as it describes the scope more clearly) and Dim is used within them (Public/Private not allowed within procedure code).

[Seth Schrock]

I didn't see Public as being a solution suggested on its own, but rather as an option that should work the same a Private, in contrast to using Dim. I will however try Public when I get over being sick and can get back to work.

One note on my accessing the variable from the immediate window... If I set the form to single form view, then my testing worked. Changing it back to split form broke it, so I think that the issue isn't with how I'm calling it in the immediate window.

As far as the Dim vs Private usage, I was not aware of any recommended practice before this thread. I will try to remember this in the future so that my code can follow best practices more closely.

I'll let you know how my testing turns out.

[NeoPa]

Sorry to hear you're sick Seth. Get well soon!

The Public recommendation occurs twice. It's possibly not expressed too clearly as a different solution, but if you look at line #5 of the code in post #4 you'll see it as a separate suggestion. Scope is something that can get tricky. So is the actual reference itself. It may prove important how you reference the form. Hence my making the particular point in my earlier post to reference the instance rather than the class.

This may well explain why it's different between split forms and single ones. Not because the scope isn't the determining factor but because single form references can determine the intended reference even when not 100% correct. Just as dates of the wrong format are accepted in Access if the day is greater than 12. #20/10/2014# is recognised as 20 Oct 2014 in SQL even though it expects the day as the second value. So, try the reference based on Forms("FormName ") after you've declared it as a Public variable. After that test go ahead and see if other references work, but do that one as the fundamental test of the idea.