In our last Article we showed you how to export custom data from a Form to an Outlook Appointment in the Outlook Calendar. But what if we want to save time and export several appointments to the Outlook Calendar? If we just used a Recordset and exported them all, there would be duplicate Appointments. We may not need to send last Month’s Appointments to Outlook. It would be very nice to be able to select the Appointments we want to put in Outlook. But how can we do that?

We will show you how you can create a Custom Dialog Form to select exactly the Appointments you want to put in your Outlook Calendar. When the series is finished you will have 3 different methods to drill down to just the data you want.

If you have never created a dialog form, you are reading the right article. We will take it step by step so you can do this again when you need a dialog form to handle other data. First, we will show you how to make it easy for the user to select Appointments by selecting a single date or a range of dates. Second, we will show you how to filter a specific field so you do not see categories of Appointments you do not want. Then we will show you how to use a Multi-select ListBox to pick each Appointment you want. We will show you how to make a Dialog Form to filter your data by dates. This is something that you can use many times as you use and develop Access Databases.

First, create a new Form that is Unbound—it will not have a Record Source. On that Form place an unbound textbox at least a couple of inches long and leave about an inch above it. Move the label and place in on top (but not covering) of the textbox. Change the label to read: “Dates Selected”. We want to center it so it is in the middle of the textbox. An easy way to do this is to stretch the label until it is the same length as the textbox. Then click on the center align button and you have something like this:

Next, add an unbound combo box, and put “Select Dates:” in the label. Name the combo box “ cboDateRanges“. In the combo box’s property sheet, click the Data tab, change the Row Source Type to Value List and paste the following into the Row Source line:

All;Today;Yesterday;Last Sunday;This Month;This Quarter;This Year;Last Month;Last Quarter;Last Year;Month to Date;Quarter to Date;Year to Date;Last 12 Months;January;February;March;April;May;June;July;August;September;October;November;December

Then set the Limit To List Property to “Yes”. Later we will add some code to this combobox’s AfterUpdate Event that will get all of these date selections for us.

Now add three unbound textboxes and change their labels to match this image:

Leave a little extra space above and below the Single Date textbox.

Next we need to add 3 Calendar buttons to our form which open the Pop-up Calendar when clicked. We can use the little Calendar images by themselves or add the image to a command button. You can copy the image from here and use it yourself if you like. We will need to place the Calendar Buttons like this:

And above the first textbox we need to add a command button. After you put the button on the form the Wizard opens up. Click the “Cancel” button. Name the button “btnExportApptByDates” and change the caption to “Export By Dates”.

After changing the Form’s Caption to “Export Appointments to Outlook” and adding some labels with solid backgrounds and some lines and rectangles it makes our Form more pleasing to the eye and easier to use. Now we can begin adding code.

Open the “Dates Selected” textbox’s Properties Sheet. Click the other tab and name the textbox txtDateSelection. We will display the dates selected there.

Name the “One Date” textbox txtOneDate and the image or button beside it btnOneDate. Name the “Start Date” textbox txtStartDate and the image or button beside it btnStartDate and name the “End Date” textbox txtEndDate and the button btnEndDate.

For the Calendar Buttons we will first get a global variable to add the date to the textbox control—in this case the txtOneDate textbox.

Set ctlIn = Me.Controls("txtOneDate")
DoCmd.OpenForm "frmCalendar" ' Launch calendar form

The first line of code sets the variable and the second line of code opens the Pop-up Calendar Form. The Calendar is actually an Access Form so you do not have to worry about versions, broken References, and dll files. You can find the link to my site to download the free Calendar along with the code and Module at the end of this article.

If we have previously left dates in the other textboxes we want to clear them. So the procedure will look like this:

Private Sub btnOneDate_Click()
    ' Use a Global Variable to get the selected date
    Set ctlIn = Me.Controls("txtOneDate")
    DoCmd.OpenForm "frmCalendar" ' Launch calendar form

    ' Clear the other textboxes
    Me.txtStartDate = vbNullString
    Me.txtEndDate = vbNullString
End Sub

We are using vbNullString instead of “” because vbNullString does not use as much memory and it makes it clear exactly what we are doing.

You can set the code for the other two Calendar buttons similarly.

Private Sub btnStartDate_Click()
    ' Use a Global Variable to get the selected date
    Set ctlIn = Me.Controls("txtStartDate")
    DoCmd.OpenForm "frmCalendar" ' Launch calendar form

    ' Clear txtOneDate
    Me.txtOneDate = vbNullString
End Sub

Private Sub btnEndDate_Click()
    ' Use a Global Variable to get the selected date
    Set ctlIn = Me.Controls("txtEndDate")
    DoCmd.OpenForm "frmCalendar" ' Launch calendar form

    ' Clear txtOneDate
    Me.txtOneDate = vbNullString
