Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2012
    Posts
    53

    List records not in table with multiple criteria

    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.

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    1) Usually experts advice against using dropdowns in table fields
    2) Are you indicating that the same actor should not be entered twice for the same movie or he / she is not available in the dropdown for another movie ? Pls tolerate the ignorance.

    Thanks

  3. #3
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2012
    Posts
    53

    List records not in table with multiple criteria

    1) the drop down is basically a look up field

    2) trying to prevent an actor being listed twice for the same movie. Obviously each actor is going to be listed for multiple movies. Using the example above, actor 1 could be in movies 1, 2, and 3. So when working with movie 1, after entering actor 1, they won't be listed when I move to the next row (the sub form is a data sheet view by the way) to enter the next actor, actor 1 won't be listed. When I open the form again for movie 2, all the actors will be listed again.

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just check, if attached below, gives some guidelines :

    Open the form "frmMaintblMovies".

    Thanks
    Attached Files Attached Files

  5. #5
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2012
    Posts
    53
    Thanks Recyan, that's exactly what I was shooting for.

  6. #6
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Glad it served your purpose.
    But it is a pretty crude effort, as I am pretty bad with Forms / Reports & Events & VBA.
    Request you to refine it for your purpose & definitely look for bugs.

    As to No 1) in our previous discussion, as an example :
    http://access.mvps.org/access/lookupfields.htm

    Hope someone experienced gives us some guidelines on it.

    Thanks

  7. #7
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2012
    Posts
    53

    List records not in table with multiple criteria

    If lookup field in tables are such a pain, what is the recommended practice?

  8. #8
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2012
    Posts
    53
    MovieTracker sample.zip

    So I tried entering that query as you showed, but came up with a new problem: when I open the form, actors that were already listed don't show up (just blank spaces). the actors who are supposed to be filing those spots aren't listed, so that's a win, but win I open the movie up again, that actor is no longer showing up. I've attached a washed down version with only the objects needed.

  9. #9
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Is it happening in the db that I attached also ?
    If no, try & compare what is different.
    Will try & take a look tomorrow, as I am packing up for the day.
    Note : I have removed the "Thread Solved" remark.

    Thanks

    Edit : Cannot open the db. You need to save it in .mdb format & reattach it. Will be accessible to all.

  10. #10
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2012
    Posts
    53
    Sorry, attached is the correct format.

    MovieTracker sample.zip

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Instead of lookup in table, just build combobox or listbox on form.

    I think this requires a dataset of all possible actor/movie combinations. Create that dataset with a query WITHOUT a join. This will be a Cartesian relationship - every record in each table will join with each record in other table, call it ActorMovieALL:
    SELECT ActorList.ActorID, MovieList.MovieID, ActorList.FullName FROM ActorList, MovieList;

    Now combobox RowSource joins that query to MovieActorList.
    SELECT ActorMovieALL.ActorID, ActorMovieALL.FullName
    FROM MovieActorList RIGHT JOIN ActorMovieALL ON (MovieActorList.MovieID = ActorMovieALL.MovieID) AND (MovieActorList.ActorID = ActorMovieALL.ActorID)
    WHERE (((ActorMovieALL.MovieID)=[Forms]![MovieList]![MovieID]) AND ((MovieActorList.ActorID) Is Null));

    This is a dependent (cascading) combobox. Need code to requery the combobox.
    Private Sub ActorIDCombo_GotFocus()
    Me.ActorIDCombo.Requery
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Check attached.

    Thanks
    Attached Files Attached Files

  13. #13
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2012
    Posts
    53
    Recyan: Still having the same problem. I copied your AvailableActors query, redirect the form towards it, set everything up and still had the same issue. I'll try and tackle it again later. Thanks everyone for your help and the ideas.

  14. #14
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Are you facing the problem in the db that I have attached ?

  15. #15
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Will try & explain the steps carried out :
    1) Table ActorList available.
    2) Table MovieList available.
    3) Table MovieActorList available.
    4) Used the Relationship Window to create the relationships.
    4) Created form MovieList using table MovieList as source & saved it as MovieList form.
    5) Created query qryMovAct.
    6) Created query qryAvailableActors.
    7) Created subform MovieActorSubForm, using qryMovAct as source.
    8) Opened form MovieList in Design view & added subform MovieActorSubForm to it, using ( Select from my own list) MovieID from the dropdowns available for connecting the Main Form with the subform.
    9) Saved the form.
    10) In Design mode of the form, for the text field ActorID, used "Change to Combo box".
    11) Saved the form.
    12) In the properties of ActorID in the subform, added "qryAvailableActors" as the Row Source.
    13) Again in the properties for ActorID, in the Event tab, in "On Focus", used Code Builder & added "Refresh".
    14) In the properties for MovieID, in the Main Form, in the Event tab, in "On Change", used Code Builder & added "Refresh".
    15) Saved form.
    16) Above steps appears to be working for me.

    Edit : Missed out one, the properties of ActorID combobox.
    In Format tab, changed Column Count to 3 & Column Widths to 1";1";1" & Colum Heads to Yes.

    Will get back, if I remember anything else, but I think, above should suffice.



    Thanks

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 05-13-2014, 08:22 AM
  2. Replies: 4
    Last Post: 12-18-2013, 04:08 PM
  3. Replies: 7
    Last Post: 07-11-2013, 10:05 AM
  4. Replies: 5
    Last Post: 01-24-2012, 06:19 PM
  5. list box with multiple criteria
    By white_flag in forum Access
    Replies: 6
    Last Post: 07-25-2011, 11:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums