Hello,
If you simply wanted to see if the date in your bound textbox has expired, you could do:
Private Sub Form_Current()
If Date > Me.TicklerDate + 7 Then
MsgBox Prompt:="Record has expired.", _
Buttons:=vbExclamation Or vbOKOnly, _
Title:="Reminder"
End If
End Sub
If you have mutiple records in your form's record source, the Current event will evaluate the date field each time you navigate to a new record.
If it is likely that multiple records could be expired at any given moment, you may simply want to flag them all when you open the form, and tuck them away for further processing, like:
' Behind your form module
Private Sub Form_Open(Cancel As Integer)
Call CheckDate
End Sub
' In a standard module
Sub CheckDate()
On Error GoTo ErrorHandler
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT TicklerID, TicklerDate FROM tblTickler"
rst.Open strSQL, cnn, adOpenStatic, adCmdText
rst.MoveFirst
' Loop through multiple records, flagging each expired records
Do Until rst.EOF
If Date > rst!TicklerDate + 7 Then
' Display information about current record
MsgBox Prompt:="Record " & rst!TicklerID & " has expired.", _
Buttons:=vbExclamation Or vbOKOnly, _
Title:="Reminder"
' Store expired record for further processing (recordset, array, collection, etc,)...
End If
rst.MoveNext
Loop
' Clean up
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
ErrorHandler:
' Clean up
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
End If
Set rst = Nothing
If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
End If
Set cnn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
HTH,
Bob
|
