Combo box-Not in list

[Srowe]

I have a Combo box setup to Display last names of People. What I am having
trouble with is everytime I try to add a name that isn't in the combo box
list I get an error saying that the nname is not in the list. Can someone
help?

Scott

 

[Wayne-I-M]

It depends on why you are entering the names on/in to the combo

The simple method to allow this would be to open the form in design view and
then the properties for the combo and in the data column select Limit To List
= No

But....

Why are you doing this. Are you wanting to add to the available list (in
the combo) for future use ? If this is the case you will need to add the Name
you are typeing in to the record source for the combo.
A good method of doing this is shown here

http://support.microsoft.com/kb/824176/en-us

Hope this helps

 

[Ken Sheridan]

Scott:

Does the 'People' table from which the names are obtained contain other
fields such as FirstName and address data for instance? If so then to add a
new name via the combo box you'll need to use the combo box's NotInList event
procedure to open another form bound to the People table so that you can add
the other data before updating the combo box's list. Here's some code for
the NotinList event procedure of a cities combo box which does this. You
should be able to adapt it easily enough for people:

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

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In addition to the above code the following code goes into the frmCities
form's Open event procedure to set the DefaultValue property of the City
control to the new name entered in the combo box. Again you should be able
to adapt this easily enough:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Ken Sheridan
Stafford, England

 

[Srowe]

Thanks for the info Wayne.

Bookmarked that site for future use.

 

[Srowe]

Thanks Ken.

Yes there are several other fields that are on the form. If I understand it
right based on your info a new form will open, named "Add subject", if the
data entered in the combo box is not recognized. At which time you will be
able to enter it into the new form.

Scott

 

[Ken Sheridan]

Scott:

That's right. Taking my code as an example, firstly the user is asked to
confirm the new item with:

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

If they answer yes then the form is opened with:

DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData

This is executed as a single line as the underscore character at the end of
each line is a continuation character and indicates that the next line is
part of the same line of code; this makes the code more readable. This line
opens the form at a new record and in dialogue mode, which means the rest of
the code in the NotInList event procedure does not execute until the
frmCities form has been closed (strictly speaking its until closed or
hidden). It also passes the new item entered into the combo box to the
frmCities form as its OpenArgs property. The code in the frmCities form's
Open event procedure then uses this to set the DefaultValue property of the
City control on the form to the new value so you don't have to type it in
again.

After the user has entered the rest of the data into frmCities and closed
it the code resumes at:

If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then

This checks to see if the user did add the new record in frmCities. If they
did then it sets the return value of the Response argument to the built in
constant acDataErrAdded, which requeries the combo box so it shows the new
item in its list.

If the user has changed their mind and not added the new city then it sets
the return value of the Response argument to the built in constant
acDataErrContinue which tells Access to carry on regardless of the item not
being in the list. The code removes the new item from the combo box by
undoing it with:

ctrl.Undo

If the user answered no to the original confirmation message box the return
value of the Response argument is also set to the built in constant
acDataErrContinue and the control is undone.

Ken Sheridan
Stafford, England

 

[Srowe]

Sorry Ken,

Your going to have to excuse my slowness in picking this up. I'll just
backtrack a bit. I have a Subject Input Form on that form I want to have a
combo box that allows me to enter new names and store them in the Subject
table. On my form I have Surname (This is the field that I want the combo box
on), first name, middle name etc...

What I am having difficulty with is adapting the code you provided me to my
form. Also, do I have to create a new form that the combo box refers to to
add a new person.

I applaude your patience with me.

Eventually what I want to happen is to have the persons info fill in the
fields once you pick a surname. Thus saving on input time. One step at a time
though.

Thanks Again,

Scott

 

[Srowe]

And to clarify a little more.

frmcities=frmaddnewsubject (form)

the specific line I am having problems with is the line "If Not
IsNull(DLookup("CityID...etc.... trying to figure out how my fields,
subjectid, surname...etc would relate to yours.

Many Thanks.

Scott