Add item to Combobox

G

Guest

Hi, I am a newbie to access. I have a combox that is that is bound to an
employee table. I have a another table called supervisor, it has supervisor
last name and autonumber

When the combo is clicked on it has a possible of supervisors. I just want
it to also add a supervisor name into the list and table. I looked online and
found this code:

Private Sub combo1_NotInList(NewData As String, _
Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo1_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new requester.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a requester, setthe Response
' argument to suppress an error message and undochanges.Response =
acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new requester, open aRecordset
' using the CHANGE#LOG table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Supervisor", dbOpenDynaset)
' Assign the NewData argument to the REQUESTERfield.
Rs![CHANGE#LOG] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new datais being added.
Response = acDataErrAdded

End If

Exit_REQUESTER_NotInList:
Exit Sub
Err_REQUESTER_NotInList:
' An unexpected error occurred, display the normalerror message.
MsgBox Err.Description
' Set the Response argument to suppress an errormessage and undo
' changes.
Response = acDataErrContinue

End Sub

When I enter new data it does not input it in. Any help would be appreciated.
 
K

Ken Snell [MVP]

Did you set OnNotInList property of the combo box control to [Event
Procedure], and then did you put the code steps in the procedure that is
called bythe NotInList event?

Also, is the Limit To List property of the combo box set to No?

Also, is the field to which you want to add this new item the primary key
for the table? If not, is the table's primary key an autonumber field?

Also (has nothing to do with the code not "working"), change this line of
code:
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr

to this:
Msg = "'" & NewData & "' is not in the list." & vbCrLf & vbCrLf

--

Ken Snell
<MS ACCESS MVP>

Bulent said:
Hi, I am a newbie to access. I have a combox that is that is bound to an
employee table. I have a another table called supervisor, it has supervisor
last name and autonumber

When the combo is clicked on it has a possible of supervisors. I just want
it to also add a supervisor name into the list and table. I looked online and
found this code:

Private Sub combo1_NotInList(NewData As String, _
Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo1_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new requester.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a requester, setthe Response
' argument to suppress an error message and undochanges.Response =
acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new requester, open aRecordset
' using the CHANGE#LOG table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Supervisor", dbOpenDynaset)
' Assign the NewData argument to the REQUESTERfield.
Rs![CHANGE#LOG] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new datais being added.
Response = acDataErrAdded

End If

Exit_REQUESTER_NotInList:
Exit Sub
Err_REQUESTER_NotInList:
' An unexpected error occurred, display the normalerror message.
MsgBox Err.Description
' Set the Response argument to suppress an errormessage and undo
' changes.
Response = acDataErrContinue

End Sub

When I enter new data it does not input it in. Any help would be
Click to expand...
appreciated.
 
G

Guest

Thanks for the quick reply Ken.

The "Event Procedure" is set on the "OnNotInList", the Limit To List was set
to "YES" and I did change that to "NO". The table that contains the data has
an "autonumber".

When I changed the "LimitToList" to "NO", I noticed that If I have the
data:1,2,3 and I type in 4, The 1 is replaced with 4.

I Set the stops but now it seems as if it isn't even accessing the code.

ControlSource: Supervisor
SELECT Lname.Supervisor FROM Supervisor;
Column Count: 1
Bound Column: 1
Limit to list: No

Ken Snell said:
Did you set OnNotInList property of the combo box control to [Event
Procedure], and then did you put the code steps in the procedure that is
called bythe NotInList event?

Also, is the Limit To List property of the combo box set to No?

Also, is the field to which you want to add this new item the primary key
for the table? If not, is the table's primary key an autonumber field?

Also (has nothing to do with the code not "working"), change this line of
code:
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr

to this:
Msg = "'" & NewData & "' is not in the list." & vbCrLf & vbCrLf

--

Ken Snell
<MS ACCESS MVP>

Bulent said:
Hi, I am a newbie to access. I have a combox that is that is bound to an
employee table. I have a another table called supervisor, it has supervisor
last name and autonumber

When the combo is clicked on it has a possible of supervisors. I just want
it to also add a supervisor name into the list and table. I looked online and
found this code:

Private Sub combo1_NotInList(NewData As String, _
Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo1_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new requester.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a requester, setthe Response
' argument to suppress an error message and undochanges.Response =
acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new requester, open aRecordset
' using the CHANGE#LOG table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Supervisor", dbOpenDynaset)
' Assign the NewData argument to the REQUESTERfield.
Rs![CHANGE#LOG] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new datais being added.
Response = acDataErrAdded

End If

Exit_REQUESTER_NotInList:
Exit Sub
Err_REQUESTER_NotInList:
' An unexpected error occurred, display the normalerror message.
MsgBox Err.Description
' Set the Response argument to suppress an errormessage and undo
' changes.
Response = acDataErrContinue

End Sub

When I enter new data it does not input it in. Any help would be
Click to expand...
appreciated.
Click to expand...
 
K

Ken Snell [MVP]

Sorry... the Limit To List property does need to be set to Yes in order for
the form to raise the NotInList event. My apology for misstyping that.

The query SQL statement that you're using for your combobox looks very
strange, however. I believe it should be this:

SELECT Supervisor.Lname FROM Supervisor


--

Ken Snell
<MS ACCESS MVP>

Bulent said:
Thanks for the quick reply Ken.

The "Event Procedure" is set on the "OnNotInList", the Limit To List was set
to "YES" and I did change that to "NO". The table that contains the data has
an "autonumber".

When I changed the "LimitToList" to "NO", I noticed that If I have the
data:1,2,3 and I type in 4, The 1 is replaced with 4.

I Set the stops but now it seems as if it isn't even accessing the code.

ControlSource: Supervisor
SELECT Lname.Supervisor FROM Supervisor;
Column Count: 1
Bound Column: 1
Limit to list: No

Ken Snell said:
Did you set OnNotInList property of the combo box control to [Event
Procedure], and then did you put the code steps in the procedure that is
called bythe NotInList event?

Also, is the Limit To List property of the combo box set to No?

Also, is the field to which you want to add this new item the primary key
for the table? If not, is the table's primary key an autonumber field?

Also (has nothing to do with the code not "working"), change this line of
code:
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr

to this:
Msg = "'" & NewData & "' is not in the list." & vbCrLf & vbCrLf

--

Ken Snell
<MS ACCESS MVP>

Bulent said:
Hi, I am a newbie to access. I have a combox that is that is bound to an
employee table. I have a another table called supervisor, it has supervisor
last name and autonumber

When the combo is clicked on it has a possible of supervisors. I just want
it to also add a supervisor name into the list and table. I looked
Click to expand...
online
and
found this code:

Private Sub combo1_NotInList(NewData As String, _
Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo1_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new requester.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a requester, setthe Response
' argument to suppress an error message and undochanges.Response =
acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new requester, open aRecordset
' using the CHANGE#LOG table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Supervisor", dbOpenDynaset)
' Assign the NewData argument to the REQUESTERfield.
Rs![CHANGE#LOG] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new datais being added.
Response = acDataErrAdded

End If

Exit_REQUESTER_NotInList:
Exit Sub
Err_REQUESTER_NotInList:
' An unexpected error occurred, display the normalerror message.
MsgBox Err.Description
' Set the Response argument to suppress an errormessage and undo
' changes.
Response = acDataErrContinue

End Sub

When I enter new data it does not input it in. Any help would be
Click to expand...
appreciated.
Click to expand...
Click to expand...
 
G

Guest

I stepped through the code and in this portion of the code:
Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

When I mouse over this portion: Setdb=nothing

rs![CHANGE#LOG] = NewData
Item not found message box appears on the form.

Is this something, I have done incorrectly in the form properties? If I hit
"NO" on the msgbox, if I want to add a new record, I get the message:
The text you entered is not in the item list.

Ken Snell said:
Sorry... the Limit To List property does need to be set to Yes in order for
the form to raise the NotInList event. My apology for misstyping that.

The query SQL statement that you're using for your combobox looks very
strange, however. I believe it should be this:

SELECT Supervisor.Lname FROM Supervisor


--

Ken Snell
<MS ACCESS MVP>

Bulent said:
Thanks for the quick reply Ken.

The "Event Procedure" is set on the "OnNotInList", the Limit To List was set
to "YES" and I did change that to "NO". The table that contains the data has
an "autonumber".

When I changed the "LimitToList" to "NO", I noticed that If I have the
data:1,2,3 and I type in 4, The 1 is replaced with 4.

I Set the stops but now it seems as if it isn't even accessing the code.

ControlSource: Supervisor
SELECT Lname.Supervisor FROM Supervisor;
Column Count: 1
Bound Column: 1
Limit to list: No

Ken Snell said:
Did you set OnNotInList property of the combo box control to [Event
Procedure], and then did you put the code steps in the procedure that is
called bythe NotInList event?

Also, is the Limit To List property of the combo box set to No?

Also, is the field to which you want to add this new item the primary key
for the table? If not, is the table's primary key an autonumber field?

Also (has nothing to do with the code not "working"), change this line of
code:
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr

to this:
Msg = "'" & NewData & "' is not in the list." & vbCrLf & vbCrLf

--

Ken Snell
<MS ACCESS MVP>

Hi, I am a newbie to access. I have a combox that is that is bound to an
employee table. I have a another table called supervisor, it has
supervisor
last name and autonumber

When the combo is clicked on it has a possible of supervisors. I just
want
it to also add a supervisor name into the list and table. I looked online
and
found this code:

Private Sub combo1_NotInList(NewData As String, _
Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo1_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new requester.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a requester, setthe Response
' argument to suppress an error message and undochanges.Response =
acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new requester, open aRecordset
' using the CHANGE#LOG table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Supervisor", dbOpenDynaset)
' Assign the NewData argument to the REQUESTERfield.
Rs![CHANGE#LOG] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new datais being added.
Response = acDataErrAdded

End If

Exit_REQUESTER_NotInList:
Exit Sub
Err_REQUESTER_NotInList:
' An unexpected error occurred, display the normalerror message.
MsgBox Err.Description
' Set the Response argument to suppress an errormessage and undo
' changes.
Response = acDataErrContinue

End Sub

When I enter new data it does not input it in. Any help would be
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

Sorry, my post is not directly related to the ComboBox (but it may help).

Since a Supervisor is an Employee also, I am not sure having the
tblSupervisor in addtion to the tblEmployee is the correct Table structure.
If you need to identify the Supervisor of each Employee in your database,
the correct structure is only the tblEmployee with a self-join relationship
rather than 2 Tables.

Suggest you check out the Relational Database Design Theory and see which
way you should go. Nore that 2 Tables may be the correct structure
depending on what you want the database to do (which I don't know) and you
are in the best position to decide which structure is the correct one.

--
HTH
Van T. Dinh
MVP (Access)





Bulent said:
Hi, I am a newbie to access. I have a combox that is that is bound to an
employee table. I have a another table called supervisor, it has supervisor
last name and autonumber

When the combo is clicked on it has a possible of supervisors. I just want
it to also add a supervisor name into the list and table. I looked online and
found this code:

Private Sub combo1_NotInList(NewData As String, _
Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo1_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new requester.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a requester, setthe Response
' argument to suppress an error message and undochanges.Response =
acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new requester, open aRecordset
' using the CHANGE#LOG table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Supervisor", dbOpenDynaset)
' Assign the NewData argument to the REQUESTERfield.
Rs![CHANGE#LOG] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new datais being added.
Response = acDataErrAdded

End If

Exit_REQUESTER_NotInList:
Exit Sub
Err_REQUESTER_NotInList:
' An unexpected error occurred, display the normalerror message.
MsgBox Err.Description
' Set the Response argument to suppress an errormessage and undo
' changes.
Response = acDataErrContinue

End Sub

When I enter new data it does not input it in. Any help would be
Click to expand...
appreciated.
 
K

Ken Snell [MVP]

Do you have a field named CHANGE#LOG in the table Supervisors? Based on what
you say the row source is for the combo box, I'm guessing it should be LName
field.

--

Ken Snell
<MS ACCESS MVP>

bulent said:
I stepped through the code and in this portion of the code:
Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

When I mouse over this portion: Setdb=nothing

rs![CHANGE#LOG] = NewData
Item not found message box appears on the form.

Is this something, I have done incorrectly in the form properties? If I hit
"NO" on the msgbox, if I want to add a new record, I get the message:
The text you entered is not in the item list.

Ken Snell said:
Sorry... the Limit To List property does need to be set to Yes in order for
the form to raise the NotInList event. My apology for misstyping that.

The query SQL statement that you're using for your combobox looks very
strange, however. I believe it should be this:

SELECT Supervisor.Lname FROM Supervisor


--

Ken Snell
<MS ACCESS MVP>

Bulent said:
Thanks for the quick reply Ken.

The "Event Procedure" is set on the "OnNotInList", the Limit To List
Click to expand...
was
set
to "YES" and I did change that to "NO". The table that contains the
Click to expand...
data
has
an "autonumber".

When I changed the "LimitToList" to "NO", I noticed that If I have the
data:1,2,3 and I type in 4, The 1 is replaced with 4.

I Set the stops but now it seems as if it isn't even accessing the code.

ControlSource: Supervisor
SELECT Lname.Supervisor FROM Supervisor;
Column Count: 1
Bound Column: 1
Limit to list: No

:

Did you set OnNotInList property of the combo box control to [Event
Procedure], and then did you put the code steps in the procedure that is
called bythe NotInList event?

Also, is the Limit To List property of the combo box set to No?

Also, is the field to which you want to add this new item the
Click to expand...
primary
key
for the table? If not, is the table's primary key an autonumber field?

Also (has nothing to do with the code not "working"), change this
Click to expand...
line
of
code:
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr

to this:
Msg = "'" & NewData & "' is not in the list." & vbCrLf & vbCrLf

--

Ken Snell
<MS ACCESS MVP>

Hi, I am a newbie to access. I have a combox that is that is bound
Click to expand...
to
an
employee table. I have a another table called supervisor, it has
supervisor
last name and autonumber

When the combo is clicked on it has a possible of supervisors. I just
want
it to also add a supervisor name into the list and table. I looked online
and
found this code:

Private Sub combo1_NotInList(NewData As String, _
Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo1_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new requester.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a requester, setthe Response
' argument to suppress an error message and undochanges.Response =
acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new requester, open aRecordset
' using the CHANGE#LOG table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Supervisor", dbOpenDynaset)
' Assign the NewData argument to the REQUESTERfield.
Rs![CHANGE#LOG] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new datais being added.
Response = acDataErrAdded

End If

Exit_REQUESTER_NotInList:
Exit Sub
Err_REQUESTER_NotInList:
' An unexpected error occurred, display the normalerror message.
MsgBox Err.Description
' Set the Response argument to suppress an errormessage and undo
' changes.
Response = acDataErrContinue

End Sub

When I enter new data it does not input it in. Any help would be
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I agree Van this isn't for a "Company" I am learning Acces and I took NWind
and wanted to create input through a combobox. I basically, created a sup
table iconjunction to the employees table. I can not figure out way, I keep
getting a
"The text you entered isn't an item in the List"
When it seems that I have everything setup correctly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Not in the list (combobox) 1
item not found in this collection 1
NotInList updated but not shown on form until close and reopen 12
ComboBox issue 4
NotInList adding new entry as a new record each time 3
No In List Event 3
NotInList ComboBox Problem 5
Adding info through combo boxes 6

Top