how do I repeat an action every day

M

mike

I export information via email every day to various customers (when I
remember!) I use a macro to send the results of a query output as an excel
spreadsheet and it works fine but I forget to do it I need to automate the
macro to run say every 24 hours and then to OK the "do you want to send"
question I get
 
R

Ron Hinds

I've solved a similar problem by using the Windows Task Scheduler to start
my Access app with a command-line switch (/cmd auto). Then, in the Open
event of my main form, I check for the "auto" switch like so:

If Command = "auto" Then
....
End If

At this point I set a global variable of type Boolean to True and then run
the code. In the code to be run, I check the global each time I might be
showing a user prompt, etc. and skip the prompt if it is True. After the
code is run, I exit the Access application. It works like a champ so far (>
6 months) at nearly 500 installations in the field!
 
M

mike

Thanks for your help, I don't think I'm clever enough to understand it
perhaps its going to be too complicated for me
Mike
 
M

mike

Its mike again, I think you mean I start access via task scheduler at the
time I want to send the email and then find out if access was started
automatically if so send the email and exit the programme - brilliant I'll
try to work out the details but if I have no joy I'll post a help me message
Thanks
 
R

Ron Hinds

Hi Mike - yes, that's exactly what I mean. In the Task Scheduler command add
"/cmd auto" after the database name. So your command string in Task
Scheduler might look like:

"C:\Program Files\Microsoft Office\Office\msaccess.exe"
"path_and_filename_of_your_mdb_or_mde" /cmd auto

Next, in the Open event of your main form, check for the "auto" command line
switch like so:

If Command = "auto" Then
'Set a global Boolean variable
gboolAutoRun = True
'Now run your macro
DoCmd.RunMacro "your_macro_name"
'Now quit Access
Application.Quit acQuitSaveNone
Else
gboolAutoRun = False
End If

Be sure to modify your macro so that it skips any dialog boxes or any other
code that might require user intervention if gboolAutoRun is True, since no
one will be there to clik "OK" or whatever! For instance, I cange all of my
error handlers to write to a log file instead of displaying a message box.
Here is the LogError routine:

Public Sub LogError(ModName As String, Number As Long, Desc As String,
Optional FileName As String)
'Log error number, description, and module/routine that generated the error
'to a text file, optionally with filename that caused error
On Error Resume Next 'Can't popup a MsgBox because this is
auto-execute

Dim strDateTime As String
Dim strLogDir As String
Dim strLogFile As String
Dim iFileNo As Integer

'Create the log directory if it doesn't exist
strLogDir = "C:\Logs"
If Dir(strLogDir, vbDirectory) = "" Then MkDir strLogDir

'Create new log filename every day
strLogFile = strLogDir & "\Log" & Format(Now(), "mmddyy") & ".txt"
strDateTime = Format(Now(), "mm-dd-yy hh:mm:ss")
iFileNo = FreeFile

Open strLogFile For Append Access Write Lock Write As #iFileNo

If Len(FileName) > 0 Then
Print #iFileNo, strDateTime; Spc(4); ModName; Spc(4); "Error " &
Number; _
Spc(4); Desc; Spc(4); FileName
Else
Print #iFileNo, strDateTime; Spc(4); ModName; Spc(4); "Error " &
Number; _
Spc(4); Desc
End If

Close #iFileNo

End Sub
 
M

mike

High Ron
Thanks very much that's brilliant and very nicely explained I really
appreciate your help
Mike
 
Top