Combo Box - Limit to List

[LarryDeLaruelle]

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

 

[tmcneil]

I have a similar type of situation and believe that I can be of some help. Your LimitToList properties should be set to Yes. Which I believe you have done. Next, an event procedure in your NotInList property should look like this:

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&quot Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("service_code_lup&quot
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 <> &quot;&quot; Then
stDesc = txtDesc.Text
End If

LastLine:
With txtDesc
.Text = &quot;&quot;
.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

 

[tmcneil]

I have a similar type of situation and believe that I can be of some help. Your LimitToList properties should be set to Yes. Which I believe you have done. Next, an event procedure in your NotInList property should look like this:


Private Sub cboSC1_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As Recordset
Dim db As Database

strMsg = &quot;'&quot; & NewData & &quot;' is not in list. &quot;
strMsg = strMsg & &quot;Would you like to add it?&quot;
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
&quot;New Service Code&quot Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset(&quot;YourTableNameHere&quot
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 = &quot;&quot; Then
MsgBox &quot;Please Enter a Description&quot;, vbExclamation, &quot;Oops!&quot;
GoTo LastLine:
End If

If txtDesc.Text <> &quot;&quot; Then
stDesc = txtDesc.Text
End If

LastLine:
With txtDesc
.Text = &quot;&quot;
.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