NotInList is not working!

[jv]

Here is my code for a combobox Not in List (to add a new
record if the applicant's name is not there):

Private Sub Combo56_NotInList(NewData As String, Response
As Integer)
Dim Db As Database
Dim rs As DAO.Recordset
Dim Msg As String


Msg = "'" & NewData & "' is not in the list." &
vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Response = acDataErrContinue

Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblApplicant",
dbOpenDynaset)

rs.AddNew
rs![Applicant1Name] = NewData
rs.Update
Response = acDataErrAdded

End If

End Sub

Answering "no" to adding a new name works but
answering "yes" results in the message: The text you
entered is not an item in the list.

The Limit to List is set to Yes for the combo box.

 

[Wayne Morgan]

Try getting rid of the second "Response = acDataErrContinue" line (the one
after the Else), you need acDataErrAdded for this, which you have a little
further down. Also, don't forget to close your recordset and set the object
variables to nothing.

......
rs.Close
Set rs = Nothing
Set Db = Nothing
End If

 

[jv]

Thanks, did all of that but still nothing happens...I
still get the message: The text you entered is not an
item in the list. Any other ideas....please help!

-----Original Message-----
Try getting rid of the second "Response =

acDataErrContinue" line (the one

after the Else), you need acDataErrAdded for this, which you have a little
further down. Also, don't forget to close your recordset and set the object
variables to nothing.

......
rs.Close
Set rs = Nothing
Set Db = Nothing
End If

--
Wayne Morgan
Microsoft Access MVP

Here is my code for a combobox Not in List (to add a new
record if the applicant's name is not there):

Private Sub Combo56_NotInList(NewData As String, Response
As Integer)
Dim Db As Database
Dim rs As DAO.Recordset
Dim Msg As String


Msg = "'" & NewData & "' is not in the list." &
vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Response = acDataErrContinue

Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblApplicant",
dbOpenDynaset)

rs.AddNew
rs![Applicant1Name] = NewData
rs.Update
Response = acDataErrAdded

End If

End Sub

Answering "no" to adding a new name works but
answering "yes" results in the message: The text you
entered is not an item in the list.

The Limit to List is set to Yes for the combo box.

.

 

[Wayne Morgan]

In that case, check for typos and verify that you are adding the data to the
correct table for the combo box's Row Source. If you look at the table, does
the new item show up? What other fields are in the table? What is the combo
box's Row Source? If the Row Source is a query, what is the SQL of the
query?

--
Wayne Morgan
Microsoft Access MVP

Thanks, did all of that but still nothing happens...I
still get the message: The text you entered is not an
item in the list. Any other ideas....please help!

-----Original Message-----
Try getting rid of the second "Response =

acDataErrContinue" line (the one

after the Else), you need acDataErrAdded for this, which you have a little
further down. Also, don't forget to close your recordset and set the object
variables to nothing.

......
rs.Close
Set rs = Nothing
Set Db = Nothing
End If

--
Wayne Morgan
Microsoft Access MVP

Here is my code for a combobox Not in List (to add a new
record if the applicant's name is not there):

Private Sub Combo56_NotInList(NewData As String, Response
As Integer)
Dim Db As Database
Dim rs As DAO.Recordset
Dim Msg As String


Msg = "'" & NewData & "' is not in the list." &
vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Response = acDataErrContinue

Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblApplicant",
dbOpenDynaset)

rs.AddNew
rs![Applicant1Name] = NewData
rs.Update
Response = acDataErrAdded

End If

End Sub

Answering "no" to adding a new name works but
answering "yes" results in the message: The text you
entered is not an item in the list.

The Limit to List is set to Yes for the combo box.

.

 

[jv]

O.k. I was not adding to the table - that caused the
error; not in list. However, when it accepts a new name
now to be added to the form, it only adds the name to the
combo box and not to the form? When I look at the table
it has the Applicant1Name filled with the name I entered
in the combo box. How can I add a new record if the name
is not in the combo box? Your help is very much
appreciated!

-----Original Message-----
In that case, check for typos and verify that you are adding the data to the
correct table for the combo box's Row Source. If you look at the table, does
the new item show up? What other fields are in the table? What is the combo
box's Row Source? If the Row Source is a query, what is the SQL of the
query?

--
Wayne Morgan
Microsoft Access MVP

Thanks, did all of that but still nothing happens...I
still get the message: The text you entered is not an
item in the list. Any other ideas....please help!

-----Original Message-----
Try getting rid of the second "Response =

acDataErrContinue" line (the one

after the Else), you need acDataErrAdded for this,

which
you have a little

further down. Also, don't forget to close your

recordset
and set the object

variables to nothing.

......
rs.Close
Set rs = Nothing
Set Db = Nothing
End If

--
Wayne Morgan
Microsoft Access MVP


Here is my code for a combobox Not in List (to add a new
record if the applicant's name is not there):

Private Sub Combo56_NotInList(NewData As String, Response
As Integer)
Dim Db As Database
Dim rs As DAO.Recordset
Dim Msg As String


Msg = "'" & NewData & "' is not in the list." &
vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Response = acDataErrContinue

Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblApplicant",
dbOpenDynaset)

rs.AddNew
rs![Applicant1Name] = NewData
rs.Update
Response = acDataErrAdded

End If

End Sub

Answering "no" to adding a new name works but
answering "yes" results in the message: The text you
entered is not an item in the list.

The Limit to List is set to Yes for the combo box.


.

.

 

[Wayne Morgan]

Normally, the table the combo box pulls from is not the table the form is
using. To add a new record to the form you would click the new record button
on the record selectors at the bottom of the form. To move to a new record
using code you would use

DoCmd.GoToRecord acNewRec

You could place this in the part of the If statement that adds the new name
and your form would move to a new record.