Adding a New Entry in a pick list

  • Thread starter Thread starter Chris B
  • Start date Start date
C

Chris B

I have a pick list of employees to assign task to. I want
to be able to create a new person if not on the list with
all information. I built a record that list the name as
** Create New ** and when selected a sub-form pops up to
enter the data. What I want is the list to be updated to
include this new person, and to select that name in the
box field.

I have seen this done before on the board, but can not
find the results.

Thanks

Chris B.
 
try the following code in your combo box's NotInList event, as

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

If MsgBox("Do you want to add a new record to the list?", _
vbDefaultButton1 + vbYesNo) = vbYes Then
DoCmd.OpenForm "MyForm", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!MyCombo = Null
Me!MyCombo.Dropdown
End If

End Sub

opening the form in Dialog suspends the code until the form is closed.
substitute the correct control and form names, and msgbox text, of course.
see below the explanations for acDataErrAdded and acDataErrContinue, pasted
from Access Help, NotInList Event topic.
acDataErrAdded - Doesn't display a message to the user but enables you to
add the entry to the combo box list in the NotInList event procedure. After
the entry is added, Microsoft Access updates the list by requerying the
combo box. Microsoft Access then rechecks the string against the combo box
list, and saves the value in the NewData argument in the field the combo box
is bound to. If the string is not in the list, then Microsoft Access
displays an error message.

acDataErrContinue - Doesn't display the default message to the user. You can
use this when you want to display a custom message to the user. For example,
the event procedure could display a custom dialog box asking if the user
wanted to save the new entry. If the response is Yes, the event procedure
would add the new entry to the list and set the Response argument to
acDataErrAdded. If the response is No, the event procedure would set the
Response argument to acDataErrContinue.

hth
 
Back
Top