Closing msgbox before function fires (Not_In_List)

[neilabbott]

Closing msgbox before a function fires

The folllowing is a Not_in_List event procedure in Access 2002
The form has a combo linked to an SQLview of my contacts in OL.
The essence of the code is:
If not in list, open new contact form in OL, save it (in OL), come back to Access form, save record and requery with new contact in the combo list.
Problem is, the msgbox is still there popped up over the Access form when the new contact has been saved (into OL) - even though the user clicks YES to add a new contact
How do I avoid this problem?
Thanks - Neil

Code is:
Option Compare Database
Option Explicit
Public golApp As Outlook.Application
Public gnspNameSpace As Outlook.NameSpace



Function InitializeOutlook() As Boolean

On Error GoTo Init_Err

Set golApp = New Outlook.Application ' Application object.
Set gnspNameSpace = golApp.GetNamespace("MAPI") ' Namespace object.

InitializeOutlook = True

Init_End:
Exit Function
Init_Err:
InitializeOutlook = False
Resume Init_End
End Function

Private Sub Form_Open(Cancel As Integer)
'new job record numbering routine - kept separate from autonumber field
Dim JNumber As Integer

JNumber = DMax("Job_Number", "M_jobs") + 1
MsgBox ("JNumber = " & JNumber)
Me.Job_Number = JNumber
End Sub

Private Sub Principal_Combo_NotInList(newdata As String, Response As Integer)
Response = acDataErrContinue
Dim ans As Variant
Dim StrNewData As String
' new account
ans = MsgBox("Do you want to add this principal?", vbYesNo, "Add new principal?")

If ans = vbNo Then
Me.Principal_Combo = Null
'take user back to combo list to choose one from the list
DoCmd.GoToControl "Principal_Combo"
GoTo exit_it
End If
'what needs to be done here to close the msgbox before Outlook throws up it's new contact form ?

StrNewData = newdata

If CreateContact(StrNewData) = True Then
End If
'When new contact has been saved (via OL contact form) the msgbox is still active when the code comes back here!!

DoCmd.RunCommand acCmdSaveRecord
'
Form_F_New_Job_Data_Entry.Principal_Combo.Requery
'
Form_F_New_Job_Data_Entry.Principal_Ref.SetFocus

exit_it:
End Sub

Function CreateContact(varNewData As Variant) As Boolean
Dim objNewContact As Outlook.ContactItem
Dim blnResolveSuccess As Boolean

If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "unable initialize Outlook - what next?"
Exit Function
End If
End If

Set golApp = New Outlook.Application
Set objNewContact = golApp.CreateItem(olContactItem)

'Need to specify a non-defaultfolder for contact store

With objNewContact
.CompanyName = varNewData
.Display
'save new contact via its own form
End With
End Function

Thanks
Neil Abbott

 

[Blorf]

Hi. I didn't review all of your code, so if I am confused, forgive me. But can you not set the controls limit to list property to false, and still have an event trigger on not in list?

Thanks,
ChaZ

 

[neilabbott]

ChaZ - I think I would still need to deal with grabbing the new record from the user in either situation.
THe problem is dealing with the msgbox popup which stays active (after hitting 'yes') when the code returns to the form.
So, the user says "yes" - adds the new contact and saves/closes the new contact (Outlook) window. When the OL window (inspector?) closes the darned msgbox is still there as if waiting for user input. If the user says yes again we're in a loop. If "No" then the code crashes because the record has not been saved (before the requery).