End Sub

After the Calendar adds the date to the textbox, it sets the focus on it also. This gives us an opportunity to do some necessary work with code. The most important things we need to do is format the date for use in a query and add it to the Date Selected textbox.

Private Sub txtOneDate_GotFocus()
    ' If txtOneDate has a value
    If Len(Me.txtOneDate & vbNullString) > 0 Then

        ' Clear the other textboxes
        Me.txtStartDate = vbNullString
        Me.txtEndDate = vbNullString

        'Add the Formatted Date to the DateSelection textbox
        Me.txtDateSelection = "#" & Me.txtOneDate & "#"
    End If
End Sub

Dates in Access require the # “delimiters”. This tells Access that the value between the # characters are dates or time values

After dates are added to the Start Date or the End Date, there is a little more work to do before we can use the dates. We want to be sure that the Start Date is not later than the End Date. The CDate Function converts the textbox’s String to a DateTime DataType so we can make sure that the Dates will work correctly. If we did not convert the string into a date, we would be geting the alphabetical String order instead of the numeric Date order.

Private Sub txtStartDate_GotFocus()
    ' Declare variables to use hold the formatted dates
    Dim strStartDate As String
    Dim strEndDate As String

   ' Check to see that there is an End Date
    If Len(Me.txtEndDate & vbNullString) > 0 Then

        ' Make sure the Start Date is not later than the End Date
        If CDate(Me.txtEndDate) > CDate(Me.txtStartDate) Then

            ' Format the dates and pass the value to the variables
            strStartDate = "#" & Me.txtStartDate & "#"
            strEndDate = "#" & Me.txtEndDate & "#"

            ' Store the dates in the Hidden Form for later use
            Forms!frmDataHoldPAW!htxtStartDate = strStartDate
            Forms!frmDataHoldPAW!htxtEndDate = strEndDate

            ' Display the date range in the Date Selection textbox
            Me.txtDateSelection = "BETWEEN #" & Me.txtStartDate _
              & "# AND #" & Me.txtEndDate & "#"

        End If
    End If
End Sub

Almost identical code is used after the End Date is added to the textbox.

Private Sub txtEndDate_GotFocus()
    ' Declare variables to use to hold the formatted dates
    Dim strStartDate As String
    Dim strEndDate As String

    ' Check to see that there is an Start Date
    If Len(Me.txtStartDate & vbNullString) > 0 Then

        ' Make sure the Start Date is not later than the End Date
        If CDate(Me.txtEndDate) > CDate(Me.txtStartDate) Then

            ' Format the dates and pass the value to the variables
            strStartDate = "#" & Me.txtStartDate & "#"
            strEndDate = "#" & Me.txtEndDate & "#"

            ' Store the dates in the Hidden Form for later use
            Forms!frmDataHoldPAW!htxtStartDate = strStartDate
            Forms!frmDataHoldPAW!htxtEndDate = strEndDate

            ' Display the date range in the Date Selection textbox
            Me.txtDateSelection = "BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
        End If
    End If
End Sub

I saved the best code for last. When you make a selection in the Select Dates combobox it adds the right dates to either the One Date textbox or the Start Date and End Date textboxes. We named the combobox cboDateRanges. It uses a very helpful bit of code that you may need to use many times. We use the combobox’s AfterUpdate Event to get the right dates in the right places.

