ADDING A NEW RECORD TO A LOOK UP FIELD WHILE IN A FORM

R

Renee

In my form I have a field that looks up a employee name so as I start to type
that persons name it brings it up in the field. The problem I am having is
when I have a new employee I have to close my form open my table where my
list of names is, enter the new name close the table and then re-open my form
to continue to enter my information. Is there any way I can add the name to
the table without doing this?
 
V

Van T. Dinh

If you use a ComboBox as the display for the look-up, check Access VB Help
on the "NotInList" Event ...
 
R

Renee

Ok, so I entered the not in list event for that combo box and set it up so it
will ask me if I want to add new entry or cancel once I click 'ok' I get a
message that says: Characters found after end of SQL statement. This is the
event I but in:

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

' Return Control object that points to combo box.
Set ctl = Me!OPERATORID
' Prompt user to verify they wish to add new value.
If MsgBox("Value 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 row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
 
V

Van T. Dinh

What kind of RowSource you used for the ComboBox? An SQL String? Or a
Query? Or a List?

In generally, if you use an SQL String or a Query, you need to Requery the
ComboBox ... In Access 2000 (???), there is also the AddItem that can be
useful in this case.
 
Top