update table combo box

[loneranger27]

Hi,

I have a combobox that the user selects an agent's name from. If the name entered by the user is not in the list then the user is prompted to add. This works fine. I would like for the name that is keyed into the combo box by the user to appear in the form where the user enters the name of the agent to add to the table. Below is the code that I am using any help would be appreciated.

Private Sub Combo77_NotInList(NewData As String, Response As Integer)
On Error GoTo combo77_notinlist_err
'add a new record to the agent table
'and requery the agent combo box
Dim NewAgent As Integer, msgtitle As String, msgdialog As Integer
Const mb_yesno = 4
Const mb_iconexclamation = 48
Const mb_defbutton1 = 0, idyes = 6, idno = 7
'make sure the user wants to add record
msgtitle = "Agent NOT in the list"
msgdialog = mb_yesno + mb_iconexclamation + mb_defbutton1
NewAgent = MsgBox("Do you want to ADD Agent?", msgdialog, msgtitle)
If NewAgent = idno Then
Response = DATA_ERRCONTINUE
Else
'[AGENT] = [Combo77]
DoCmd.OpenForm "desc", acNormal, , , acAdd, acDialog
Response = DATA_ERRADDED
End If
combo77_notinlist_exit:
Exit Sub

combo77_notinlist_err:
' MsgBox Err.Description
Resume combo77_notinlist_exit

 

[RoyVidar]

Hi!

The name entered, which also triggers the Not In List event, is contained in the NewData variable. This can be be passed with the openargs of the docmd command.

[tt]DoCmd.OpenForm "desc", acNormal, , , acAdd, acDialog, NewData[/tt]

There it can be retrieved with the me.openargs in the forms on open event.

For more details, take a look at KenReay's faq on the subject faq702-4283

HTH Roy-Vidar