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

[Number11]

Hi all,

So i need to have this module run only on a monday and only once! , so here my code is called from the main database menu, but it gets called everytime to the database is closed and re-opened

Public Function AutoQuery()
If VBA.Weekday(Now(), vbMonday) = 1 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "Unallocate_1", acViewNormal, acEdit
DoCmd.OpenQuery "Unallocate_2", acViewNormal, acEdit
DoCmd.SetWarnings True
End If
End Function

how would i go about only having the database call it say at 10 am ?

 

[Minty]

You really need to have a flag value (probably a "LastRunDate") set in a table somewhere, that you update that says when the process was last run and you check that before running it again.

 

[theDBguy]

Hi. You could add a table to log when the module last ran. If it hasn't run yet (on a Monday at 10am), then let it run. Otherwise, don't let it run.

Edit: Oops, too slow...

 

[Number11]

You really need to have a flag value (probably a "LastRunDate") set in a table somewhere, that you update that says when the process was last run and you check that before running it again.

loving it yes so how would the code look at the table for the last run date?

 

[Number11]

Hi. You could add a table to log when the module last ran. If it hasn't run yet (on a Monday at 10am), then let it run. Otherwise, don't let it run.

Edit: Oops, too slow...

loving it yes so how would the code look at the table for the last run date?

 

[vba_php]

loving it yes so how would the code look at the table for the last run date?

if dlookup("field", "table") > date_in_question then
   'run code here
else
   exit function
end if

you can also use all kinds of date functions to do this stuff. the table can also have a list of mondays in it, in the form of full dates, and you can use the above code to check the latest date in the table

 

[Minty]

Lets say your table is called tblDBSystemVars and the field is called LastRunDate, and you always want the process to run on a Monday after 9:30AM?

