• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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
        SendKeys Chr(vbKeyEscape)
        Dim stDocName As String
        stDocName = "frmContacts"
        DoCmd.OpenForm stDocName, , , , acFormAdd
End Sub

Open in new window

  • 3
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
See Method 2 in this KB article:


DoctorSwampAuthor Commented:
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.
DoctorSwampAuthor Commented:
OK, I think I have an answer, which is to include this in the Contacts from Close event

    If CurrentProject.AllForms("frmEventsRecord").IsLoaded Then
        Exit Sub
    End If

I'll keep trying this out but for time being just want to say thsank you for the suggestion.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't quite follow ... the Load event really has nothing to do with the code. Basically, the code at the link I provided simply opens a form named "Customers" as a Dialog form (which forces you to close that before you can move on to anything else), and then checks to see if anything was added and, if it was, requeries the combo. The acDataErrAdded is the trigger that forces Access to requery the combo ...

DoctorSwampAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now