Today we will make our Appointment Dialog Form very User Friendly. We will be able to Filter and Select the exact Appointments we want. First we will add several checkboxes to be used in filtering data and then we will add a Multi-Select ListBox so we can see and select the exact Appointments we want.


We are naming or are renaming our Form “frmDBApptDialog” for Database Appointment Dialog. We will have another form later named “frmOLApptDialog” for Outlook Appointment Dialog. Here is how our frmDBApptDialog appears so far.

1 ) First we will add 4 CheckBoxes to the Form.


Open your Form in Design view and place the CheckBoxes below the Date TextBoxes. Adding an informative Label and a Rectangle around them gives the Form a nice look and makes it easier for the user.

Enter the Label Captions you see in the image above. Add a name for each of the CheckBoxes in the Properties Sheet under the Tab “Other”. Name them according to their Captions:


Label Caption: “Exclude Holidays”, CheckBox Name: “chkHolidays”


Label Caption: “Exclude Other”, CheckBox Name: “chkOther”


Label Caption: “Exclude Business”, CheckBox Name: “chkBusiness”


Label Caption: “Exclude Personal”, CheckBox Name: “chkPersonal”


2 ) Add an Unbound ListBox to the Form.


It should look something like this:

Our Form after adding CheckBoxes and a ListBox

Name the ListBox “lstAppointments”. Copy the following into the RowSource because we do not want to see anything there when the Form is opened: SELECT tblAppointments.ApptmntID FROM tblAppointments WHERE ApptmntID = 999999999;


Click on the Properties Sheet “Other” tab and in the Multiselect Property select “Extended” from the Drop Down List. This will enable us to select any or all of the Appointments in the ListBox.


3 ) Add the code for each of the CheckBoxes.

Private Sub chkBusiness_AfterUpdate()
    If Len(Me.txtDateSelection & vbNullString) > 0 Then
        OptionsSet
    End If
End Sub

Private Sub chkHolidays_AfterUpdate()
    If Len(Me.txtDateSelection & vbNullString) > 0 Then
        OptionsSet
    End If
End Sub

Private Sub chkOther_AfterUpdate()
    If Len(Me.txtDateSelection & vbNullString) > 0 Then
        OptionsSet
    End If
End Sub

Private Sub chkPersonal_AfterUpdate()
    If Len(Me.txtDateSelection & vbNullString) > 0 Then
        OptionsSet
    End If
End Sub

OptionsSet is a Sub that builds a SQL “Where” Statement that we will use to filter out the dates that we do not want. For example, if you do not want your Personal Appointments in the list they are instatly removed. Each time a CheckBox is clicked the OptionsSet Sub is run and it sets the RowSource for the ListBox and requeries it so the right Appointments appear in the ListBox.


4 ) Add the OptionsSet code.


You can place this sub anywhere in the Form. The first part is the declaration of the SQL string Variables.

Private Sub OptionsSet()
    Dim strSQLlbx As String ' A SQL Statement for the ListBox.
    Dim strWhere As String ' To build final "Where" Statement
    Dim strWherechk As String ' To filter using the CheckBoxes

    ' This is one way you can build a SQL "Where" Statement
    If Me.chkBusiness = True Then
        strWherechk = strWherechk & ",'Business'"
    End If

    If Me.chkHolidays = True Then
        strWherechk = strWherechk & ",'Holiday'"
    End If

    If Me.chkOther = True Then
        strWherechk = strWherechk & ",'Other'"
    End If

    If Me.chkPersonal = True Then
        strWherechk = strWherechk & ",'Personal'"
    End If

    ' If the first part of strWherechk is a comma: ,
    ' We know that strWherechk has a value to use
    If Left(strWherechk, 1) = "," Then

        ' Remove the comma: ,
        strWherechk = Right(strWherechk, Len(strWherechk) - 1)

        ' Add the beginning part of the Where Statement
        strWherechk = "AND Categories Not In (" & strWherechk

        ' Add the end part of the Where Statement
        strWherechk = strWherechk & ")"
    Else
        ' If the , was not added to strWherechk then
        ' No value was there--clear strWherechk
        strWherechk = vbNullString
    End If

    ' Add the dates that were selected to another "Where" Statement
    strWhere = " WHERE ((tblAppointments.ApptDate)" & _
        " >= [Forms]![frmDBApptDialog]![txtStartDate])" & _
        " AND ((tblAppointments.EndDate)" & _
        "<=[Forms]![frmDBApptDialog]![txtEndDate])"

    ' Put together both of the "Where" Statements.
    strWhere = strWhere & strWherechk

    ' Build the entire SQL Statement
    strSQLlbx = "SELECT tblAppointments.ApptmntID," & _
        " tblAppointments.Appt, tblAppointments.ApptDate," & _
        " tblAppointments.EndDate, tblAppointments.Categories" & _
        " FROM tblAppointments" & _
        strWhere & _
        " ORDER BY tblAppointments.ApptDate DESC;"

    ' Set the ListBox's RowSource to show the Appointments
    Me.lstAppointments.RowSource = strSQLlbx

    ' Requery the ListBox to update the data
    Me.lstAppointments.Requery

End Sub

Now we are really getting somewhere! We can filter the Appointments and see the exact Appointments we want to select. Now we can set up some time-saving features.


5 ) Add a Toggle Button to Select all or Deselect all Appointments.


Open the form in Design View and add a Toggle Button centered directly above the ListBox. Name the Toggle Button “tglSelectAll”. On the Button’s AfterUpdate Event add the following code:

Private Sub tglSelectAll_AfterUpdate()
    ' Declare a Variable to use to select Appointments
    Dim i As Integer

    ' If clicked select all of the Appointments
    If Me.tglSelectAll = True Then

        ' Get a range for the Loop
        ' ListCount gives us the total number of Appointments.
        For i = 1 To lstAppointments.ListCount
            lstAppointments.Selected(i) = True
        Next i

        'Change the Toggle Button Caption to Deselect
        Me.tglSelectAll.Caption = "Deselect All"
    Else

        For i = 1 To lstAppointments.ListCount
            lstAppointments.Selected(i) = False
        Next i

        Me.tglSelectAll.Caption = "Select All"
    End If

End Sub

Now we can select the exact Appointments we want in the ListBox. We can select all with one click and then deslect them all.


Our next step is to add code that will process the Appointments. In our next article, we will add the ability to Export the selected Appointments to Outlook or to Delete them from the Database.


To download the code used in this article visit our Free CodeSamples Page.

You can download the completed and free Report Date Dialog Form in the US or UK Version at our Gaining Access website.

More Free Downloads:
Pop-up Calendar
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Here is a preview of the Dialog Form we will complete with our next article:

Our Completed Appointment Dialog Form

Happy computing,
Patrick (Pat) Wood
Gaining Access