Filtering through combo-box works partially on a continuous form

[Himalaya]

I finally decided to transform my list of movies that I've seen from word to access after watching some youtube videos, as they made it seem easy enough.

I have tables:
Movies (MovieID, MovieName, MovieYear, MovieGenre, MovieActors, MovieActresses, MovieDirectors, MovieCountries, MovieStudios...)
Genres (GenreID, Genre)
Actors (ActorID, ActorNameandSurname, ActorYoB, ActorYoD, ActorCountry, Picture)
Actresses (ActressID, ActressNameandSurname, ActressYoB, ActressYoD, ActressCountry, Picture)


Directors (DirectorID, DirectorNameandSurname, DirectorYob, DirectorYoD, DirectorCountry, Picture)
Studios (StudioID, Studios)
ActorCojnunction (ActorID, MovieID)
ActressConjunction (ActressID, MovieID)
DirectorConjunction (DirectorID. MovieID)
StudiosConjunction (StudioID, MovieID)
GenresConjunction (GenreID, MovieID)


It all works well enough except now I'm at the form stage. I want to make a searchable form for each of the specific tables that I have. Meaning I want to search through a combo box either by typing in or by selecting that specific value through a list. I want what I type/click to show up. So I made a continues form to show all the movies in my main table (Movies) to which all the others are linked up through relationships.

All the movies show up. Next I created a combo box and through a querry I selected actors. Due to the fact that one actor can be in multiple movies I needed to group them up which I did through clicking the totals in the design stage. It worked and I could see just one record per actor. But when I went on the form and I clicked through them it just showed me movies where I had put one actor in. You see I, as a test to see if this will work, had put a few test records in.

1 ---- Movie 1 ---- Drama ----- -----------2005 ---- -Acotor 1, Actor2, Actor 3 ------ Actress 7 ----- -------------------------Director 1 ----- Country 1 ----- Studio 1
2 ---- Movie 2 ---- Comedy ----- ----------2011 ---- Acotor 4, Actor5, Actor 6 ------ Actress 6 ----- -------------------------Director 1 ----- Country 2 ----- Studio 2
3 ---- Movie 3 ---- Horror ----- ------------1977 ---- Acotor 1 ------ -------------------Actress 1, Actress 4, Actress 5 ----- Director 1 ----- Country 1 ----- Studio 3
4 ---- Movie 4 ---- Drama, Comedy ----- -1968 ---- Acotor 1, Actor6 ---------------- Actress 2, Actress 3 ----- -------------Director 1 ----- Country 1 ----- Studio 4

Now when I search through the combo box it only shows me the records where one actor is written. If I delete actors and leave just one there, then the combo box works well and all the records show as intended. But as soon as I put an additional actor there then the movie doesn't show, when I click on either of the two actors in the combo box. .

I am fairly new at this. Meaning I had some access training is school, but that was long ago. We worked on 2007 at the time. So I'm just getting back into it. What should I do to make this work?

[Micron]

If you can select more than one actor from a combobox list then your actor field must be a multi value field, but I'm not seeing that in your relationships window. Do you really have values like Actor, Actor2, Actor3 in one field? You could try basing your form recordsource on a query that uses LIKE operator on actor field (e.g. LIKE "*" & Forms!Filmi.Igralci & "*") and see what you get. Picture details very small so I'm guessing at form and field names. Shouldn't work if it's a multi value field though.

EDIT - could also review http://allenbrowne.com/ser-62.html

[Himalaya]

It's not a multi valued field. I don't think. I have actors in my actors table. Then in the conjunction table I write in actors ID and the movies ID. In the movies table I just write actors in manually. Because one actor can be in many movies... What I want to do is to search/filter using the actors name and it should return all movies he's in, regardless if the movie has one actor or multiple separated by a comma in that field. That's what's not working. If the movie has just one actor written it will return that movie, but as soon as you add another one seperated by a comma, it acts as if that movie doesn't exist. And will only show that result if you add all the values in that cell. Ie. Actor 1, Actor 3.

[Micron]

or multiple separated by a comma in that field.

That fits my definition of a multi value field, just not exactly the same kind as what Access creates. I suppose one might call it a csv field in order to be precise but I can't recall ever seeing that label given to what you have.
This is what junction tables are for - the csv values should be individual records in your junction table(s). Not sure I will have the time to go back and study your relationships. Got company coming.

[June7]

Actors and Actresses can be Directors and vice versa (sometimes for the same film). Actors and Actresses and Directors should probably be one table: People. Then if you really want to assign a gender have a field for that.

You have 'junction' tables to associate movies and other entities. Why are you replicating this info in Movies table with CSV fields? Here's another Allen Browne tutorial you should review http://allenbrowne.com/func-concat.html

And instead of 3 junction tables for Actor, Actress, Director have one (Credits) with another field for Category (performer, director, producer, screenwriter, cinematographer, costumer, etc).

[Gicu]

One possible solution is to modify how your combo search works; instead of using the

[ActorName]= Forms!frmYourForm.cboSearch

use

Instr([ActorName],Forms!frmYourForm.cboSearch)=True

as the criteria.

Cheers,