First, sorry if this confuses anyone. I have a database to track my movies and actors. They all have their own tables (MovieList, ActorList) with unique IDs (MovieID, ActorID). A third table list actors and which movie they are in MovieActorList and contains only two fields, MovieID and ActorID, which gets populated through a form. The ActorID on the MovieActorList table is a combo box dropdown that lists all of the records in table Actor. When I pull up the form MovieInfo (which is based on MovieList table, with a subform called MovieActorInfo which is based on the MovieActorList table linked by the MovieID, the Actor field on the subform lists all of the actors.
Still following along? Here's what I'm trying to do: When I put in an actor for a movie, that actor is taken out of the available pool (to make sure that the same actor isn't entered twice on accident). I'm tracking that a Requery macro will need to be on the OnChange for that field. How would I set up the query to show all actors from the ActorList table that don't exist in the MovieActorList table with that specific MovieID?
So I enter a movie with MovieID of 1, the ActorList has 10 actors (1-10). I open the form for movie 1, and enter actor 1 into the subform. I select the next block and open the combo box, only actors 2-10 are shown.