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!

 

[xenou]

Well, I would delete the public variables if you aren't using them, and clean up the code based on the new setup. The error message you get is very strange - I have never seen it before. If the form is not a complex one you might want to just rebuild it.

 

[davidb88]

Great! Thank you, everyone. I think I have gotten it to the point where the date selected from the user input form is now feeding into the table without the error message.

Xenou - One follow up question. You wrote the following in a post:

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

1. Create an ID field, Data Type Long Integer
2. Set it's validation property to the value of 1
3. Open the Table and set the value of the field to 1
4. 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.

Does this mean that there would be 2 fields in the table, "ID" and "modeldate"? And how do you set the validation property of the ID field?

Thanks for all your help!

 

[xenou]

In table design view you view the field properties for your ID field - it should be at the bottom of the screen under the field names. One of this is validation rule. Just put a 1 in that box. You can also put =1 in the box (both work).

So yes, two fields, one called ID and one called modeldate.

 

[Joe4]

If you have other criteria entries you would like to store, you can add more fields to this one-record table.