[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • 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
        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

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

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

0
 
DoctorSwampAuthor Commented:
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.
0
 
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
        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.
0
 
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 ...

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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