Trying to prevent multiple instances from occuring

[Opal]

I have an Access 2003 database from which I have automated the
emailing
of a report each Friday the first time the database is opened.

I run the following from the on Timer event when the DB is opened:

If Me.cboDayofWeek = 6 Then ' test to send Friday (weekly)
Management Report
TestSend
End If

Then

Sub TestSend()

If 0 = DCount("*", "qryAudit") Then
SendManagementrpt
End If

End Sub

and the following is the SQL from "qryAudit"

SELECT Audit.AuditDate, Audit.AuditDay, Audit.AuditTime
FROM Audit
WHERE (((Audit.AuditDate)=Date()) AND ((Audit.AuditDay)=6) AND
((Audit.AuditTime)>#12/30/1899 6:0:0# And
(Audit.AuditTime)<#12/30/1899 11:0:0#));

Everything seems to work fine, except, anytime the DB is opened after
midnight
on Friday morning it sends the report. So if its opened 20 times
between midnight
and 2 am on Friday morning, it will send the report 20 times. I
thought by putting
in the time constraints that it would prevent that, but it doesn't. I
can't quite figure
out how to correct this. Can anyone offer any assistance / advice?

 

[Dennis]

Create a Macro that runs a Public Function. Create an Access Command Line
that invokes the Macro. Add that Command Line to the Windows Scheduler.

All done...

 

[Klatuu]

You are kind of on the right track, but I wouldn't use the Timer event of the
form. I would use a macro named AutoExec with the RunCode action that would
run a function in a standard module. That code would then do the check,
email the report, if necessary, then open the form:

Public Function CheckForFriday()
Dim strTimeRange As String

If WeekDay(Date) = 6 Then
strTimeRange = "AuditTime BETWEEN #" & Date & " 12:00:00 AM# AND #"
& Now & "#"
If DCount("*", "Audit", strTimeRange ) = 0 Then
SendManagementrpt
End If
End If

Docmd.OpenForm "StartUpForm"
End Function

 

[Opal]

You are kind of on the right track, but I wouldn't use the Timer event ofthe
form.  I would use a macro named AutoExec with the RunCode action that would
run a function in a standard module.  That code would then do the check,
email the report, if necessary, then open the form:

Public Function CheckForFriday()
Dim strTimeRange As String

    If WeekDay(Date) = 6 Then
        strTimeRange = "AuditTime BETWEEN #" & Date & " 12:00:00 AM# AND #"
& Now & "#"
        If DCount("*", "Audit", strTimeRange )  = 0 Then
            SendManagementrpt
        End If
    End If

    Docmd.OpenForm "StartUpForm"
End Function

--
Dave Hargis, Microsoft Access MVP












- Show quoted text -

Thank you Dave. Sorry for the delayed response, first chance I had to
check it out
was this afternoon. It appears to test out okay.