Combo Box - Limit to List
Combo Box - Limit to List
(OP)
I am setting up a form with a combo box for user selection. The source of the combo box is a table using the key field (number, long) and description. The key field is not displayed in the combo but is bound to a field on the form.
If the item the user enters is not in the list, the new information must be stored in a separate table before being added to the combo source table. I wrote code for the OnNotInList event of the combo box which opens an input form to record the information for the new item.
All this works fine except that I continue to get the message:
“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.”
How do I disable this message?
I thought that changing the LimitToList property of the combo box to No might resolve it, but when I tried I received the message:
"Microsoft Access can’t set the LimitToList Property to No right now.
The first visible column, which is determined by the ColumnWidths property, isn’t equal to the bound column.
Adjust the ColumnWidths property first and then set the LimitToList property.”
If I understand the Help on this it has to do with where Access will store the info entered into the combo box. However, I’m not sure what to do to resolve the problem.
Any help will be greatly appreciated. Thanks.
Larry De Laruelle
larry1de@yahoo.com
If the item the user enters is not in the list, the new information must be stored in a separate table before being added to the combo source table. I wrote code for the OnNotInList event of the combo box which opens an input form to record the information for the new item.
All this works fine except that I continue to get the message:
“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.”
How do I disable this message?
I thought that changing the LimitToList property of the combo box to No might resolve it, but when I tried I received the message:
"Microsoft Access can’t set the LimitToList Property to No right now.
The first visible column, which is determined by the ColumnWidths property, isn’t equal to the bound column.
Adjust the ColumnWidths property first and then set the LimitToList property.”
If I understand the Help on this it has to do with where Access will store the info entered into the combo box. However, I’m not sure what to do to resolve the problem.
Any help will be greatly appreciated. Thanks.
Larry De Laruelle
larry1de@yahoo.com
Talk To Other Members
RE: Combo Box - Limit to List
cboSC1 is the name of your combo box
make sure you change the names of the table and field that the script updates.
Basically, when a user types in a value that is not in the list, a message box pops up to let the user add the value to the table the combo box is built on. User choses yes, and then the value is added.
Private Sub cboSC1_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As Recordset
Dim db As Database
strMsg = "'" & NewData & "' is not in list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Service Code") Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("service_code_lup")
rst.AddNew
rst!SERVICE_CODE = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If
End Sub
This VBA code is great for a table with a single field. My table has a service code and then a description. So, I am trying to do the same thing as yourself to be able to build an input form and then add that description to my table in the second field. The description field is the bound field to the combo box. So when the service code value is updated, I get this message.
“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 then see an empty space where my description value should be.
This code should help, but will not solve our problem. My code in my input form looks like this:
Private Sub cmdAddDesc_Click()
Dim stDesc As String
If txtDesc.Text = "" Then
MsgBox "Please Enter a Description", vbExclamation, "Oops!"
GoTo LastLine:
End If
If txtDesc.Text <> "" Then
stDesc = txtDesc.Text
End If
LastLine:
With txtDesc
.Text = ""
.SetFocus
End With
End Sub
The problem with this is that I do not know how to dimension the stDesc string variable to have my sub procedure in another form be able to use the value captured. If you have any answers or tips, I would greatly appreciate it.
Todd
RE: Combo Box - Limit to List
Private Sub cboSC1_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As Recordset
Dim db As Database
strMsg = "'" & NewData & "' is not in list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Service Code") Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourTableNameHere")
rst.AddNew
rst!FieldNameToUpdateHere = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If
End Sub
Basically, when a user types in a value that is not in the list, a message box pops up to let the user add or not to add the value to the table the combo box is built on. User choses yes, and then the value is added.
This VBA code is great for a table with a single field only. My table has a service code and then a description field. So, I am trying to do the same thing as yourself to be able to build an input form and then add that description to my table in the second field. The description field is the bound field to the combo box. So when the service code value is updated, I get this message.
“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 then see an empty space where my description value should be.
This code should help, but will not solve our problem.
My code in my input form looks like this:
Private Sub cmdAddDesc_Click()
Dim stDesc As String
If txtDesc.Text = "" Then
MsgBox "Please Enter a Description", vbExclamation, "Oops!"
GoTo LastLine:
End If
If txtDesc.Text <> "" Then
stDesc = txtDesc.Text
End If
LastLine:
With txtDesc
.Text = ""
.SetFocus
End With
End Sub
The problem with this is that I do not know how to dimension the stDesc string variable to have my sub procedure in another form be able to use the value captured. If you have any answers or tips, I would greatly appreciate it.
Todd