Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Denver, CO
    Thanked 0 Times in 0 Posts

    Saving dynamic information to a table (Access 2002 sp1)

    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.

    <font color=blue> Dim dtLastDateRun As Date
    Dim dtPreviousDateRun As Date

    <font color=448800>'get the current 'LastRunDate' </font color=448800>
    dtPreviousDateRun = DLookup("LastDateRun", "tblWeeklyLabelsLastDate")

    <font color=448800>'move 'LastDateRun' value to 'PreviousDateRun' in the table</font color=448800>
    PreviousDateRun = dtPreviousDateRun

    <font color=448800>'move yesterdays date to 'LastDateRun' </font color=448800>
    dtLastDateRun = Date - 1
    LastDateRun = dtLastDateRun <font color=blue>

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Saving dynamic information to a table (Access 2002 sp1)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts