NotInList problem

[lastout]

I'm having problems making my OnNotInList event property work for a combobox on a form. The form is called frmProject, the combobox is called cmbAgentFirstName. FrmProject is based on tblProject. CmbAgentFirstName is based on tblAgent. They each have primary keys that are autonumbers, ProjectID and AgentID.

I?m trying to allow the user to add a new Agent to the list of agents in the combobox by using the NotInList event to trigger frmPopUpAgent to load. So far I have that working with the following code:
___________________________________________________________
Private Sub cmbAgentFirstName_NotInList(NewData As String, Response As Integer)

Dim ctl As Control

Set ctl = Me.cmbAgentFirstName

If MsgBox(?Would you like to add a new Agent to the list??, , , , acFormAdd, acDialog
Response = acDataErrAdded
Ctl.RowSource = ctl.RowSource & ?;? & NewData

Else
Response = acDataErrContinue
Ctl.Undo
End if
End Sub
____________________________________________________________
Using this code, frmPopUpAgent opens, allows the user to add a new Agent and their info to tblAgent, but when I close the popup, I get the Access NotInList error message. If I close frmProject and then go look at tblAgent, the new Agent is added to the table. Then if I go back to frmProject, the new agent still doesn?t appear in the combobox list. I understand that with the code above I must have the RowSourceType property of the combobox set to Value List and the RowSource to the first names of the agents and their ID numbers (ID not visible to user).

Can anyone help me with why the new name is not added to the RowSource of the combobox and how to remedy this?

I have also tried to do this by using an SQL statement for the RowSource with the RowSourceType set to Table/Query.

I thought that by using Response = acDataErrAdded, that Access would automatically add the new Agent to the list and requery the RowSource. I get the Access NotInList error message before the popupform even loads.

lastout (the game's not over till it's over)

 

[jdegeorge]

This is what I used for a similar problem I had.

This is in the field's NOT IN LIST property on a form called frmAddRequests:

Dim db As Database, rs As Recordset, strMsg As String, NewPOC As Variant

strMsg = "'" & NewData & "' is not known"
strMsg = strMsg & "@Do you want to associate to the current request?"
strMsg = strMsg & "@Click Yes to link or No to re-type."
NewPOC = NewData
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add?&quot = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblPOC", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!POC_Name = NewPOC
rs.Update
[Forms]![frmAddRequests].Visible = False
DoCmd.OpenForm "frmAddPOC_Maintenance"

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If


Hope this helps!

Jim "Get it right the first time, that's the main thing..."