ComboBox Limit-to-List

[OldBirdman]

Is there a good way, in Access 2002, to only allow a user to pick from the dropdown list for a combobox?

Logically, this would 'Lock' the textbox portion and have the dropdown list unlocked. Many websites have this feature. Just try to enter 2.5 people into an airline or hotel reservations website.

I know about 'Limit to List', but if the RowSource = "1;2;3;4;", and the user enters 'q', no error occurs until the user navagates away from the control/page/record. Then an error message must be presented to user, and they must make another attempt. I want to stop this before it starts.

[DonRayner]

Hey OB, You could use the key up event to set the control value to ""

[OldBirdman]

Just the kind of answer I'm looking for.

I had to change to

cboTest = cboTest.Value

because bound column is not displayed column, and I don't want to lose current value. But...if there is a Menu with Edit->Paste, it is still possible to enter stuff into the textbox without pressing a key. Pasting with Cntl+V catches with the KeyUp when V is released.

Within the Access Window, if the form is Modal, then Edit->Paste doesn't work as that toolbar is not accessablle. If nothing better, I'll go with your suggestion.

Thank you.

[OldBirdman]

The Delete Key defeats this method. Select any or all of current entry, and press Delete, and an erroneous entry is present. Now I'm back with Not In List to trap.

[missinglinq]

This is easy enough to do:

Private Sub ComboBoxName_KeyDown(KeyCode As Integer, Shift As Integer)
  KeyCode = 0
End Sub

The problem is, this does away with the ability to use the AutoExpand feature of Comboboxes. When a user types in the box, Access moves to find the item that matches what's been entered, which is invaluable if you're dealing with a lot of items.

Linq ;0)>

[DonRayner]

Use the on enter event for the control to set a form variable and then use that variable in the key up event.

[OldBirdman]

Private Sub ComboBoxName_KeyDown(KeyCode As Integer, Shift As Integer) 
  KeyCode = 0 
End Sub

This does the job except when Edit->Paste is used. So I still have to have a Not In List event also:

Private Sub ComboBoxName_NotInList(NewData As String, Response As Integer)
    cboUserNow = cboUserNow.OldValue
    Response = acDataErrContinue
End Sub

The problem is, this does away with the ability to use the AutoExpand feature of Comboboxes.

I asked this question to deal with the types of cases where the list is small, often a Value List. Columns per page = 1 or 2 or 3 or 4 and I don't want a 0 or 5 or q or 2.2. Year = 2007 or 2008 or 2009, not 209 or 2010.

I will have to treat the long lists differently where AutoExpand is desired. My case for this is my list of bird species (common English names). That has its own problems in that the list is 10,000 entries and it is lumpy. There are 580 species starting with the string "white", which isn't too bad, but 66 species starting with "White-throated " so until 16th character, not unique, and could be 20 before unique. I would not want to create a new species from this list, so a Not-in-List is an error by user.