Variable Losing Value

[Sherwood]

Greetings,
I am wanting to do something fairly simple, but am unable to get it
working for some reason. I simply want to use the value from a Public
Variable (declared in one module) and use it in the caption of another from.
I am declaring this variable in the module sheet, "form_frmSearch", as
follows:

Public nbr_of_records as long

In the code (in this module sheet) I am assigning it a value as follows:

rstTemp.MoveLast
nbr_of_records = rstTemp.RecordCount

In the second form, "frmTotalRecordsFound", I have the following code in the
Form Load event:

Me.Caption = "Total Records Found - " & nbr_of_records

However, when I run this code (by first launching the Search form, which in
turn displays this second form) there is no value in the variable,
"nbr_of_records". I was under the impression that declaring a variable as
Public (at the top of a form's module sheet) made it public to all modules in
that database. Is there something obvious that I am overlooking?

Thanks in advance!

Sherwood

 

[Ken Snell [MVP]]

Is the form that declares the public variable still open when you try to
read the value? It must be open; otherwise, the variable is destroyed when
the form closes.

Normally, a public variable would be declared in a regular module, not a
form's module.

Also, public variables can lose their values any time an error occurs during
the running of the VBA code and your code does not properly handle the
error. So, usually I don't use public variables except for very short
durations, relying instead on either a field in a table as the place to
store the data, or an invisible control on an open form to store the data.

 

[Sherwood]

Thanks, Ken. Placing the public variable in a regular module sheet solved
the problem.

Sherwood