Link to home
Start Free TrialLog in
Avatar of DoctorSwamp
DoctorSwamp

asked on

Update form after a NotInList event

I'm using a subform with a combo lookup for people's names. The subform is for people attending events, and its parent Events form stores the event details.
If a new name is entered the NotInList procedure opens a separate Contacts form that stores names, and allows the full new entry to be completed. On closing this form and returning to the original I need the combo to update so the new entry appears in the drop-down list.
The Contacts form uses a ContactID as primary key, and then separate first and last names.
The combo on the Events form looks up a query based on the Contacts table, displaying people's names as LastName, FirstName, with the ContactID as bound column with zero width.

Code snippets show below.


Look-up combo source:
SELECT qryContacts.ContactID, qryContacts.ContactName AS ParticipantName FROM qryContacts WHERE (((qryContacts.ContactName)>"0")) ORDER BY qryContacts.ContactName; 
 
NotInList procedure
Private Sub cboParticipant_NotInList(NewData As String, Response As Integer)
        If MsgBox("This person isn't in the list." & Chr(13) & _
           "Create a record for this person in the Contacts form?", vbOKCancel) = vbCancel Then
           Exit Sub
        End If
        Response = acDataErrContinue
        Me.cboParticipant.Undo
        SendKeys Chr(vbKeyEscape)
'Err_cboOrganiser_NotInList:
        Dim stDocName As String
        stDocName = "frmContacts"
        DoCmd.OpenForm stDocName, , , , acFormAdd
End Sub

Open in new window

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

See Method 2 in this KB article:

http://support.microsoft.com/kb/197526

Avatar of DoctorSwamp
DoctorSwamp

ASKER

Thanks
I'm leaving the Events form open so it stays at the event I'm working on with a blank subform ready for the new person I'm creating when the NotInList event runs. So the Load event isn't triggered when returning - I think this is where OpenArgs would have come in but I'm not sure what it would have returned anyway.
I 've been trying to find a way of requerying/refreshing the combo on the open Events form as the Contacts form closes.
OK, I think I have an answer, which is to include this in the Contacts from Close event

    If CurrentProject.AllForms("frmEventsRecord").IsLoaded Then
        Forms!frmEventsRecord.sfmEventParticipants.Form.cboParticipant.Requery
        Exit Sub
    End If

I'll keep trying this out but for time being just want to say thsank you for the suggestion.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
May be duplicating an answer already entered but thanks, you're ahead of me on the technicalities and your link is useful in asnwering my question.