Access Macro

[CorrieAnn]

Is there a way to ensure that a macro within my database is only run once per calendar day?

 

[ThomasLafferty]

Sure! Establish a global module, and call it as the first line of your macro. Use it to write a date to a log table.

Here's the basic idea...

In the first few lines of code, do something like this:

If IsError(DLookup(Date, "tblMacroLog")) = True Then
     'Routine to write date to table
Else
     'Code to halt macro here
End if
[code]


If you need help with code, let me know.

Hope this helps.

Tom

[COLOR=green]Born once die twice; born twice die once.[/color]

 

[CorrieAnn]

Would it be too much to ask for some code? This isn't something I would be able to do on my own...

 

[ThomasLafferty]

Hi CorrieAnn!
Sorry about the delay in getting back to you - it's been a struggle figuring this out, but here's how to do what you need:
1. Open the macro you want to prevent from running more than once a day in [!]Design View[/!]
2. Right click on the first row of the macro Action column
3. Insert a new row
4. Select RunCode from the dropdown.
5. In the Function Name box at the bottom left of your screen, type [!]MacroLog()[/!]
6. Save and close your Macro.
7. Create a new Macro.
8. Add the following action: [!]StopMacro[/!]
9. Save this macro as [!]HaltDailyMacro[/!]
10. Create a table with the following fields:

LogID..........AutoNumber (PrimaryKey)
RunDate........Date

11. Save this table as [!]tblMacroLog[/!] and close it.
12. In the object pane of your database window, click Modules.
13. Click [!]New[/!]
14. Paste the following code:

[COLOR=blue]Public Function[/color] MacroLog()
[COLOR=green]'*****VARIABLE DECLARATION*****[/color]
[COLOR=blue]Dim[/color] strSQL [COLOR=blue]As[/color] String, RecCount [COLOR=blue]As[/color] Long

RecCount = DCount("[RunDate]", "tblMacroLog", "[RunDate] = Date()")
[COLOR=blue]If[/color] RecCount >= 1 [COLOR=blue]Then[/color]
[COLOR=green]'Today's date found in RunDate Field - halt macro[/color]
    DoCmd.RunMacro "HaltDailyMacro"
[COLOR=blue]Else[/color]
[COLOR=green]'date not found - add to table[/color]
    strSQL = "INSERT INTO tblMacroLog ( RunDate )" _
    & "VALUES (DATE());"
    DoCmd.RunSQL strSQL
[COLOR=blue]End If
End Function[/color]

Hope this helps.

Tom

Born once die twice; born twice die once.