Here’s how to make a reminder automatically pop up with a MessageBox when a date has expired.
Q: Martin writes: “I keep trying to make a reminder that automatically pops up with a msgbox when a date has expired, but no luck…
If cboPartshøringSlut >= Date Then MsgBox “Partshøringen sat til den: ” _
& cboPartshøringStart _
& ” og er udløbet den: ” _
& cboPartshøringSlut & vbCrLf & vbCrLf _
& “Der skal træffes en afgørese i sagen vedr.: ” & vbCrLf _
& vbCrLf & txtAdresse & vbCrLf & vbCrLf _
& “Vedr. partshøring om: ” _
& cboPartshøring & vbCrLf & vbCrLf _
& txtBrevPartshøring & vbCrLf & vbCrLf _
& vbCrLf, vbCritical & vbCrLf
If cboPartshøringSlut < Date Then MsgBox "Partshøringen slutter den: " _
& cboPartshøringSlut & vbCrLf _
& “Der er ” _
& cboPartshøringSlut – Date _
& ” dag(e) tilbage af partshøringen.” _
& vbCrLf, vbCritical
A: I see a number of problem areas in this code:
- The many combo box references (cboPartshøringSlut, etc.) should be references to the value in the combo box, not the control itself. They should also be preceded by the Me! keyword (or frm!, if the procedure is converted into a more generic version taking an Access form argument). Otherwise there can be errors caused by confusion between the control and its value, or the compiler may interpret the control name as an undeclared variable.
- It is a good idea to check that the combo boxes contain valid date values, before trying to work with them.
- To make the procedure more useful, I converted it to a Sub procedure that can be called from different event procedures on the form, with a frm argument (Access.Form object type).
- Finally, I like to use strPrompt and strTitle variables when constructing a complex message box.
Here is my final revised procedure:
Public Sub ShowMessage(frm As Access.Form)
On Error GoTo ErrorHandler
Dim strTitle As String
Dim strPrompt As String
Dim dtePartshøringStart As Date
Dim dtePartshøringSlut As Date
Dim strPartshøring As String
‘Check for valid dates in combo boxes
strTitle = “Invalid date”
If IsDate(frm![cboPartshøringSlut].Value) = False Then
strPrompt = “PartshøringSlut not a valid date; canceling”
MsgBox strPrompt, vbExclamation + vbOKOnly, strTitle
GoTo ErrorHandlerExit
Else
dtePartshøringSlut = frm![cboPartshøringSlut].Value
End If
If IsDate(frm![cboPartshøringStart].Value) = False Then
strPrompt = “PartshøringStart not a valid date; canceling”
MsgBox strPrompt, vbExclamation + vbOKOnly, strTitle
GoTo ErrorHandlerExit
Else
dtePartshøringStart = frm![cboPartshøringStart].Value
End If
strPartshøring = Nz(frm![cboPartshøring].Value)
strTitle = “Date message”
If dtePartshøringSlut >= Date Then
strPrompt = “Partshøringen sat til den: ” & dtePartshøringStart _
& ” og er udløbet den: ” & dtePartshøringSlut & vbCrLf & vbCrLf _
& “Der skal træffes en afgørese i sagen vedr.: ” & vbCrLf _
& vbCrLf & frm![txtAdresse].Value & vbCrLf & vbCrLf _
& “Vedr. partshøring om: ” _
& strPartshøring & vbCrLf & vbCrLf _
& frm![txtBrevPartshøring].Value & vbCrLf & vbCrLf
ElseIf dtePartshøringSlut < Date Then
strPrompt = “Partshøringen slutter den: ” & dtePartshøringSlut & vbCrLf _
& “Der er ” & dtePartshøringSlut – Date _
& ” dag(e) tilbage af partshøringen.”
End If
MsgBox strPrompt, vbInformation + vbOKOnly, strTitle
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
To call this procedure from any event procedure on the form that has these combo boxes (or indeed, any form with the same combo boxes), use this line:
Call ShowMessage(Me)