Results 1 to 4 of 4

Cascading Combo issue

  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    278

    Cascading Combo issue

    Hello all,

    I have never played with cascading combo boxes and decided I would try but I am having issues. In the attached file if you open the frmMovies and look at the subforn "frmMoviesSubform1" at the bottom of the form you will see one issue. The intent is to be able to put all of the actors/actresses that are in the movie in the subform however I cannot add a character name for Mickey or Minnie. I get a parameter error and I believe this is at least partially because it is in a subform, if I try to enter it directly into the subform I don't get the error but it also doesn't list the correct info (it's blank).



    I am not good a SQL and limited in VBA but trying to get better at both (hence the reason for this exercise)

    If I haven't explained what the issue is well enough, let me know.






    Playing 11-7-19.zip
    Dave

    Learn Something new everyday. A day in which you learn nothing is a wasted day.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,401
    First of all, need to set subform container Master/Chilld Links properties so forms will synchronize related records.

    Second, tblCast and your cascading combobox makes no sense to me. If anything, Character combobox should only list characters that are associated with movie. tblCharacter would be better named tblMovieCharacters and remove CastID_FK field. Have a textbox named tbxMovie in subform. Combobox properties:

    RowSource: SELECT tblCharacter.CharacterID, tblCharacter.CharacterName FROM tblCharacter WHERE (((tblCharacter.MovieID_FK)=[tbxMovie]));
    ColumnCount: 2
    ColumnWidths: 0";1"
    ControlSource: CharacterID_FK

    Then you need VBA code that requeries combobox for each movie.
    Code:
    Private Sub Form_Current()
    Me.frmMoviesSubFrm1.Form.CmbCharacterSelect.Requery
    End Sub
    

    Third, there is no need for CharacterName textbox nor code to save this value. CharacterID is saved via combobox and character name can be retrieved in query linking tables. Same for ActorActressName textbox. Change this combobox properties:

    RowSource: SELECT tblCast.ID, tblCast.ActorActressName FROM tblCast;
    ColumnCount: 2
    ColumnWidths: 0";1"
    ControlSource: CastID

    Remove ActorActressName field in tblMovieCast. Should probably rename CastID to CastID_FK and MovieID to MovieID_FK. Be consistent with naming convention.

    Remove CharacterID_FK and CharacterName from tblCast. Better table name might be tblPerformers and PerformerID as primary key field name.

    However, this gets more complicated when you create a new movie record because that requires also creating character records. So will likely need combobox NotInList event code.

    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.

  3. #3
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    278
    June7

    I had neglected to set the Mater/Child fields, that is corrected.

    Second-The cascading Combos are my first attempt at using them, in fact this entire database is just an exercise at using them. tblCast is a list of Actors/Actresses which currently has a field for Character, not sure if I want to keep this there or not, still thinking about that.

    Third - The CharacterName and Actor/ActressName text boxes are there for my own testing purposes currently, I agree to using query to create any report etc to join the data when needed.

    Do you know why the error is flagged when trying to put the character name in the subform on the frmMovies but it isn't if you open the subform directly? It has to be because the entry is being made on the subform instead of the actual form but I don't understand why.
    Dave

    Learn Something new everyday. A day in which you learn nothing is a wasted day.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,401
    Parameter popup occurs when form is subform because path reference does not include main form. Simplify by eliminating form prefix:

    WHERE (((tblCast.ActorActressName)=[CmbActorActressSelect]))

    However, moot point if you consider my suggested modifications.
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. cascading combo box issue
    By ShostyFan in forum Queries
    Replies: 3
    Last Post: 08-05-2015, 08:56 AM
  2. Cascading Form issue
    By GSevensM in forum Forms
    Replies: 7
    Last Post: 04-03-2014, 11:51 AM
  3. Cascading ComboBox Issue
    By Nippy in forum Forms
    Replies: 4
    Last Post: 03-13-2014, 10:06 PM
  4. Replies: 1
    Last Post: 02-27-2014, 03:43 PM
  5. Cascading combos issue
    By Andyjones in forum Programming
    Replies: 5
    Last Post: 04-14-2012, 11:09 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
  •  
Tech Forums: Microsoft Office Forums