Private Sub cboDateRanges_AfterUpdate()
    Select Case cboDateRanges
        Case "All"
            Me.txtStartDate = #1/1/1900#
            Me.txtEndDate = #12/31/2050#
            Me.txtOneDate = Null
        Case "Today"
            Me.txtStartDate = Null
            Me.txtEndDate = Null
            Me.txtOneDate = Date
            Me.txtStartDate = Me.txtOneDate
            Me.txtEndDate = Me.txtOneDate
        Case "Yesterday"
            Me.txtStartDate = Null
            Me.txtEndDate = Null
            Me.txtOneDate = Date - 1
            Me.txtStartDate = Me.txtOneDate
            Me.txtEndDate = Me.txtOneDate
        Case "Last Sunday"
            Me.txtStartDate = Null
            Me.txtEndDate = Null
            Me.txtOneDate = Date - Weekday(Date) + 1
            Me.txtStartDate = Me.txtOneDate
            Me.txtEndDate = Me.txtOneDate
        Case "This Month"
            Me.txtStartDate = DateSerial(Year(Date), Month(Date), 1)
            Me.txtEndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
            Me.txtOneDate = Null
        Case "This Quarter"
            Me.txtStartDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
            Me.txtEndDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 4, 0)
            Me.txtOneDate = Null
        Case "This Year"
            Me.txtStartDate = DateSerial(Year(Date), 1, 1)
            Me.txtEndDate = DateSerial(Year(Date), 12, 31)
            Me.txtOneDate = Null
        Case "Last Month"
            Me.txtStartDate = DateSerial(Year(Date), Month(Date) - 1, 1)
            Me.txtEndDate = DateSerial(Year(Date), Month(Date), 0)
            Me.txtOneDate = Null
        Case "Last Quarter"
            Me.txtStartDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1 - 3, 1)
            Me.txtEndDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 4 - 3, 0)
            Me.txtOneDate = Null
        Case "Last Year"
            Me.txtStartDate = DateSerial(Year(Date) - 1, 1, 1)
            Me.txtEndDate = DateSerial(Year(Date) - 1, 12, 31)
            Me.txtOneDate = Null
        Case "Month to Date"
            Me.txtStartDate = DateSerial(Year(Date), Month(Date), 1)
            Me.txtEndDate = Date
            Me.txtOneDate = Null
        Case "Quarter to Date"
            Me.txtStartDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
            Me.txtEndDate = Date
            Me.txtOneDate = Null
        Case "Year to Date"
            Me.txtStartDate = DateSerial(Year(Date), 1, 1)
            Me.txtEndDate = Date
            Me.txtOneDate = Null
        Case "Last 12 Months"
            Me.txtStartDate = DateSerial(Year(Date) - 1, Month(Date), Day(Date) + 1)
            Me.txtEndDate = Date
            Me.txtOneDate = Null
        Case "January"
            Me.txtStartDate = DateSerial(Year(Date), 1, 1)
            Me.txtEndDate = DateSerial(Year(Date), 2, 1 - 1)
            Me.txtOneDate = Null
        Case "February"
            Me.txtStartDate = DateSerial(Year(Date), 2, 1)
            Me.txtEndDate = DateSerial(Year(Date), 3, 1 - 1)
            Me.txtOneDate = Null
        Case "March"
            Me.txtStartDate = DateSerial(Year(Date), 3, 1)
            Me.txtEndDate = DateSerial(Year(Date), 4, 1 - 1)
            Me.txtOneDate = Null
        Case "April"
            Me.txtStartDate = DateSerial(Year(Date), 4, 1)
            Me.txtEndDate = DateSerial(Year(Date), 5, 1 - 1)
            Me.txtOneDate = Null
        Case "May"
            Me.txtStartDate = DateSerial(Year(Date), 5, 1)
            Me.txtEndDate = DateSerial(Year(Date), 6, 1 - 1)
            Me.txtOneDate = Null
        Case "June"
            Me.txtStartDate = DateSerial(Year(Date), 6, 1)
            Me.txtEndDate = DateSerial(Year(Date), 7, 1 - 1)
        Case "July"
            Me.txtStartDate = DateSerial(Year(Date), 7, 1)
            Me.txtEndDate = DateSerial(Year(Date), 8, 1 - 1)
            Me.txtOneDate = Null
        Case "August"
            Me.txtStartDate = DateSerial(Year(Date), 8, 1)
            Me.txtEndDate = DateSerial(Year(Date), 9, 1 - 1)
            Me.txtOneDate = Null
        Case "September"
            Me.txtStartDate = DateSerial(Year(Date), 9, 1)
            Me.txtEndDate = DateSerial(Year(Date), 10, 1 - 1)
            Me.txtOneDate = Null
        Case "October"
            Me.txtStartDate = DateSerial(Year(Date), 10, 1)
            Me.txtEndDate = DateSerial(Year(Date), 11, 1 - 1)
            Me.txtOneDate = Null
        Case "November"
            Me.txtStartDate = DateSerial(Year(Date), 11, 1)
            Me.txtEndDate = DateSerial(Year(Date), 12, 1 - 1)
            Me.txtOneDate = Null
        Case "December"
            Me.txtStartDate = DateSerial(Year(Date), 12, 1)
            Me.txtEndDate = DateSerial(Year(Date), 13, 1 - 1)
            Me.txtOneDate = Null
    End Select

    If IsNull(Me.txtOneDate) Then
        ' Put the date range in the Date Selection textbox
        Me.txtDateSelection = "BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
    Else
        Me.txtDateSelection = "#" & Me.txtOneDate & "#"
    End If
End Sub

You may wonder how the code can get the last day of a certain month or the date of last Sunday for example. Actually it is not very hard at all. The code gets the first day of the next Month and subtracts one day to get the last day of the Previous Month. It works similarly to get last Sunday or any day of the week. For more information about the code look up DateSerial in Access VBA Help.

The Dialog Form is almost ready to use with Date Selection. In our next article in this series we will show you how to build a SQL “Where” string and how to loop through a Recordset to add Appointments from the selected Date Range to the Outlook Calendar.