NotInList Event, Combobox and PopUp form

[lastout]

Hello,

I did search on the forum for the answer to this question but everything I came up with wasn't quite what I'm looking for.

Here's what I'm trying to do. I have a combobox, cmbAgent on a form I use to enter data into tblProject. CmbAgent's control source is tblAgent while the form's (frmProject) record source is tblProject.

If the user enters a value into cmbAgent that isn't in the list, I want to have a popup form (frmPopUpAgent) appear so that the user can enter the new Agent info (phone number, cell number, email, etc.) and save it to tblAgent. After filling in the new info and closing the popup, I would like to have the new value appear in the combobox. And one more thing about the popup. When it appears after the user has entered a new name, for instance "Jerry," it would be nice if when the popup appears, the name Jerry is already entered in the firstname field of the new blank tblAgent record.

I've been trying to piece the code together bit by bit starting with what I thought would be the easiest part, opening the popup form on the NotInList event of cmbAgent:

Private Sub Agent_NotInList(NewData As String, Response As Integer)

DoCmd.OpenForm PopUpAgent, , , , acFormAdd, acDialog

End Sub

But the popup doesn't appear. I don't understand why. Interestingly though I can move to the next control without getting the dreaded "not a value in the list" error message. "Jerry" doesn't get added to the list but it does get saved to the Agent field in tblProject. Problem is, I need to have it added to the list as well as adding a new record with all the other relevant fields in tblAgent.

I would be terribly grateful for any help. Thanks! Denise

 

[Laois]

It should be
DoCmd.OpenForm "PopUpAgent", , , , acFormAdd, acDialog

You must put "" around the form name. If you are using the NotInList event of the combo box you can pass the NewData variable in the OpenArgs of the popup form. NewData is what ever the user has typed into the combo box. So if the user typed Jerry then NewData = Jerry.

DoCmd.OpenForm "PopUpAgent", , , , acFormAdd, acDialog, NewData

Then in the Load event of the popup form use something like this.

Me.FirstName = Me.OpenArgs

Hope this helps,

Dermot