By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Module level variable not holding its value

Seth Schrock
Expert 2.5K+
P: 2,941
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm FormName:="frmAuthorizedPeople", _
  2.                WhereCondition:="CustomerID_fk = " & Me!CustID_pk, _
  3.                OpenArgs:=Me!CustID_pk
  4.  
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. lngCustomerID = Me.OpenArgs
  3.  
  4. 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.
Expand|Select|Wrap|Line Numbers
  1. ?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? frmAuthorizedPeople 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.
Feb 25 '14 #1

✓ answered by topher23

Since a splitform does, in effect, create two instances of a form, it's probable that an object module-level variable would never work. If you don't want to explicitly use a global variable, perhaps a TempVar which is created when you need it and removed as soon as you don't would strike your fancy. With that, you could bypass OpenArgs altogether.

Share this Question
Share on Google+
18 Replies


zmbd
Expert Mod 5K+
P: 5,397
how did you declare: "lngCustomerID"
Feb 25 '14 #2

Seth Schrock
Expert 2.5K+
P: 2,941
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 25 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
This should be at the top of your form's code module
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private lngCustomerID As Long
  5. 'Change to public if you need access to the variable outside of the form while it's open
  6.  
  7. Private Sub Form_Load()
  8. 'or the open event etc....
  9. End Sub
  10.  
Feb 25 '14 #4

Seth Schrock
Expert 2.5K+
P: 2,941
That is how I have it, and why I'm so confused as to why it isn't working.
Feb 25 '14 #5

zmbd
Expert Mod 5K+
P: 5,397
... do you have DIM
or
Private/Public?

You state DIM in your reply.
Feb 25 '14 #6

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Feb 25 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Feb 25 '14 #8

zmbd
Expert Mod 5K+
P: 5,397
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.
Feb 25 '14 #9

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub LoadVariable()
  2.  
  3. If lngCustomerID = 0 Then
  4.     If Not IsMissing(Me.OpenArgs) Then
  5.         lngCustomerID = Me.OpenArgs
  6.     End If
  7. End If
  8.  
  9. 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.
Feb 26 '14 #10

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 26 '14 #11

Seth Schrock
Expert 2.5K+
P: 2,941
Duh. I guess I'm back where I started in regards to that. Thanks for pointing this out Rabbit.
Feb 26 '14 #12

NeoPa
Expert Mod 15k+
P: 31,494
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).
Mar 1 '14 #13

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Mar 3 '14 #14

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 4 '14 #15

topher23
Expert 100+
P: 234
Since a splitform does, in effect, create two instances of a form, it's probable that an object module-level variable would never work. If you don't want to explicitly use a global variable, perhaps a TempVar which is created when you need it and removed as soon as you don't would strike your fancy. With that, you could bypass OpenArgs altogether.
Mar 5 '14 #16

NeoPa
Expert Mod 15k+
P: 31,494
That makes sense. It probably means that even a Public defined variable wouldn't be adequate to your needs. Try it first though, anyway.

Otherwise, Topher's suggestion of TempVars sounds like a good solution. It's a shame, as it helps (with maintenance etc.) to have variables related to an object and its work be stored within the design of the object itself rather than elsewhere.
Mar 5 '14 #17

Seth Schrock
Expert 2.5K+
P: 2,941
Okay, finally got back to this project. Public didn't help anything, so I'm going with the global variable declared in a separate module.

You would think that Microsoft would provide a way to reference the correct instance of the form when using split forms.
Mar 10 '14 #18

zmbd
Expert Mod 5K+
P: 5,397
agreed.

Guess what I found here on Bytes: http://bytes.com/topic/access/answer...-access-2007-a
Appears that this isn't the first time this shows up.

It would seem to me that if there are two instances then in the forms collection both should be visible, something to try. Wish I had time to play with that concept today howver, we've lost another in the lab; thus, I'm now wearing 5 hats.
:(
Mar 10 '14 #19

Post your reply

Sign in to post your reply or Sign up for a free account.