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;
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
Response = acDataErrContinue
Dim stDocName As String
stDocName = "frmContacts"
DoCmd.OpenForm stDocName, , , , acFormAdd