Skip to content

MessageBox Reminder

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)

 

About this author