Event-driven messaging

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I'm using the following code to call a SendMail macro if the EntryDate of a
quote in our Quote Log has a variance of over 10 days from the current date.
I tested the logic using the On Activate event of the Quote Log Update form.

Private Sub Form_Activate()

Dim stDocName As String
stDocName = "QuoteSend"
If DateDiff("d", [EntryDate], Date) > 10 Then
DoCmd.RunMacro stDocName
End If

End Sub

I have been using the form event and macro only to test the logic. I would
like to be able to call this code independent of a form event. Basically, I
would like to use AutoExec to open the database once a day, scan the table
for the variance, and use CDONTS to send the e-mail (something I still have
to work on). I would like a bit of help with the first part (calling the
code independent of the form event). Any advice you can offer her would be
appreciated. Please let me know if my explanation has not been adequate.

Thanks in advance,

Nick
 
I use a Public Function called StartUp() in all of my
databases, called by a macro names AutoExec using the
RunCode action.

What I would do is have the StartUp() code pull a
recordset - from the table, where the records meet your
criteria. If there are records in the recordset, prompt
the user if they want to run the macro.

' declare variables
Dim dbMe As Database
Dim strSQL As String
Dim rs As Recordset
Dim i As Integer

' set database and get recordset
Set dbMe = CurrentDb
StrSQL = "SELECT tblTable.Fields FROM tblTable WHERE
CriteriaIsMet"

Set rs = dbMe.OpenRecordset(strSQL)

rs.MoveLast
i = rs.RecordCount

' if no records exist, end the function
If i = 0 Then
Exit Function
End If

' otherwise, ask user whether to run macro
If MsgBox("Run the macro now?")=vbYes Then
DoCmd.RunMacro "MacroName"
Else
Exit Function
End If

Hope this helps!

Howard Brody
 
Back
Top