Yet another form question...need the list to update.

[SomeGuy]

I have a combo box that allows the data entry operator to select an employee's name from the list on the main form.

However, if the name is not on the list I would like them to enter in the new employee info.

I could set it up to open up the employee form by pressing a button or something but I found that upon selecting the employee form and entering the new employee info, the combo box does not seem to update and reflect the change in its list. What am I doing wrong?

Also, can the field be set up to automatically open up the employee form if someone enters an unfamiliar name in the combo box field?

Thanks for any suggestions. =+}

 

[Jack Cowley]

This article will show you how to use the NotInList event of the Combo box....
http://support.microsoft.com/support/kb/articles/Q161/0/07.asp?LN=EN-US&SD=gn&FR=0

HTH,
Jack

 

[andyh]

Private Sub Product_Type_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me.Product_Type
' Prompt user to verify they wish to add new value.
If MsgBox("Product is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to products table.
NewData = CapitalizeFirst(NewData)
strSQL = " INSERT INTO Products ( ProductType ) SELECT '" & Proper(NewData) & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

exit_Product_Type_NotInList:
Exit Sub

err_Product_Type_NotInList:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox Str(Err)
MsgBox Err.Description
Resume exit_Product_Type_NotInList
End If
End Sub