Filter combo boxes in continuous form

[Bob 4]

I have a continuous form used in a tour planning process that lists the
vendors we plan to use for the tour such as resaurants, attractions and
hotels. The columns are VendorType and VendorName and both are combo boxes.
If I select a VendorType=Restaurant then I want to see just the list of
restaurant vendors in the VendorName combo box. If I select
VendorType=Restaurant I can use the VendorType.AfterUpdate event to update
the VendorNames.Rowsource to just restaurants. However, when I move to a new
record in the continuous form and select VendorType=Hotel and
VendorType.AfterUpdate event updates the VendorName.Rowsourse to just hotels.
My challenge is that after I requery the VendorName the continuos form does
not show the VendorName in the first record because its VendorType is
restaurant. I would appreciate any guidance.

 

[Rick Brandt]

I have a continuous form used in a tour planning process that lists
the vendors we plan to use for the tour such as resaurants,
attractions and hotels. The columns are VendorType and VendorName
and both are combo boxes. If I select a VendorType=Restaurant then I
want to see just the list of restaurant vendors in the VendorName
combo box. If I select VendorType=Restaurant I can use the
VendorType.AfterUpdate event to update the VendorNames.Rowsource to
just restaurants. However, when I move to a new record in the
continuous form and select VendorType=Hotel and
VendorType.AfterUpdate event updates the VendorName.Rowsourse to just
hotels. My challenge is that after I requery the VendorName the
continuos form does not show the VendorName in the first record
because its VendorType is restaurant. I would appreciate any
guidance.

If you use a dynamic (cascading) ComboBox List and that ComboBox is also set up
to store one thing while displaying another (typically storing an ID number
value while displaying a text value) then you will have this problem.

The text you see in the ComboBox only exists as the result of the RowSource
query, The only data that is really there locally in your form is the ID
number. When you alter the RowSource to return a row set that no longer
includes the text value for a given ID value then Access has no way to show it.

This combination (dynamic row sources and displaying a column other than the
bound column) is really just not a good one for a continuous form. Work-arounds
exist though. One is to place a TextBox directly on top of the ComboBox so it
covers everything but the drop arrow. In that TextBox you can use a DLookup()
function that retrieves and displays the missing text in the ComboBox.

 

[Bob 4]

If you use a dynamic (cascading) ComboBox List and that ComboBox is also set up
to store one thing while displaying another (typically storing an ID number
value while displaying a text value) then you will have this problem.

The text you see in the ComboBox only exists as the result of the RowSource
query, The only data that is really there locally in your form is the ID
number. When you alter the RowSource to return a row set that no longer
includes the text value for a given ID value then Access has no way to show it.

This combination (dynamic row sources and displaying a column other than the
bound column) is really just not a good one for a continuous form. Work-arounds
exist though. One is to place a TextBox directly on top of the ComboBox so it
covers everything but the drop arrow. In that TextBox you can use a DLookup()
function that retrieves and displays the missing text in the ComboBox.

Thanks Rick. I'll solve this problem by dropping the requirement to filter
the VendorName combo. bob