Combo Box Not Clearing

[dgj32784]

I have a combo box on a form that is based on a query. The combo box lists the 100 counties that are in North Carolina. I also have an option group with 27 controls - one for each letter in the alphabet that when clicked, provides a parameter to the query to only show the counties in the combo box that start with that letter. The extra option (27th control), resets the combo box to all of the counties. I would like a command button that clears the selection in the combo box. I've tried the typical options with the On Click Event, including:

Me.Combobox.Value = ""
Me.Combobox.Value = Null

and several others that are out there on the web. The odd thing that I cannot figure out is that the "clear command button" is not clickable until I click the 27th control in the option group that resets the combo box to all of the counties. What's that all about? I would of course like the clear botton to work at any time.

Your thoughts are much appreciated.

Thanks,
David

 

[JamesMcS]

I think what I'd do there is have the option group update the combo box's row source, i.e. change the query. What you'll need to do is

Set the option group up so that "a" is 0, "b" is 1, etc.

Put this in the group's on click:

Dim Letter_ASCII as integer, Where_String as string

Letter_ASCII= 97+optiongroupname.value

If optiongroupname.value = 26 then
Where_String = ""
Else
Where_String = "WHERE [countyname] Like'*" & chr(Letter_ASCII) & "*'"
Endif

comboboxname.rowsource = [insert SQL here] & Where_String & ";"

Not sure if this'll work, and it's only aircode, but what I'm getting at is that we're using the value of the option group + 97 (ascii value for "a") to return the selected letter in the combo box. If the option group value is 26 (your 27th option), the where string will be null and the SQL will bring back all 100 counties. I think.....

 

[spikepl]

"Unclickable" means, in Access-speak, "Enabled=False"?

That probably means that you somewhere have some code that disables it (sets myButton.Enabled=False) either directly, or perhaps a loop over controls and this button got swept along.

 

[gemma-the-husky]

out of interest, why would you want the combo box to filter in this way - just typing the first letter will jump to the the first possible match.

filtering a combo box by its contents is pointless, I think, unless there is an excessive number of rows of data.

 

[valeryk2000]

How about that:
Me.ComboBox.RowSource =""

 

[JamesMcS]

That would make the combo box blank, not display all the countries.

 

[valeryk2000]

And that:
Dim i As Integer
For i = 0 To Me.ComboBox.ListCount - 1
If Me.ComboBox.Selected(i) Then Me.ComboBox.Selected(i) = False
Next

 

[dgj32784]

Thanks for the ideas. I'll give them a try today now that I'm back in the office. GT Husky - you're right that the combo box filter is unusual. This dbase is for conducting field surveys from a tablet PC. So any amount of records that requires using the scroll bar inside the combo box could be less user friendly on a sunny humid day in the southeastern US. While the 100 counties are not too difficult to scroll through, the 73,000 towns or 100,000+ road names is a pain. Once the county is selected from the first combo box, another combo box with a list of towns and another with a list of roads is filtered based on the county selected. Using the alphabet option group will further reduce the choices within the combo boxes, making data entry easier for the user, particularly since using the stylus to type can be a pain sometimes.

Thanks again. I'll give these a try.

David