Option Compare Database
Option Explicit
'module to auto-generate PM's into table
'is run by the timer on the switchboard form
'note this means that the first computer to
'run the switchboard after midnight will auto-generate.
'last date PM's were generated
Private DateChecked As Date
Public Sub PMGenerate()
If DateChecked = 0 Then
'set DateChecked to last day a PM was generated
Dim qdLastDate As DAO.QueryDef
Dim rsLastDate As DAO.Recordset
Set qdLastDate = CurrentDb.QueryDefs("PM Last Generate Date")
Set rsLastDate = qdLastDate.OpenRecordset
If rsLastDate.EOF Then 'the very unlikely event that the PM history table is empty
DateChecked = Date - 1 'assume yesterday print date, forces generation on next check
Else 'set date checked...
DateChecked = rsLastDate.Fields("LastDate")
End If
ElseIf Date > DateChecked Then
'a new day that hasn't seen PM's generated- so generate them
Dim strQuery As String
'decide which version to use
If DatePart("w", Date) = 1 Or DatePart("w", Date) = 7 Then
strQuery = "PM Print List Append Weekends"
Else
strQuery = "PM Print List Append"
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery strQuery, acViewNormal, acEdit
DoCmd.SetWarnings True
DateChecked = Date
End If
End Sub