MS-Outlook like Calendar

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everybody again,

I need to create a form like the one built in MS-Outlook to record
appointments.

Is there any way to do that - Create such a form and record appointments
within a database?

Thanking yoy in advance,

George ([email protected])
 
Why not just use Access VBA to create Outlook AppointmentItems and keep
the info in Outlook?

The function below gives you an idea of how easiy it is. Please NOTE
that I copied this code as is from an existing database as such it will
not work without several changes.

Function createOutlookAppointmentFromId(lngTransportId As Long)

'Dim objOutlook As Outlook.Application
Dim objOutlook As Object
Dim newAppt As Object
Dim lngPrimaryPassengerId As Long
Dim strPrimaryPassengerFirstName As String
Dim strPrimaryPassengerLastName As String
Dim rs As DAO.Recordset

ynCreateFail = False

DoCmd.Hourglass True

Call updateAdvisory("Looking up appointment details from database")

lngPrimaryPassengerId = DLookup("lngClientId", "tblTransferGuests",
"lngTransportId = " & lngTransportId & " AND ynPrimaryPassenger = True")
strPrimaryPassengerFirstName = DLookup("txtClientFirstName",
"tblClients", "lngClientId = " & lngPrimaryPassengerId)
strPrimaryPassengerLastName = DLookup("txtClientLastName",
"tblClients", "lngClientId = " & lngPrimaryPassengerId)
strPrimaryPassenger = strPrimaryPassengerFirstName & " " &
strPrimaryPassengerLastName

Set objOutlook = CreateObject("Outlook.application")
Set newAppt = objOutlook.CreateItem(WMS_olAppointmentItem)

newAppt.UserProperties.Add "dbAccessID", WMS_olNumber
newAppt.UserProperties.Add "dbLastModified", WMS_olDateTime
newAppt.UserProperties.Add "dbStatus", WMS_olText

Call updateAdvisory("Creating new appointment")

Call loadTransport(lngTransportId)

'This is the heart and soul of creating the AppointmentItem
With newAppt
.Start = glb_dteDate & " " & glb_dteTimeScheduled
.End = glb_dteDate & " " & DateAdd("h", 1,
CDate(glb_dteTimeScheduled))
.Subject = strPrimaryPassenger
.Location = glb_strOrigination & " - " & glb_strDestination
.UserProperties(1) = lngTransportId
.UserProperties(2) = Now
.UserProperties(3) = DLookup("txtStatusDescription",
"tblStatusCodes", "txtStatus = '" & glb_strStatus & "'")
.Body = getBodyText(lngTransportId)
.BusyStatus = WMS_olBusy
If glb_dteDate < Now() Then
.ReminderSet = False
End If
.Categories = "Reservations"
.MessageClass = "IPM.Appointment.Reservations"
.Save
If newAppt.UserProperties(1) = 0 Then
ynCreateFail = True
Else
ynCreateFail = False
End If
createOutlookAppointmentFromId = newAppt.EntryID
End With

If Err.Number <> 0 Or ynCreateFail = True Then
createOutlookAppointmentFromId = Null
updateAdvisory ("Failed to create Outlook appointment")
updateAdvisory ("Error Number " & Err.Number & " " &
Err.Description)
If ynCreateFail Then updateAdvisory ("Unable to confirm that
the transport ID was attached to the appointment item")
Else
Call updateAdvisory("New appointment created")
Call updateAdvisory("Updating database record")
Set rs = CurrentDb.OpenRecordset("SELECT lngTransportId,
txtOutlookEntryId, dteOutlookLastUpdated FROM tblTransports WHERE
lngTransportId = " & lngTransportId)
With rs
.Edit
.Fields("txtOutlookEntryId") = createOutlookAppointmentFromId
.Fields("dteOutlookLastUpdated") = Now
.Update
.Close
End With
Set rs = Nothing
Call updateAdvisory("Completed")
End If

DoCmd.Hourglass False
DoCmd.Echo True

Set newAppt = Nothing
Set objOutlook = Nothing

Call clearTransport

End Function
 
George said:
Hello everybody again,

I need to create a form like the one built in MS-Outlook to record
appointments.

Is there any way to do that - Create such a form and record appointments
within a database?

Thanking yoy in advance,

George ([email protected])
Click to expand...

I needed to do something similar for a database, where we wanted a group
calendar separate from everyone's Outlook calendars. There's probably a
clever way to do this better with some ActiveX control, but it worked for me:

Lay out your Calendar Form with boxes for dates, little boxes for the day
numbers, and headings for week days. Add 30+ labels that fit in the big
boxes; these are going to be your appointments. Give each of these a name
you can remember, numbered sequentially. For example, my controls are
BoxMon1, BoxTue1, BoxWed1 through BoxSun6; LabelMon1 through LabelSun6; and
Appt1 through Appt60.

You'll need a function that labels the days of the calendar. I use
"LabelMon1.Caption = Day(FirstDate): LabelTue1.Caption = Day(FirstDate+1)..."

You'll need another function that creates the appointments. Loop through
all the appointments in your DB that start <= the last day showing, and end
= the first day showing. For each one, grab the next ApptX label, position
Click to expand...
it on the correct day programmatically (you'll need to keep track of how many
Appts you've assigned to each day-box). Set the ApptX caption to the
appointment's time and name. Set Visible = False for any unused Appts.

I've added a little more; it color-codes months, highlights the current day,
automatically adds people's names, handles multiple-day appointments, etc.

If this seems a little overwhelming, please see this screen shot:
http://www.tmscontracting.com/Images/Calendar.gif. I'd be glad to share the
schema and source code.

Of course, all this may be overkill! You can make something much simpler in
the form of a list of appointments, with a blank line and a label each time
the day changes. Like this:

-----Monday-----
10:00: Boss get his shoes shined
3:00: Fred doctor's appt
4:00: Staff meeting

-----Tuesday-----
8:30: Bob doctor's appt
12:00 Boss lunch with headhunter

If that's good enough for your needs, you can get by with a simple
Appointments table, query it for >= Now(), and (if you're using Access 2003)
insert extra lines after the fact.
 
JonOfAllTrades said:
:




I needed to do something similar for a database, where we wanted a group
calendar separate from everyone's Outlook calendars.
Click to expand...

Are you using Exchange?


There's probably a
 
JonOfAllTrades,

Sorry to break the thread, but I have been trying to create exactly what you
have shown on your screen shot. Insted of showing appointments though, I want
to total info from a query, and show on corresponding days.

You had mentioned sharing the source code. Would you be so kind to do so.

Thanks in advance,

Steve
 
steve p said:
JonOfAllTrades,

Sorry to break the thread, but I have been trying to create exactly what you
have shown on your screen shot. Insted of showing appointments though, I want
to total info from a query, and show on corresponding days.

You had mentioned sharing the source code. Would you be so kind to do so.

Thanks in advance,

Steve
Click to expand...

Sorry for the delay, for some reason I don't get the e-mail notifications I
ask for on this board.
I don't have access to that source code anymore, I've changed companies.
I'm sure I can still help you, though. How much do you have working?
 
Back
Top