Sub-form variables being reset to zero

[Miles R]

I have some variables that I set in the Open Event of a sub-form of my Main Form. I use these in the Current Event of the sub-form.


The first time the Main Form is loaded, the Open Event of the sub-form is triggered and the variables are set. I have checked these are set correctly.
However, the second time the Current Event is triggered, these variables have been reset to zero (they are only ever set in the Open Event).

I have got round this by moving the variables to a Module and making them global.

It seems to me that once the Sub-form has been loaded from the Main Form, any variables set at the top of the Sub-Form (i.e. after the Option Explicit) are all reset to zero.
Its as if the sub-form is being closed and just left as part of the main form - which would of course mean any variables set in the Open Event would lose their value.

I have read that any untrapped error may set variables to zero, but the code seems to work without any errors being apparent.

Can anyone confirm my suppositions about variables declared at the top of sub-forms.

[June7]

If you want variables to retain values for duration of work session, declare them in module header. If you want them to be available to multiple modules, then must be declared in header of a general module. You seem to have tried both of these.

If you want variables to persist in spite of run-time errors, use TempVars.

Could provide db for analysis.

[Micron]

I dunno for sure, but the way I interpret some of that, it's not readily apparent to me that the OP knows that the subform loads first, then the main form. That may be the root of the problem.

[Miles R]

Thanks, but yes I do know that if I want a variable to be available in multiple modules it must be in a general module. In this case it only needs to be available in the sub-form, which is why I originally put them in the module header.
However for some reason they are being reset.
When I look at what forms are open using code below, the sub-form is not in the list, but maybe subforms do not appear in the list of open forms.
Set DbO = Application.Forms ' Collection of all the open forms

Frms = ""
For Each DbF In DbO ' Loop all the forms
If Frms = "" Then
Frms = DbF.Name
Else
Frms = Frms & ", " & DbF.Name
End If
Next DbF

[Miles R]

Thanks, but I am aware that the subform loads first - discovered that years ago on another project.

I was hoping that someone might know off the top of their head that module variables in a subform get reset.

I might create a small database with a main form and a subform just to test this. If the variables don't get reset, then there must be some problem in my main database, although any problem is not apparent as the database if functioning correctly with the variables in a separate module.

[Miles R]

Rather than create a new database, I tried this in my other database that has been running successfully for years without problems.
It has a main form and a subform in it.
This is the essence of the code in the subform:-

Option Compare Database
Option Explicit


Dim testvalue As Integer


' This event handler does initialisaion of various values.
' It is called before any events on the main form as sub-forms are loaded first.
' This must be done before anything else.
Private Sub Form_Open(Cancel As Integer)
Dim rstQuery As Recordset


testvalue = 45
MsgBox testvalue

.................
Private Sub Form_Current()

MsgBox testvalue

.................

Now, when the program is loaded, testvalue is shown to be 45 both in the open event and the current event.
However, any subsequent current events show testvalue to be 0, so it is being reset.

[Miles R]

Ok,
Have created a test database and this shows the module variable in the subform being reset. If this is true, then best not to put any variables in the module header of a subform.
Unless I am missing something.
Database uploaded.

[Micron]

maybe subforms do not appear in the list of open forms.

That is true. If the following doesn't help I'll take a look at what you posted but need java first!
I'd say that in your posted example, the value isn't being reset - it's showing you the default value for the data type (0 for long, integer, etc.).

IIRC, VB variable scope is public by default. VBA scope is private by default. So module level variables are Private unless declared public. You could try this simple exercise in a general module. Declare a boolean without Public or Private keyword. Test it in the immediate window and when you hit return you'll get nothing. Go back, use Public keyword, save, compile and test again in immediate window. You should get False as a result. In your posted example, the variable is reset as soon as the code finishes since it is private to that procedure.

https://learn.microsoft.com/en-us/of...and-visibility

EDIT
Apologies if I've misunderstood (having second thoughts) because now I'm not sure what is meant by

However, any subsequent current events show testvalue to be 0, so it is being reset.

Does that mean current events in the same form or other forms?

[Micron]

Doing anything on this site has become a problem lately. Took a look at your db. I put this on the main form and added a button:

Option Compare Database
Option Explicit
Dim strTest As String

Public Sub Form_Current()
strTest = "dog"
End Sub

Private Sub Command16_Click()
MsgBox strTest
End Sub

That behaves as you would expect (msgbox says "dog"). I can only guess that the same approach doesn't work with a subform because the subform isn't loaded into the collection of forms. To solve that with a subform, put your variable in a standard module or perhaps use some other approach that doesn't involve module level variables on subforms.

[Miles R]

Thanks for looking at that.
As you will have seen from the example DB, the value set in the subform Open Event and declared in the subform module header, is being reset to blank when the subform Current Event trigger button is pressed.

I'm surprised that no one seems to have come across this before or it is a known feature of Access when declaring variables at the top of a sub-form. Clearly the values don't persist, even though they are still in scope.
Its almost as if once the subform is loaded from the main form it is closed and reopened without going through the Open Event.

Anyway, as you say the variables need to be in a standard module so the values are never reset.

I'm sure someone else is going to come a cropper with this in the future, as it is not at all obvious why this should happen. Maybe someone involved in the programming of Access could shed some light on this if they ever look at this site.

I shall mark this thread as solved, even though no-one has a definitive answer on the cause.

[Micron]

I can only imagine that after subform events have run once and set variable value, once the code completes then the variable value defaults. It's like the variable scope is only to the procedure regardless of where you put the variable. However, if you open the subform directly, it will behave as you expect, so I can only imagine that it has something to do with the subform not existing in the forms collection, because that is not true if you open the subform directly.

[June7]

I tested this in my db.

Option Compare Database
Option Explicit
Public intX As Integer

Private Sub Form_Open(Cancel As Integer)
intX = 222
End Sub

Private Sub Form_Current()
Debug.Print intX
End Sub

Variable retains value when form is used as a subform.

Then I tested your db. Variable retains value navigating records.

[Miles R]

June7,

Thanks for trying this, but how do you explain that when you press the button on my test database form, it clearly shows that the variable from the subform has been reset to blank - unless it doesn't on your version?

[Welshgasman]

Well it is not the current event, as if you move to another record, test is still shown?
Only when we use your button does it disappear?

THis does not look right to me? Where did you get this syntax from?

Private Sub Command2_Click()
    [Form_Subform].Filter = "ID = 1"
    [Form_Subform].FilterOn = True
End Sub

However

Private Sub Command2_Click()
'    [Form_Subform].Filter = "ID = 1"
'   [Form_Subform].FilterOn = True
Me.Child14.Form.Filter = "ID=1"
Me.Child14.Form.FilterOn = True


End Sub

will still show Test?

[June7]

Ooops, I forgot to test button.

Agree with Welshgasman and revised code does make a difference.