ComboBox Filter for multi-value field

[cPineda]

I have a simple database for Cars, Color and Owner with the following fields -

ListCar

• CarID (autonumber)
• Brand (short text)
• Owner (number, looks up to Owner Table, allow for multiple selection)
• Color (number, looks up to Color Table, single selection)

ListOwner

• OwnerID (autonumber)
• Owner (short text)

ListColor

• ColorID (autonumber)
• Color (short text)

I created a search form (results displayed as a subform) with 2 combo boxes - Color/cboColor and Owner/cboOwner (code attached). Both combo boxes show the correct Owner and Color entries (look up to Owner and Color tables). When I choose a color, I get the correct results to display. But when I choose an owner, I see an empty subform. There's no difference in how the codes were written up except for the fact that multiple selection is allowed for the Owner.

I am using the 'BROWSE-TO' function from FormTempListCar which is executed from the Navigation Form - FormMain.

The Macro for the Color Filter After Update (within FormTempListCar) is -
BrowseTo
Object Type Form
Object Name FormDSListCar
Path to Subform Control FormMain.navigationsubform>FormTempListCar.DS
Where Condition = ="[Color]=" &[cboColor]
Page
Data Mode Edit

The Macro for the Owner Filter After Update (within FormTempListCar) is -
BrowseTo
Object Type Form
Object Name FormDSListCar
Path to Subform Control FormMain.navigationsubform>FormTempListCar.DS
Where Condition = ="[Owner]=" &[cboOwner]
Page
Data Mode Edit

FormDSListCar is just a datasheet listing the contents of ListCar.

To verify that data is structured properly, I ran a query for a particular Owner, and I am able to get the correct results. The query is -
SELECT ListCar.[CarID], ListCar.[Brand], ListCar.[Owner], ListCar.[Owner]. [Value], ListCar.[Color], ListCar.[New] FROM ListCar WHERE (((ListCar.[Owner].[Value])=1));
In the WHERE clause of the query, OwnerID 1 has been setup to own 2 cars (where 1 of those cars has 2 selected owners, OwnerID 1 and 2).

Should the filter for cboOwner be written differently? Or does this kind of code just not work for a field with multiple selection?

This is a simplified version of the actual database that I need to build, but will leverage this feature repeatedly (filter for a specific characteristic on a field that should allow for multiple selection).

Thank you very much!

 

[CJ_London]

multiple selection has its limitations and should not be used in this instance anyway. It is intended to be used for a relatively short and stable list - e.g. days of week, months of year, shift patterns, colours etc. Using it for owners will soon become a problem when you have a large number of them.

Instead you should have a join table to link owners to cars - one owner can have many cars, one car can have many owners. This would be completed on a subform to a main form for either car or owner. I would imagine you also need to consider when an owner owns a car and then ceases to own it - not possible with a multiselect field.

Regret I don't use macros so cannot help with this part.

 

[arnelgp]

I am not sure about macro, but in Vba you will use Owner.Value to compare with.