Solved Run only once on a Monday module (1 Viewer)

[Number11]

Okay a number of things - a) that code is running the same query twice. b) The currentdb.execute line is missing.

Try this code complete; and we'll see what is happening

 Dim dLastRun As Date
    Dim dCurrRun As Date
    Dim sSql As String
  
    dLastRun = Nz(DLookup("propvalue", "zzz_tblSystem", "Property = 'LastRunDate'"), #1/1/2020#) ' This will make sure it runs the first time around
  
    Debug.Print dLastRun
    Debug.Print Now() - dLastRun
  
    If Now() - dLastRun >= 7 Then ' We need to run as it's been over 7 days
'        'DoCmd.SetWarnings False
         DoCmd.OpenQuery "Unallocate_1", acViewNormal, acEdit
         DoCmd.OpenQuery "Unallocate_2", acViewNormal, acEdit
'        'DoCmd.SetWarnings True

        ' This will now update the last run date to the next monday at 9:30. This will prevent time creep
        If Weekday(Date) = 2 Then   ' Its Monday
            dCurrRun = Date + (9.5 / 24)
        Else
            dCurrRun = (Date - Weekday(Date - 3) + (9.5 / 24))
        End If
        sSql = "UPDATE zzz_tblSystem SET LastRunDate = #" & Format(dCurrRun, "yyyy/mm/dd hh:nn") & "#"
    
        CurrentDb.Execute sSql         ' This will run the sql created above.
  
    End If

    msgbox "The last run time was " & dLastRun & " , The new last run time is now set to " &  dCurrRun

RUNTIME ERROR 2471 see pic

i changed the first line to this:
dLastRun = Nz(DLookup("LastRunDate", "zzz_tblSystem"), #1/1/2020#) ' This will make sure it runs the first time around
and the code runs completly and if updates the date in the table, however the update query come back with 0 records but when run manually i have 45

 

[Minty]

Okay and what line does it highlight when you press debug ?

 

[Number11]

Okay and what line does it highlight when you press debug ?

all working now it was an issue with my update query thanks so much you are amazing

 

[Minty]

Excellent - Good luck with the rest of your project!

 

[Number11]

Excellent - Good luck with the rest of your project!

Thanks - so just one last Question what does this do...
' This will now update the last run date to the next monday at 9:30. This will prevent time creep
If Weekday(Date) = 2 Then ' Its Monday
dCurrRun = Date + (9.5 / 24)

and if i need to change the code to run on a say a Wednesday i just change the code from 2 to 4?

 

[Minty]

Nearly - you would also need to change this line,( the -3) to get it to the correct day

dCurrRun = (Date - Weekday(Date - 3) + (9.5 / 24))

 

[Number11]

ok so this is running fine jut its causing runtime error 3734 The database has been placed in a state by user "Admin" ....

I can calling the Module code via Autoexec Macro. any better way to run this code?

 

[arnelgp]

is this on Timer event? when you "Enter" the timer event, disable the timer:

Me.TimerInterval = 0

so your timer will not be triggered, then perform what is needed.

 

[Number11]

is this on Timer event? when you "Enter" the timer event, disable the timer:

Me.TimerInterval = 0

so your timer will not be triggered, then perform what is needed.

Ok, so i do have a timer event on my main menu that closes the database after 10 mins if no usage, so i need to add some code to turn that off in autoexec macro before running the Module code ?

So would it be best to move this code to the same Main form and run this before the timer ect