How to add a new item to a combo box field

[Guest]

Hi All,

I have recently been working on developing a form that will perform an auto
fill when a value in a form combo box is selected. It is working great
except for one small problem:

If I have new information to enter that is not a value in the combo box, the
form will not allow me to enter a new value.

I work in video teleconferencing and we constantly have requests to connect
to both old rooms (already values in the combo box), as well as new rooms
(which I would like to enter). How do I set up my database to permit me to
add new values to the combo box?

I keep getting the following error when I try to enter a new Room
Name/Number into the Room Name/Number combo box:

"The Microsoft Jet database engine cannot find a record in the table
'tblConstants' with key matching field(s) 'Room Name/Number'."

All help is appreciated.

Sean

 

[George Nicholson]

http://support.microsoft.com/kb/197526/en-us
Use NotInList event to add a record to a combobox

2 methods are described.

HTH,

 

[Guest]

Hi George,

I appreciate you guiding me to that information. I tried molding it to my
situation, but am getting the following error when I try to input a new room
name:

"The text you entered isn't an item in the list. Select an item from the
list or enter text that matches one of the listed items."

I have 1 form used for entering data. I have 2 tables -- tblConstants and
tblVariables (the naming convention is just for clarity while developing).

Fields in each table:

tblConstants --
RoomNameNumber (primary key)
Contact Info
Contact Phone Number
etc...

tblVariables --
Room ID (primary key - autonumber)
RoomNameNumber (foreign key)
Remarks
Date

I have a one-to-many relationship established between RoomNameNumber in both
tblConstants and tblVariables. The form is based on a query (qryVariables)
that gathers all info from both tables.

Based on the tutorial you sent me, I set the Limit to List property to "Yes"
for my RoomNameNumber combo box. I then changed to code to fit my situation,
but am certain I have made some mistakes, as I am a total novice w/ code.

Here is the code I used for OnNotInList:

Private Sub RoomNameNumber_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_RoomNameNumber_NotInList

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

' Confirm that the user wants to add the new room.
Msg = "'" & NewID & "' 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 room, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the tblConstants table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblConstants", dbOpenDynaset)

' Ask the user to input a new room.
Msg = "Please enter the new room."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("RoomNameNumber", dbText, NewID)
' If the NewID already exists, ask for another new unique
' RoomNameNumber
Do Until Rs.NoMatch
NewID = InputBox("Room Name/Number " & NewID & " already exists.")
Rs.FindFirst BuildCriteria("RoomNameNumber", dbText, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the RoomNameNumber field.
Rs![CustomerID] = NewID
' Save the record.
Rs.Update

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

End If

Exit_RoomNameNumber_NotInList:
Exit Sub
Err_RoomNameNumber_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub

Would you please be so kind as to help me out with this code? Thank you.

Sean

 

[George Nicholson]

Suggestions:
1) If RoomNameNumber is a number *field*, try changing the code to:
Rs.FindFirst BuildCriteria("RoomNameNumber", dbLong, NewID)
(Note that this line appears twice, so change both)
2) I'm thinking that you probably want "Rs![CustomerID] = NewID" to be:
Rs![RoomNameNumber] = NewID

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi George,

I appreciate you guiding me to that information. I tried molding it to my
situation, but am getting the following error when I try to input a new
room
name:

"The text you entered isn't an item in the list. Select an item from the
list or enter text that matches one of the listed items."

I have 1 form used for entering data. I have 2 tables -- tblConstants and
tblVariables (the naming convention is just for clarity while developing).

Fields in each table:

tblConstants --
RoomNameNumber (primary key)
Contact Info
Contact Phone Number
etc...

tblVariables --
Room ID (primary key - autonumber)
RoomNameNumber (foreign key)
Remarks
Date

I have a one-to-many relationship established between RoomNameNumber in
both
tblConstants and tblVariables. The form is based on a query
(qryVariables)
that gathers all info from both tables.

Based on the tutorial you sent me, I set the Limit to List property to
"Yes"
for my RoomNameNumber combo box. I then changed to code to fit my
situation,
but am certain I have made some mistakes, as I am a total novice w/ code.

Here is the code I used for OnNotInList:

Private Sub RoomNameNumber_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_RoomNameNumber_NotInList

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

' Confirm that the user wants to add the new room.
Msg = "'" & NewID & "' 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 room, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the tblConstants table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblConstants", dbOpenDynaset)

' Ask the user to input a new room.
Msg = "Please enter the new room."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("RoomNameNumber", dbText, NewID)
' If the NewID already exists, ask for another new unique
' RoomNameNumber
Do Until Rs.NoMatch
NewID = InputBox("Room Name/Number " & NewID & " already
exists.")
Rs.FindFirst BuildCriteria("RoomNameNumber", dbText, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the RoomNameNumber field.
Rs![CustomerID] = NewID
' Save the record.
Rs.Update

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

End If

Exit_RoomNameNumber_NotInList:
Exit Sub
Err_RoomNameNumber_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub

Would you please be so kind as to help me out with this code? Thank you.

Sean

http://support.microsoft.com/kb/197526/en-us
Use NotInList event to add a record to a combobox

2 methods are described.

HTH,