With this article we will complete our Appointment Dialog Form that will give us more choices and more powerful ways to manage both our Access and Outlook data. We are transforming it into something far better than just a Date Dialog Form. You will also learn new and important methods of managing data with Access.
1 ) Provide instructions for the user.
If others will be using our Dialog Form they may need some guidance. When you first open the Form there is no way for a user to know the right place to start. We can show a short message when the form first opens by adding the message on a Label. Add the Label as shown in the next image and type in some instructions on the Label. We have set the back color to draw the user’s attention immediately. Name the Label “lblStartInfo”. Next, open the Label’s Properties, click on the “Format” tab and set the “Visible” Property to “No”. Then add the following code to the Form’s On Current Event:
.
Private Sub Form_Current()
If Len(Me.txtDateSelection & vbNullString) = 0 Then
Me.lblStartInfo.Visible = True
Else
Me.lblStartInfo.Visible = False
End If
End Sub
If a date has not been chosen the txtDateSelection TextBox will be empty. Since selecting a date is the first step in using our Dialog Form our code will make the label visible when the Form opens but invisible when a date has been selected. We need to move the “Export By Dates” Button over to make some room. When our Form is opened it should look like this:
2 ) Add a Button to clear the Form.
Providing a way to clear the Form and start over would be helpful and it is not hard to do. Add a Button on the right side of our information label and name it “btnClearAll”. Add the following code to the Button’s On Click Event:
Private Sub btnClearAll_Click()
' Clear all controls on the Form
Me.txtDateSelection = vbNullString
Me.txtOneDate = vbNullString
Me.txtStartDate = vbNullString
Me.txtEndDate = vbNullString
Me.cboDateRanges = vbNullString
Me.tglSelectAll = False
Me.lstAppointments.RowSource = vbNullString
Me.lstAppointments.Requery
Me.lblStartInfo.Visible = False
Me.chkBusiness = False
Me.chkHolidays = False
Me.chkOther = False
Me.chkPersonal = False
End Sub
3 ) Add a Button to add all selected Appointments to Outlook.
Place the Button as shown in the image above and set the Caption to “Export Selections to Outlook”. Name the Button “cmdLstBoxToOutlook”.
4 ) Enter the code to add all selected Appointments to Outlook.
Now we get to add some interesting code. The code has plenty of comments to help you understand what it is doing. This code will get the ID of all the selected Appointments in the ListBox, build a SQL string, use it to open a Recordset, add all of the Appointment information to Outlook, set “Added to Outlook” to True, and then tell us how many Appointments were added to Outlook.
Private Sub cmdLstBoxToOutlook_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWhere As String
' This variable will be used to tell us how
' many Appointments were added to Outlook
Dim intCount As Integer
' This variable will be used in
' Looping through the ListBox
Dim i As Integer
' Use late binding to avoid the "Reference" issue
Dim olApp As Object 'Outlook.Application
Dim olAppt As Object 'olAppointmentItem
If isAppThere("Outlook.Application") = False Then
' Outlook is not open, create a new instance
Set olApp = CreateObject("Outlook.Application")
Else
' Outlook is already open--use this method
Set olApp = GetObject(, "Outlook.Application")
End If
' Build the first part of a SQL String that will include
' all of the Appointments selected in the ListBox
strSQL = "SELECT tblAppointments.* FROM tblAppointments "
' Start the first part of the SQL "WHERE" String
' The "WHERE" String will hold the Database Appointment ID
' Use the "IN" Predicate to make building the SQL easier
strWhere = "WHERE tblAppointments.ApptmntID IN("
We build the SQL “WHERE” String by using a Loop to go through all rows in the ListBox and get the ApptmntID of the rows that have been selected. The ListBox contains an Array. An Array is basically data that is arranged and defined in a specific manner. It can consist of just a list of items, but is usually arranged in rows and columns like the Appointments in our ListBox.
Because an Array’s dimensions are known, we can easily get specific data from our ListBox. A ListBox Array has a 0 based index. That means the index number of the first item in our ListBox is 0. We will use code to Loop through every Row in the ListBox starting at 0 until the last index number is reached. We use the ListCount Property to know when to stop looping. We need to subtract 1 from our ListCount because ListCount started at 1 instead of 0. This is done in our code like this:
For i = 0 To Me.lstAppointments.ListCount – 1.
Now we know how to get a specific Row using the variable “i”. But getting the Row is not enough. We need to get the ID number of each Appointment selected. We do this by using the Column Property. Each Row in the ListBox contains Columns. Our ID information is in the first Column. The Columns are also 0 based so our code is “.Column(0, i)”. The “0″ indicates the first Column in the Row and the “i” is our Row’s index number.
We want our “WHERE” String to look like this …IN(3, 14, 26). We must separate our ID numbers with a comma, which our code adds at the end of this line: strWhere = strWhere & lstAppointments.Column(0, i) & “, “. So our code will loop through the rows in the ListBox getting the value of the first Column of all rows that are selected, and separate the ID numbers with commas and a space.
.
' Run the Loop adding a comma , between the ID numbers
For i = 0 To Me.lstAppointments.ListCount - 1
If lstAppointments.Selected(i) Then
strWhere = strWhere & _
lstAppointments.Column(0, i) & ", "
End If
Next i
' Remove the last comma and enclose with a Parenthesis
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
' Put the whole SQL Statement together
strSQL = strSQL & strWhere
At this point as the code runs our completed strSQL statement will look like this:
strSQL = SELECT tblAppointments.* FROM tblAppointments WHERE tblAppointments.ApptmntID IN(10, 12, 16);
We will use our strSQL to open a Recordset which will only contain the Appointments that have been selected. Then we will then loop through those Appointments, creating a new Appointment in Outlook with each loop.
.
' Set a Reference to the CurrentDb
Set db = CurrentDb()
' Create a Recordset based on strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
' Begin a Loop through the Recordset
' Move to the first Record
rst.MoveFirst
' Loop to the End of the Recordset
Do Until rst.EOF
' Create a New Outlook Appointment Item
' with each loop through the Recordset
' 1 is an olAppointmentItem
Set olAppt = olApp.CreateItem(1)
' Add the data to the Appointment Properties
With olAppt
' Set the Appointment Property Values
.Start = Nz(rst!ApptDate) & " " & _
Nz(rst!ApptTime, vbNullString)
.End = Nz(rst!EndDate) & " " & _
Nz(rst!EndTime, vbNullString)
.Duration = Nz(rst!ApptLength, 0)
.Subject = Nz(rst!Appt)
.Body = Nz(rst!ApptNotes)
.Location = Nz(rst!Location)
If rst!ApptReminder = True Then
If rst!ApptDate < Now() Then
' Do Nothing no Reminder is needed
Else
If Not IsNull(rst!ReminderMinutes) Then
.ReminderOverrideDefault = True
.ReminderMinutesBeforeStart = _
rst!ReminderMinutes
.ReminderSet = True
End If
End If
End If
' Add the Category if it exists
.Categories = Nz(rst!Categories)
' Save the Appointment Item Properties
.Save
End With
' Use intCount to count the Appointments added
intCount = intCount + 1
' Set the AddedToOutlook Database Field to True
rst.Edit
rst!AddedToOutlook = -1 ' True
rst.Update
rst.MoveNext
Loop
' Release the Outlook object variables.
Set olAppt = Nothing
Set olApp = Nothing
' Inform the user
MsgBox intCount & _
" Appointments were added to Outlook.", _
vbInformation
End Sub
We can now select individual Appointments in our ListBox and add them to Outlook. But what if an Appointment is canceled and we need to delete it? We can do that.
5 ) Add a Button to Delete selected Appointments in our Access Database.
Place the “Delete Selections” Button on the Form according to the image below and add an appropriate caption. Name the Button “cmdDeleteSelected”.
6 ) Enter the code to Delete the Access Database Appointments we select.
It is important to understand that this will delete our Appointments in our Database but not in the Outlook Calendar. We will use the MsgBox as a reminder. Most of the remaining code we have examined previously and it needs no further comment except for one very important line of code, which we will examine.
.
Private Sub cmdDeleteSelected_Click()
Dim db As DAO.Database
Dim strSQL As String
Dim strWhere As String
' Declare a Variable to use to show
' how many records were deleted.
Dim lngRecDeleted As Long
Dim i As Integer
Select Case MsgBox("This action will delete the " & _
" selected Appointments from your Access Database. " _
& vbCrLf & "" _
& vbCrLf & " Are you sure you want to" & _
" permanently delete these Appointments?" _
, vbYesNo Or vbExclamation Or vbDefaultButton2, _
"Delete Database Records?")
Case vbYes
' Let the code continue
Case vbNo
MsgBox "Deletion has been cancelled! ", _
vbInformation
Exit Sub
End Select
' Build a SQL Statement to Delete the Selected Appointments
strSQL = "DELETE tblAppointments.* FROM tblAppointments "
strWhere = "Where tblAppointments.ApptmntID IN("
' Loop through the ListBox and gather the data
For i = 0 To Me.lstAppointments.ListCount - 1
If lstAppointments.Selected(i) Then
strWhere = strWhere & _
lstAppointments.Column(0, i) & ", "
End If
Next i
' Complete the end of Where Statement
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
' Put all the SQL together
strSQL = strSQL & strWhere
' Set a Reference to the CurrentDb
Set db = CurrentDb()
' Delete the Selections but Rollback
' the deletions if there is an error.
db.Execute strSQL, dbFailOnError
' Inform the user
lngRecDeleted = db.RecordsAffected
MsgBox lngRecDeleted & _
" Appointments were Deleted from the Database.", _
vbInformation
End Sub
Most of the code here we have seen previously. However, the line of code that actually did the work is an important snippet of code that we should discuss.
That important line of code is
db.Execute strSQL, dbFailOnError.
The Execute Method, when coupled with the dbFailOnError Constant protects our data by rolling back the SQL’s changes if there are any errors executing the SQL. Our Database ends up exactly the same as it was before the Rollback. This is especially important if you are doing payroll or taxes!
If you have followed these articles and completed your Appointment Dialog Form then you have a valuable tool that can increase your productivity and save time. Even more important is the knowledge and ability you have gained. This Dialog Form handled our Appointments in our Access Database. Our next Dialog Form will help us manage our Appointments in Outlook.
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 when you purchase a Premium Membership with Total Access.
Here is how our Appointment Dialog Form appears now:
Our Completed Appointment Dialog Form
Happy computing,
Patrick (Pat) Wood
Gaining Access