' I personally wouldn't check for a Monday in case you've had a power cut or similar

  Dim dLastRun  As Date
    Dim sSql As String
    dLastRun = Nz(DLookup("LastRunDate", "tblDBSystemVars"), #1/1/2020#) ' This will make sure it runs the first time around
    
    If Now() - dLastRun >= 7 Then ' We need to run as it's been over 7 days
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Unallocate_BAU", acViewNormal, acEdit
        DoCmd.OpenQuery "Unallocate_Rollout", acViewNormal, acEdit
        DoCmd.SetWarnings True
        
        ' This will now update the last run date to when this was run.
        sSql = "UPDATE tblDBSystemVars SET LastRunDate = Now()"
        CurrentDb.Execute sSql
        
    End If

 

[Micron]

What if nobody opens the db on a Monday? A lot of people use Task Scheduler for this sort of thing. AFAIK, it can run even when you're not logged on to the pc.

 

[Number11]

What if nobody opens the db on a Monday? A lot of people use Task Scheduler for this sort of thing. AFAIK, it can run even when you're not logged on to the pc.

good point so if i use the above code it looks for 7 days and will run so that covers this point

 

[Number11]

Lets say your table is called tblDBSystemVars and the field is called LastRunDate, and you always want the process to run on a Monday after 9:30AM?

' I personally wouldn't check for a Monday in case you've had a power cut or similar

  Dim dLastRun  As Date
    Dim sSql As String
    dLastRun = Nz(DLookup("LastRunDate", "tblDBSystemVars"), #1/1/2020#) ' This will make sure it runs the first time around
   
    If Now() - dLastRun >= 7 Then ' We need to run as it's been over 7 days
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Unallocate_BAU", acViewNormal, acEdit
        DoCmd.OpenQuery "Unallocate_Rollout", acViewNormal, acEdit
        DoCmd.SetWarnings True
       
        ' This will now update the last run date to when this was run.
        sSql = "UPDATE tblDBSystemVars SET LastRunDate = Now()"
        CurrentDb.Execute sSql
       
    End If

so this is updating the table with the date fine, but its not running the query to update?

 

[Minty]

That doesn't make much sense, if it does the last bit it must be doing the first bit?
Are there criteria in your queries that would prevent the updates happening more than once?
Can you post the actual code you are using?

Failing that can you post a stripped-down version of your database with enough "stuff" left in it to demonstrate the issue?

 

[Number11]

That doesn't make much sense, if it does the last bit it must be doing the first bit?
Are there criteria in your queries that would prevent the updates happening more than once?
Can you post the actual code you are using?

Failing that can you post a stripped-down version of your database with enough "stuff" left in it to demonstrate the issue?

sorry i was mistaken, all of it doesn't work, so i have built the table with the field name left it blank. when i run the code i get the normal warning/confirmation (all zero!) when you run an update query (as popped ' before the code to ignore) but nothing updates at all and no date is entered into the table..

Public Function AutoQuery()

Dim dLastRun As Date
Dim sSql As String
dLastRun = Nz(DLookup("LastRunDate", "tblDBSystemVars"), #1/1/2020#) ' This will make sure it runs the first time around

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 when this was run.
sSql = "UPDATE tblDBSystemVars SET LastRunDate = Now()"

End If
End Function


butif i run these update queries manually they do prompt to change 21 records (which is what i would have (expected)

 

[Minty]

Okay time for some debugging - my logic is probably disfucntional; try adding these lines so you can see what is happening;

 Dim dLastRun 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 when this was run.
        sSql = "UPDATE zzz_tblSystem SET LastRunDate = Now()"
       CurrentDb.Execute sSql

I would simply call this a private sub, rather than a function, a function normally takes some parameters and returns a value.

 

[Number11]

if dlookup("field", "table") > date_in_question then
   'run code here
else
   exit function
end if

you can also use all kinds of date functions to do this stuff. the table can also have a list of mondays in it, in the form of full dates, and you can use the above code to check the latest date in the table

dont seem to work either,

 

[Gasman]

Well you are settig the sql to update the table, but not doing anything with it?

 

[Minty]

dont seem to work either,

Please try what I suggested - It's pointless building a table of Monday's when you could always calculate when that last was.
After you have run it open the VBA editor and make sure you can see the immediate window (Press ctrl + G to open it), when I do it (with yesterday in the table) I get

21/04/2020 09:30:00
1.01015046296379

Which indicates the code is working correctly

 

[Number11]

Please try what I suggested - It's pointless building a table of Monday's when you could always calculate when that last was.
After you have run it open the VBA edition and make sure you can see the immediate window (Press ctrl + G to open it), when I do it (with yesterday in the table) I get

21/04/2020 09:30:00
1.01015046296379

Which indicates the code is working correctly

the new code doesnt work get runtime error 2471 can find propvalue

so need to find another way to do this

 

[Minty]

Okay, so the problem is in your Dlookup then.
Please show us the code as you have it now, and also can you post a picture of the table you have stored the last run date in?

Th Dlookup syntax is available from my signature - but we can help with that if you are stuck.
I also thought of an improvement to the code to stop "time creep" but we'll come back to that later once your initial problem is solved.

 

[Number11]

Okay, so the problem is in your Dlookup then.
Please show us the code as you have it now, and also can you post a picture of the table you have stored the last run date in?

Th Dlookup syntax is available from my signature - but we can help with that if you are stuck.
I also thought of an improvement to the code to stop "time creep" but we'll come back to that later once your initial problem is solved.

oh ok

code:

Dim dLastRun As Date
Dim sSql As String


dLastRun = Nz(DLookup("LastRunDate", "tblDBSystemVars"), #1/1/2020#)


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_1", acViewNormal, acEdit
' 'DoCmd.SetWarnings True
' This will now update the last run date to when this was run.
sSql = "UPDATE tbleDBSystemVars SET LastRunDate = Now()"


End If
End Sub

Table is as attached

the code runs get the warning messages but 0 records will be changed - if i run if manually i get 43 records (as this is currently what is unworked)

 

[Minty]

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