Update form after a NotInList event

Posted on 2009-02-22
Last Modified: 2012-06-27
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

Question by:DoctorSwamp
    LVL 84
    See Method 2 in this KB article:


    Author Comment

    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.

    Author Comment

    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.
    LVL 84

    Accepted Solution

    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 ...


    Author Closing Comment

    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.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now