Make Combo Box Un-Editable, Allow Only Values In Combo Box List To Be Selected

[elhale]

I've setup a DB that tracks a team's activities through statistics. The stats are entered through a form that captures the "Date" of the activity (date field), the "Team" that conducted the activity (combo box), the "Zone" the team belongs to (combo box) and the "Area" the activity was conducted in (combo box). There is also a "Division" field (text box) that is populated when the "Area" is selected. After, there are a series of stat categories (fields) where numbers are entered, followed by a narrative (long text field) for a descriptive note of the activities.

Because the initial fields (date, zone, team, area) are important to designate which team and areas the activities are associated with, the data integrity of the fields is paramount. The "Zone" combo box modifies the teams listed in the "Team" combo box once the zone is selected. from there, once the "Area" is selected, it populates the "Division" field. This keeps the data relevant to which zone, team, area and division the activities were conducted in.

We have encountered a problem in that users can manually edit the combo box fields even though I've set the properties to only accept from the list. One example is the "Zone" field can be changed to a different value after a team was selected, even though it's not correctly associated with the selected team. Another example is the "Team" or "Area" fields can be edited after selected and the change can be as simple as upper or lower case resulting in changing the report that runs from that data.

I would like to make the combo boxes uneditable, only allowing the selection of the values in the combo box list. Any help on how to accomplish this would be greatly appreciated.

[ranman256]

you cant edit a combo box. you can only edit the table where data comes from.
you can set it to limit to list = true
you can only select a value in the box.

if you want a cascading combo box to limit the items in the next box down, then
when a user selects a country in cboCountry, you refresh the next box down, cboCity

sub cboCountry_afterupdate()
if isNull(cboCountry) then
    cboCity.rowsource = "qsAllCities"
else
    cboCity.rowsource = "qsCities1Country"
end if

then when user changes the city, the afterupdate fills the next combo.

[elhale]

ranman, thanks for the reply.

I already have the combo boxes setup as cascading. When the user selects the "Zone" it limits the "Team" combo box values to only those within the selected zone. In addition, when the user selects the "Area", the afterupdate property populates the "Division" field with the selected area.

The combo boxes are set with "limit to list" property = true. Also, I understand the value cannot be edited outside the list, but for example if the list contains a value "Central" and the user edits the field to "central", the edit is allowed because it matches a value on the list. Unfortunately, when I run a report showing data grouped by "Division", the report sees the two versions of "Central" (uppercase and lowercase) as two different divisions. I also have instances where users are changing the "Zone" from a "2" to a "3" after they've selected a corresponding "Team" from zone 2.

If there was a way to only allow the users to select the values in a combo box while at the same time preventing any type of editing capability in said combo box, that would solve my problem.

I hope that made sense.

[aytee111]

Here are some suggestions:
1 - make the comboboxes unbound fields
2 - change the case in after-update so that you don't have those upper/lower case issues
3 - have the combobox row source SQL read from the screen (it looks like you are changing the SQL each time?)
4 - set all of the below comboboxes to null each time, validate that there are entries in the before-update of the form

[Bulzie]

Unless I am reading it wrong, I tested the upper/lower case in combo and if it is uppercase in combobox list and i enter it as lower case in field on form, it still saves in table as uppercase. Not sure if you have different options or something. Also doing query on the word, does not matter if I put criteria in upper or lower or mixed case, it returns all records from the spelling of the word, not the case. So again, not sure why yours is doing something different.

[elhale]

aytee111, thanks for the suggestions, I will check those out and see if they work for me.

Bulzie, the uppercase/lowercase issue comes into play with a report I generate that uses the "Division" field to group data in the report. Every division that occurs becomes a grouping and so far it has seen "Central" and "central" as two different groups with the only difference I can see is the lowercase type. I don't know if that clarifies what my situation is, hopefully it does.

[Missinglinq]

...so far it has seen "Central" and "central" as two different groups...

This is really weird because Access, by default, is Case Insensitive. There are a couple of things you can do to get it to recognize the difference be Upper and Lower Case, but it's something you have to explicitly instruct Access to do...not something that 'just happens.'

If the number of selections is small enough that scrolling down them to find the desired one is adequate, without using the AutoExpand feature, you can simply prevent anything from being entered, freehand, as it were, into the Combobox. The code is fairly simple:

Private Sub ComboBoxName_KeyDown(KeyCode As Integer, Shift As Integer)
 Select Case KeyCode
  Case vbKeyReturn, vbKeyTab, vbKeyUp, vbKeyDown
   KeyCode = KeyCode  'Accept these keys
  Case Else
   KeyCode = 0 ‘Block all other keys
 End Select
End Sub

Linq ;0)>

[elhale]

Linq, thank you! I will try the code, I believe that may be what I'm looking for. I appreciate it.