Combo Box Blank while underlying recordset field has data

[etrain]

OK I feel like I need a refresher in Access 101 because I can not understand why something is happening.

Throughout an application I have built there are many combo boxes and these combo boxes pull from set up tables or look up tables as other call them. These were set up with Short Names, Description etc…as well as Effective Date and End Dates. This way the selections in the combo boxes could be limited to only active items. If the current date is not between these two you would not see them in the list. Sounds simple enough and this works as intended. With some additional forms built for the set up tables it allows the power users to maintain this rather than a programmer or developer.

Problem: If an item was selected last week and then today that item is inactivated. If the user pulls up the form tomorrow and goes to the record of this item the combo box is blank. The underlying recordset has the data when you look at it from a table or query view just not in the form view.

I have this scenario throughout my application so it will become a big problem very soon. Is there something I didn't do or something I should do differently to remedy this problem? What am I not understanding? I thought the recordsource just shows you what can be selected but if that changed in the case of item becoming inactive it should affect the way it is displayed on the form.

Thanks
e-train
Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain

 

[etrain]

OK I thought of one way to solve my probelm. I can set the recordsouce of the ComboBox to show all but on the click event set the criteria for the Effective Date and End dates. This might work but will take me some time in adding the code to just about every ComboBox on my slew of about 25 forms. Which means more code which increases size and potentially slows performance.

Any other suggestions besides this one. If I am just missing a concept or something please just tell me. Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain