public variable resetting themselves to "" ? why

[paulmcdonnell]

Hi guys

I have a code module which declares public variables when the database opens…

Option Compare Database

'set variables


'set public variable
Public OffersDataSource As Variant
Public SetPropertyID As Integer

'For OUTLOOK AUTOMATION OF APPOINTMENTS
Public ASTARTDATE, ASTARTTIME, AENDTIME As Date
Public ADURATION, ABODY, ALOCATION, ATITLE, ASUBJECT As Variant
Public MailboxID As Variant
Public Emailto As Variant
Public who As String

As I understand it these variables are available to the database and can be used in any private functions.

I have a routine that adds an appointment to an outlook diary.

The first instance that the code is run the variable “Mailbox”, which is set to a variable when the database opens is used and all works fine the appointment added.

If I try to run the code a second time the variable is now “”.

Nothing in my code resets this variable that I can see. It should still be there for a second use surely?

How do I set a variable when the database starts up and then be able to use and reuse this variable again and again until I reset it or set it to “”?

Also when does my module with my public variable execute. This is the only thing I can see that would reset this variable. If my ON click event is initiating the module event above how can I set a variable once and keep it from being reset (it must be public and open to many prodecures though?)

Hope you can help
Cheers
Paul

 

[Mile-O]

Public OffersDataSource As Variant
Public SetPropertyID As Integer

'For OUTLOOK AUTOMATION OF APPOINTMENTS
Public ASTARTDATE, ASTARTTIME, AENDTIME As Date
Public ADURATION, ABODY, ALOCATION, ATITLE, ASUBJECT As Variant
Public MailboxID As Variant
Public Emailto As Variant
Public who As String

It looks like you don't know this, so I'll tell you.

When you dimension a group of variables in one line then you should explicitly declare the data type of each - any without an explicit data type assume the Variant Data Type.

Therefore, with this line:

Public ASTARTDATE, ASTARTTIME, AENDTIME As Date

You are making two Variants and one Date.
The correct line should be:

Public ASTARTDATE As Date, ASTARTTIME As Date, AENDTIME As Date



Now, using Variants for everything is a poor practice as they consume a lot more memory than other data types due to their metamorphic nature. It's always best, therefore, to explicitly declare the Data Type for each variable:

Public ADURATION, ABODY, ALOCATION, ATITLE, ASUBJECT As Variant

Here, these look as if they are all, with the exception of ADURATION, strings.

Ergo, it should be:

Public ADURATION As Date, ABODY As String, ALOCATION As String, ATITLE As String, ASUBJECT As String

I don't know what the Variable in Bold actually is so I just made it a date.
A better practice, again, is to declare each variable on a seperate line.

Public ADURATION As Date
Public ABODY As String
Public ALOCATION As String
Public ATITLE As String
Public ASUBJECT As String

As for variables losing their value, this can happen if there's an error and no error trapping.
Try going through your code step by step using the debugger.

 

[cable]

I have had a similar problem where public variables seem to loose their value between bits of code. I never bothered to track it down, and just passed the variables to each sub/func as needed.

 

[paulmcdonnell]

Still Loosing A Variable Somehow?

gUYS,

mANY THANKS

I'VE CORRECTED ALL THAT YOU RECOMMEDNED.. THANKS


iVE CHECKED THE CODE AND THERE ARN'T ANY ERRORS WHEN IT FIRST RUNS THE ERROR ONLY APPEARS WHEN IT RUNS A SECOND TIME AND THE VARIABLE IS "". i CANT TRACE WHEN THE VARIABLE LOOSES ITS VALUE BUT IT HAS ONE AT THE END OF THE CODE ON THE FIRST INSTANCE AND DOESN'T AT THE START OF THE CODE WHEN IT RUNS AGAIN. nO THER CODE RUNS IN THE MEAN TIME.

COULD IT BE AN ERROR WHICH DOESN'T REPORT.

PAUL

 

[paulmcdonnell]

missing variables

Many Thanks Pat!

Paul