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.
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
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.
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.
ASKER
OK, I think I have an answer, which is to include this in the Contacts from Close event
If CurrentProject.AllForms("f rmEventsRe cord").IsL oaded Then
Forms!frmEventsRecord.sfmE ventPartic ipants.For m.cboParti cipant.Req uery
Exit Sub
End If
I'll keep trying this out but for time being just want to say thsank you for the suggestion.
If CurrentProject.AllForms("f
Forms!frmEventsRecord.sfmE
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
http://support.microsoft.com/kb/197526