Filtering Multiple Fileds

[cruzpatas]

I have a database that is set up to categorize media (ex music and movies) by theme. I have a field Theme which is a drop down. Because each song or movie can have more than one theme, I have 6 separate fields with Theme 1, Theme 2 etc... I have created a Form to filter the Query using an unbound dropdown. I want the filter the query looking in all six of the Theme fields. As of now I can make it filter in just one by putting [form]!frmtheme]![cbotheme], but to put this in the other fields criteria, it comes back as null because the multiple criteria cancel each other out.

2nd question- Is there a way to use an or statement to look up multiple themes at a time?

Thanks, in advance for your help!!!

 

[boblarson]

The problem is that you should not have set it up with fields like Theme1, Theme2, etc.

You should have a JUNCTION table to handle the one-to-many relationship and then use a Subform for entering the themes.

 

[cruzpatas]

I have three tables as of now and am creating a Junction table. Categories & Themes which are going to be a part of the Media Table. I have created a 4th which has the fields ThemeID, CatID and the MediaID. My question is how exactly is the subform going to work? I take it that I am going to put it on the Input form for creating new records for Media? I am reading the PDF http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf that you suggested to someone else, but am not getting anywhere.

 

[boblarson]

See if this sample helps you see how a subform works.

 

[cruzpatas]

It works great. Thanks. Now I am off to find a way to make my unboud combo filter the form.

 

[cruzpatas]

I am using 2007 and I can't get any of the code to work to filter the Form. I have tried several examples in the Forum and nothing happens. Even if I have the Combo as Visible = False it is still there. Anyone have an idea why?