How to pass Outlook AppointmenItem data back to Access form textbox-CodePudding

I have an Access form which will create an Outlook AppointmentItem for the current record. The .Start and .Categories of the AppointmentItem are derived from user-input on the form. I have a command button which will find and open the AppointmentItem so that the user may edit it. After the user makes the edits I want to pass back the edited information to the form controls so that the user can see the updated start time and category without having to open the AppointmentItem. I am currently storing Public variables for the two bits of data, however, I cannot figure out the process by which the variables are updated with the data from the AppointmentItem. Here is the code for finding the existing AppointmentItem:

Option Compare Database
Public gdtStart As Date
Public gstrCat As String
Option Explicit

Function FindExistingAppt(strPath As String)

    Dim OApp        As Object
    Dim OAppt       As Object
    Dim ONS         As Object
    Dim ORecipient  As Outlook.Recipient
    Dim OFolder     As Object
    Dim sFilter     As String

    Const olAppointmentItem = 1
    Dim bAppOpened            As Boolean
    'Initiate our instance of the oApp object so we can interact with Outlook
    On Error Resume Next
    Set OApp = GetObject(, "Outlook.Application")    'Bind to existing instance of Outlook
    If err.Number <> 0 Then    'Could not get instance of Outlook, so create a new one
        err.Clear
        Set OApp = CreateObject("Outlook.Application")
        bAppOpened = False    'Outlook was not already running, we had to start it
    Else
        bAppOpened = True    'Outlook was already running
    End If
    On Error GoTo Error_Handler
    
    Set OApp = GetObject(, "Outlook.Application")
    Set ONS = OApp.GetNamespace("MAPI")
    
    Set ORecipient = ONS.CreateRecipient("xxxxxxxxxxxxx")
    
    'my example uses a shared folder but you can change it to your defaul
    Set OFolder = ONS.GetSharedDefaultFolder(ORecipient, olFolderCalendar)
    'use your ID here
    sFilter = "[Mileage] = " & strPath & ""
    
    If Not OFolder Is Nothing Then
    
        Set OAppt = OFolder.Items.Find(sFilter)
    
        If OAppt Is Nothing Then
            MsgBox "Could not find appointment"
        Else
        
            With OAppt
              .Display
           
            End With
        End If
    End If
gdtStart = OAppt.Start
gstrCat = OAppt.Categories
    
Error_Handler_Exit:
    On Error Resume Next
    If Not OAppt Is Nothing Then Set OAppt = Nothing
    If Not OApp Is Nothing Then Set OApp = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & err.Number & vbCrLf & _
           "Error Source: FindExistingAppt" & vbCrLf & _
           "Error Description: " & err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl), _
           vbOKOnly   vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit

End Function

Here is the code from the command button which opens the AppointmentItem.

Private Sub cmdFindAppt_Click()
'Goes to the OutlookApp module and uses the FindExistingAppt function to look for an appointment that has
'already been created to the Warrants Outlook calendar, and if it found, opens the appointment. After edits are
'made the Appointment Date and Category are updated on the form.
    Call FindExistingAppt(Me.ID)
    Me.ApptDate = gdtStart
    Me.Category = gstrCat
End Sub

I do not know how to update the form controls with the Public variables. After the code runs the form controls do not reflect the values of the stored Public variables. If I open the AppointmentItem one more time (using the FindExistingAppt code - not by opening the AppointmentItem in Outlook proper), and close either by saving or not, then the form controls update.

All this being said, I am not even sure that this is the best way to update those form controls. If anyone has any better ideas I would appreciate your help.

CodePudding user response：

The way you update those form controls should be working fine. Try to check if the FindExistingAppt function really does assign values into public variables by debugging the function. Use locals window to watch the variables values (gdtStart and gstrCat) while debugging.

Just sharing common practice: If your procedure/routine does not return the value then you can declare it with Sub keyword instead Function keyword.

CodePudding user response：

This probably doesn't work because the VBA code continues to run after OAppt.Display.

So any changes you make and save to OAppt won't be read to your variables, because the function is already finished.

Try using the Modal parameter, this may halt the code until OAppt is closed.

        With OAppt
          .Display True

https://learn.microsoft.com/en-us/office/vba/api/outlook.appointmentitem.display

Also add Debug.Print commands and/or breakpoints before and after .Display to see what's happening.