Help remembering variable outside If statement

[fredalina]

i have a module that runs daily, and, only on the second business day of the month, sets up a spreadsheet with a column for each working day in the month and saves that spreadsheet in the format

"Template" & Month(date) & Year(Date) & ".xls" (i.e. Template1208.xls).

Because the setup code only runs on the second business day of the month, it is nested within an If statement that determines whether that day is, in fact, the second business day of the month.

The code following the If statement looks up values from a query and fills in the correct data for each customer in the previous business day's column and saves and sends the spreadsheet, so that the data is cumulative. The users want the data stored within the spreadsheet so that it is accessible to them, so that an append query is not useful.

i need to be able to open the correct spreadsheet (i.e. Template1208.xls) and set the cell values each day. How can i store the name of the spreadsheet so that it can be seen on subsequent days? i can't simply use the Month(date) & Year(Date) code each day because sometimes months are "extended" beyond the last day of the actual month (for example, the working month of December this month is being extended through Jan 2, so that on Jan 5, the first business day of the month, i will be filling in information for Jan 2 as if Jan 2 falls within December). If i tried to use the Month(Date) & Year(Date) code, it would look for Template0109.xls unsuccessfully on Jan 5, or at best entering it in the wrong spreadsheet.

i hope this makes sense! i've tried creating a public variable and setting it in the monthly If statement, and i've tried creating an unbound object in the form with the command button that runs the code, but it doesn't remember the value the next time the form is opened. The form is already bound to a different table, so i can't bind that field very effectively.

Thanks!

 

[dkinley]

Aside from the issues of using variables and such - that information is gone as soon as the application is shut down - so you will need to actually write it somewhere.

In situations such as these, I have a tblUtilities that I use to store information. You could write it to a specific record so that you can recall, analyze, and write-over as needed.

-dK

 

[fredalina]

Thanks. How do i write the value to a specific record of a table?

 

[dkinley]

What I do is usually have two fields in the table. One holds some terminology - for example one field might have DataForFormA and the second field would record what I wanted saved for it.

Since the first field is unique for my information, I might do a DLookup to retrieve the information and an SLQ Update to write over that field since I can specify the correct record on the first field.

Here is a link that demonstrates the writes in SQL, DAO ....

http://www.functionx.com/vbaccess/

An example might be ...

DoCmd.RunSQL "UPDATE tblUtilities " _
& "SET tblUtilities.StoredData = " & Me.txtInfoToStore & _
" WHERE ((tblUtilities.Identifier) = 'DataForFormA' );"

Hope that helps.

-dK

 

[fredalina]

Thank you! Works just fine.

 

[dkinley]

Awesome! I am glad that could resolve your dilemma.

-dK