Maintaining Combobox (1 Viewer)

[NewShoes]

Hey all,

When using combo boxes, I tend to use a query as their row source. In an effort to maintain certain comboboxes and stop them from becoming too long, I use a check box to exclude values that are no longer needed.

For example, if I have a combobox on a form that lists 'Years', as the yeas go on, this list will increase. If I want to stop Years that are no longer needed for inputting purposes from showing, I can go to the Years form (or table) and tick a box next to a particular year. Due to me setting the criteria of the combo box to exlcude any where this check box is ticked, these will no longer show.

However, and here is my issue, once I have done this, if I look back over historical records, the Year I've previously selected will now be missing.

Is this the correct way of maintaining combo's that have a query as their row source?

Any help or nudge in the right direction would be awesome!

-NS

 

[Anakardian]

Hi NewShoes

While I am not able tooffer anything on the actual mechincs of the combobox I know I would make it a bit differently if I could.

While I agree that removing "old" years is a good thing, perhaps you could do this in the query by setting the oldest year to current year minus something.
Provide a tickbox next to the combobox to toggle between all records and the limited list.
I think this can all be done with the query you are already using.

Anakardian

 

[veraloopy]

I do a similar thing that works well for me but on the data table, do not set the row source to exclude the years without the tick boxes.
I keep the field on the data table as standard text, then use a combo box on the form to look up in exactly the same way you have done already.

 

[Pat Hartman]

In this particular case, I would sort the list descending so that the most recent years are on top rather than excluding old years.

When you change the RowSource query so that it doesn't include the entry for the current row, the field value looks "blank". The underlying value is still in the bound column, it just isn't being displayed. This is a common problem when working with "cascading" combos on a continuous form. There are several solutions to the problem. I've included a database showing what I do.

 

[NewShoes]

Thanks all for your replies.

Pat, that is a very creative solution, thank you! The only slight issue is that it does not allow the user to click into the combobox and type (thus activating the auto complete feature). It does however work when tabbing into it. I think I can live with this tho

Thanks,
-NS

 

[Pat Hartman]

It also doesn't work for datasheet view. Someday, maybe Microsoft will change the way forms work so this isn't necessary. They've come part way with Conditional Formatting which allows font and color changes and whichh works if you have only four formatting options (the default + 3 others). But the combo RowSource isn't handled by CF.