Retaining Form Value

[NikToo]

Ok, another problem. I have a form with three values in it. I want those values to remain when I reload the database. Is there a way to do that? The reason is that I want to call the procedure from Excel and not have to edit the form every time.

 

[Joe4]

Is this a bound or unbound form?
Are these never changing default-type values?

Assuming these are unbound Text Boxes, if the values are never changing, you can use the Form_Load event to populate those text boxes with your default value when the form first opens.

If they can change, so you need a way to remember them, store them in a table, and make the form bound to the table, and the text boxes bound to fields in the table.
I often do this for things like default file paths that I want to allow my users the ability to change. Basically, I have a one-record table that store these variables that my Form is bound to.
I set my form to not allow additions or deletions, to ensure that it is always exactly a one-record table.

 

[NikToo]

Thanks. I've tried that,, but I get #Error values. If I look at the table though, I have one row with the numbers I want, and one row with 0, 0, 0 in it, can I make it a one-row table?

Edit: Tried setting the value to =[tbl_Years]![CurrYear] for example, but I get #name errors instead now.

 

[Joe4]

I've tried that,, but I get #Error values.

You have tried which option I discussed?
And how did you try to apply it? Please post any VBA code that you tried to use.

If I look at the table though, I have one row with the numbers I want, and one row with 0, 0, 0 in it, can I make it a one-row table?

If you use the Table route, you will probably want to use a one row table. Through the Form Properties, you can control it to not allow any Row Additions or Deletions, which will keep it a one row table (and users should NEVER access tables directly - all their access should be via Forms).

What exactly is this form being used for?
If there are other fields on it that need to be entered and stored to a Table, we will probably not want our Form to be bound to this one row table, but rather the other table for data entry. We might want to use a DLOOKUP for these other values.

If you are unsure, please explain the whole thing in more detail, what you are trying to use this for and what else is on your Form.

 

[NikToo]

Basically, I need to have three parameters, Current Year, Previous Year and Next Year. I thought the easiest way to reference something like this would be through a Form. Then I can do a formula saying PY_P01: Sum(IIf([Year]=[Forms]![Years]![PreviousYear],[P01],0)). Years are always going to be changing and since I'm going to have three rolling years I didn't want to have 36 query columns to edit.

My first attempt was to do a table with the values and link that, but it didn't like it. So I tried the form which I thought would be an easier way to keep it updated as well.

 

[Joe4]

Are Current Year, Previous Year, and Next Year always based on the current date?
Meaning, right now Current Year should be 2017, Previous Year is 2016, and is Next Year is 2018, and that should change on January 1st of each year?
If so, there is no reason to have to store/set these values, they can be derived dynamically off of the current date, i.e.

Current Year: Year(Date())
Previous Year: Year(Date()) - 1
Next Year: Year(Date()) + 1

 

[NikToo]

No, it's Fiscal Year, running from April 1st...

 

[Joe4]

So, it should change April 1 of each year?

Still can do it dynamically, using the DateAdd function, i.e.
For Current Year:

Year(DateAdd("m",-3,Date()))

Then just subtract/add 1 for Previous/Next year.