Public Variables in Access Getting Deleted After Query Run

[davidb88]

Hi -

I have a main page form in my database where the user is selecting the month end date for which they are using the database for. I have established this as a public variable "modeldate". Once the user selects the date the variable is stored and I have verified this using the debug.print (modeldate) function. However, once I run any query the public variable "modeldate" is getting removed and in turn it is outputting a value of 12:00 AM. I assume that means that when I am running a query, the value as inputted by the user is being removed. Is there any known way to keep the value of the variable stored, even after a query is run? Maybe I am just missing something simple?

Thanks in advance!

 

[Joe4]

I believe a Public Variable is stored only for the "life" of that VBA cycle code run.
So once your code completes its run, the value would not be retained after that.
Also, are you sure that you want a Public variables and not a Global one (http://www.techonthenet.com/access/standards/variables.php)?

I usually use a one-record table to store my user input variables & criteria.
Makes them easy to access and use, and no worries about them being dropped.

 

[davidb88]

Thanks, Joe. That makes sense that it would only be stored for the life of the code cycle. You mentioned that you use a one record table to store user inputs...How do you then reference that table? Once the user inputs the data I would then need to reference that input to select data from another table where the date matches the user input date. Thanks again!

 

[Joe4]

Typically, I have my Criteria/User Input Form bound to this one record table (I update the Form properties to not allow any record additions or deletions, only edits, and remove all record selectors so all that they can ever do on this form is edit that single record).

So then you can use this table in queries with other tables/queries. Because it only has a single record, you don't even need to join/link the table with the other tables/queries in the new queries. The Cartesian Product of any table/query with a one-record table will always be equal to the number of records in the other table/query.

You could also add a field to that one-record table that never changes (and "ID" field), and use the DLOOKUP function to look up the different criteria fields, if you desire to do it that way instead.

 

[davidb88]

Thanks, Joe. Really basic question though...How do you make the user input box "bound" to the table? Right now I have the textbox which is a drop down calendar where the user selects the date. I also created a table called "tblmodeldate". So how do I link the two so that when the user selects the date from the calendar it updates the table? I've never been able to figure out how to do that.

 

[Joe4]

Here is the easiest way to do it.
Create your table first, adding a field of DateTime datatype and name it something like DateSelection (or whatever you want to call it).
Now, if you go into the Properties of your Form, go to the RecordSource property and select your Table Name.
Now, from the Design menu under Tools, click add Existing Fields, and click and drag the DateSelection field to your Form.
Now you have a bound field (you could likewise go to your existing field on your form and update its Control Source (once the Form is bound).

 

[Norie]

Where did you declare the variable?

 

[davidb88]

I almost got it to work I think. When I now select the date on the main form it is updating the table properly. The only issue now is that I keep getting an error when I open the main form and when I try to close it. The error says: "The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives". Do you know what that means? I double checked the properties source and there isn't any "On Click" events set to the calendar text box so I am not sure what it is referring to. I know that I have a VBA script that requires the user to select a month-end date for the form to be closed. Could that be what is causing this error?

 

[xenou]

With a one-record table for storing values I would also recommend:

2. Create an ID field, Data Type Long Integer
3. Set it's validation property to the value of 1
4. Open the Table and set the value of the field to 1
5. Recommended: Make the ID field your primary key (all tables should have a PK)

This way there is no way that by any chance a second record could be added, which could do harm to queries that assume this table has only one record.



Note:

I know that I have a VBA script that requires the user to select a month-end date for the form to be closed. Could that be what is causing this error?

What is your script and how are you requiring it's use?

 

[davidb88]

Hi Xenou,
This is the code that I had in place when I was still trying to establish "modeldate" as a public variable. I'm not sure if this is what is affecting the bound text box now:

Option Compare Database
Public modeldate As Date
Public rateDate As Date


Sub UpdateModelDate()
Dim subseqDate As Date




'set modelDate equal to TextBox
modeldate = Form_MainPage.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


Sub UpdateRateDate()
Dim subseqDate As Date


'set rateDate equal to TextBox
rateDate = Form_MainPage.textRateDate.Value


'check for month end date selected, if not then set rateDate to 0
subseqDate = DateAdd("d", 1, rateDate)
If Month(subseqDate) = Month(rateDate) Then
    msgbox "Please choose a month-end date for the PD-LGD Rate."
    rateDate = 0
End If


End Sub