Referencing a Public Variable in multiple modules?

[davidb88]

Hi - Is it possible to reference an established public variable in another module other than the one it was created in? In my database I have a main form page where the user selects a date from the calendar. The way that I have this set up is:

Public modeldate As Date
'Model Date Select
Sub textModelDate_AfterUpdate()
Dim subseqDate As Date


'set modelDate equal to TextBox
modeldate = Me.textModelDate.Value


'check for month end date selected, if not then set modelDate to 0
subseqDate = DateAdd("d", 1, modeldate)
If Month(subseqDate) = Month(modeldate) Then
    MsgBox "Please choose a month-end date for the Model Run Date."
    modeldate = 0
End If


End Sub

I would like to then be able to reference this variable "modeldate" in other modules but as it stands right now anytime I reference it, nothing appears which makes me think that I am doing something wrong with establishing the variable. Is there an easier way to do this or am I just missing something in the code I have right now?

Thanks!

 

[xenou]

You should declare the public variable in a standard module, not in a class module (a form).

I would however, prefer to just reference the textbox itself all things being equal, not set a public variable.
myDate = [Forms]![myForm]![textModelDate]

 

[davidb88]

Hi Xenou,

How do I declare a public variable in a standard module if the user is inputting the date on the main form? Is there a workaround for that?

Can you explain the second option a little more? I don't have to establish a public variable?

Thanks!

 

[xenou]

Hi,
It depends a little on what you are doing, but you can reference a form textbox value almost anywhere you need to, using the syntax above (it works in both queries, reports, other forms, as well as in VBA. Where else do you need to use the value?

As far as declaring a public variable, you just have to add a standard module then put the Public modeldate As Date in it. You form will be able to access it that way (I don't think it works as reliably the other way around - standard modules are the right place for your public variables, custom functions, and so on.

 

[davidb88]

The model date variable will need to be used in about 5 other modules. Would it work to establish the model date as a public variable on one of those modules first referencing it using this syntax that you referenced above: myDate = [Forms]![myForm]![textModelDate] and then I could just use the variable modeldate in the other modules?

Also, not sure if I mentioned this but the textbox that is on the main page is a date selection (using the calendar drop down). Does that have any affect or does it function the same was as a textbox in terms of referencing it?

Thanks for your help!

 

[Norie]

Why do you want a publc variable when you can access the value in the textbox from any other module using the syntax that xenou has suggested.?

 

[xenou]

Yes, basically you can just put this wherever you need the date:
=[Forms]![myForm]![textModelDate]

So you see, no need for a public variable. It's also useful for self-documentation. 8 months from now when you (might) go back to work on enhancing this form, you won't be wondering how that public variable gets set.

 

[revver]

If you REALLY need a global variable, here's what I do.

Create a New module. I call mine llbGlobals. (llb = my initials so I know who wrote it.)

Option Compare Database
Public gdteModelDate As Date
-----------------------------
Public Sub SetGlobals()
    gdteModelDate = Today()
End Sub

In the Load event of the Main form I call SetGlobals if I need to initialize the variable's value.

So now I have established the variable (and maybe initialized it) and I can manipulate its value anywhere in VBA. For example, in the AfterUpdate event for txtModelDate on the Main form I can set the gdteModelDate to the value the user just entered.

Now I can use this value using VBA in ANY module, even if Main Form is not open at the time.

 

[davidb88]

Thank you everyone! Using the =[Forms]![myForm]![textModelDate] formula works well for what I need to do. One follow up question though. Is it possible to then save the value of what is entered in the textbox for when the form closes and then reopens? As of right now, when I close the main form the value that was entered by the user is lost. Is there a simple VBA process to keep the value of [txtModelDate] in the form until I select a "Refresh" button or something like that?

 

[revver]

The easiest way would be to hide the form rather than close it. (visible = false)

You could always use a global variable and use the form's close event to store it, loading it again in the open event or by clicking a Refresh button.