Limiting combo box choices - makes form values disappear

[Robbo1974]

Hi all,

A bit of background:

I have a db used for processing orders for hospitality at sporting events. There is a main form with two subforms, one nested in the other - the subforms hold order information, the main client details. We run around 50 events a year. As part of entering an order the user selects an event from a combo box.

Because a)Orders are inputted onto the db a minimum of five days before the event and b) in the interest of user friendliness (who wants to scroll through loads of old events to find the one they're looking for?), I want to restrict the list to events where the event date > date().

When I do this (by changing the row source criteria) the field which displays the event name on the subform blanks out for all orders for events before date() - they are still there because when I change the row source back they re-appear, but are just not displayed.

How can I prevent this happening and still restrict the combo list? Robbo ;-)

 

[hsitz]

It sounds like you may have the dual-combo setup on a continous form, where multiple records are displayed at a time. You get the blanking out because the recordsource is changed for the control on ALL the records, not just the record you're editing at the time. I think you suspected this.

There is a way to implement the second (limited) combo box on a continuous form, but it's a little more involved. I've done it by placing a text box right on top of the second combo box. Hide the combo box, and have the text box display the field value by using a dlookup function. Then have a small button to the right of the text box that makes the combo box visible and has it drop down to select a value. That is the basics of how something like this can be done. It is a little more complicated than having limited combo boxes on a form that shows only one record at a time. -- Herb