×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combo Box - Limit to List

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
 
 

RE: Combo Box - Limit to List

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") 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

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 = "'" & 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close