Saving dynamic information to a table (Access 2002 sp1)

Topic

New Reply

Trying to write a procedure that will print a report that contains only the data that is new since the last time the report was printed. I created a single-record, 2-field table to hold two dates: the date the report was last printed and the previous date is was printed. I use that information as criteria in my query. Straight forward so far…

My procedure is written so that it updates the two dates in the table to their appropriate values, then fires the report. When I “watch” these fields and their corresponding variables from within the VBA editor, I see that they update correctly. However, when the report runs, the old dates are used. When I open the table, sometimes the new dates are there, sometimes the old dates are there. It’s the strangest thing.

Here is the code I’m using to update the fields, etc.

Dim dtLastDateRun As Date
Dim dtPreviousDateRun As Date

‘get the current ‘LastRunDate’
dtPreviousDateRun = DLookup(“LastDateRun”, “tblWeeklyLabelsLastDate”)

‘move ‘LastDateRun’ value to ‘PreviousDateRun’ in the table
PreviousDateRun = dtPreviousDateRun

‘move yesterdays date to ‘LastDateRun’
dtLastDateRun = Date – 1
LastDateRun = dtLastDateRun

There’s probably an easier way to do this. Thanks for any help.

Reply | Quote

Replies

I assume that this code is run in a form bound to tblWeeklyLabelsLastDate – otherwise I don’t see how

PreviousDateRun = dtPreviousDateRun

would ever update the record. If my assumption is correct, you don’t need a DLookup – you can get the value from the form:

PreviousDateRun = LastDateRun
LastDateRun = Date – 1

If you then open the report, the record displayed in the form is not necessarily written back to the table yet, so the report may use the old values (which it gets from the table, I suppose). To prevent this, save the record explicitly before opening the report:

RunCommand acCmdSaveRecord

Reply | Quote