Results 1 to 7 of 7
  1. #1
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    Parent-child form relationships and cascading combo boxes

    Hi folks! I previously posted a question about cascading combo boxes here, but it became more about redesigning the database. Now, I'm having trouble with my forms using this new database design, which is attached with all forms, queries, tables (clam_test3.zip).



    In my main form (and tblMain), I have a field called Port. There is a lookup table for the options for Port in luPort. I created a combo box using the luPort values to populate the Port field in tblMain. I have and Area field in two other tables (yes, these are different information): tblEffort (where we count # people in Area) and tblInterviews (where take info from individuals at Area). I have a subform for populating tblEffort and a subform for tblInterviews. I have combo boxes for Areas (referencing lookup tblAreas) for both these subforms. The goal is to get only the options for each port in the subforms based on the selection in the main form.

    There are probably many ways to create cascading combo boxes, but my lack of vba programming experience led me to trying a simple process of calling up a query (qryAreas) and using the Forms!fmPort!cboPort criteria in the query Design View for the query. In the After Update, I added the code line: me.cboPort.Requery. However, I get a pop-up asking me to manually input the port, so I'm clearly not doing this correctly. Other things in my forms aren't working either (yes/no boxes aren't checking, etc) so I'm thinking there is an underlying problem with the form parent-child relationships that I've screwed up. I really appreciate any time you can take to look at the forms.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    See if this shows any progress:

    clam_test4-davegri.zip
    Last edited by davegri; 04-11-2018 at 11:27 PM. Reason: update

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    clam_test5-davegri.zip
    Spent some more time on this and got a better feel for the project.
    Made changes to keys and relationships.
    Changed name of tbl_Main to tbl_Surveys.
    Added queries for the both subform recordsources.
    I think the area dropdowns now reflect the port properly
    Fixed the comboboxes for selecting rain, clouds, etc. Form now display the names instead of the keys.
    Sampler was a multivalue field. Bad design.
    If you need 2 samplers per survey, suggest adding a second combobox for the second sampler, or mention the second sampler in the notes.
    Last edited by davegri; 04-13-2018 at 07:23 AM. Reason: more

  4. #4
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    Thanks davegri! I'll take a look at it this afternoon after I return from the field (collecting more data). I was able to take a brief look at test4 and noticed that the dropdowns used the FieldName instead of the FieldCode, which is actually what we use (e.g. 902 instead of "Cockle"), but that should be easy to adjust. Haven't had a chance to run through it in detail and I'll focus on test5 now. Thank you very much for your time and assistance!

  5. #5
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    Still having trouble with updating combo boxes

    My forms are cleaned up a bit here. I'm still having trouble getting the updating cascading combo boxes to work though. When I select the Port from the main form, I get choices for both my Area combos in the two subforms. However, if I change the Port or create a new record, the choices are a holdover from that first selection.

    I need to learn more about vba, since I'm having no success with changing the syntax to get it to work even with watching a bunch of videos. Also, should the code be for On Change, After Update, On Click? Attached is my latest version.

    clam_test7.zip

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Add this code to form [Data Entry - Main]
    It will cause the area comboboxes on the subforms to take a new look at qryAreas after the port is updated.
    The previous values in the area comboboxes on the subform are blanked out.
    Code:
    Private Sub cboPort_AfterUpdate()
        Forms![Data Entry - Main]!sfmEffort.Form!cboAreaEff.Requery
        Forms![Data Entry - Main]!sfmInterviews1.Form!cboAreaInt.Requery
    
    
        Forms![Data Entry - Main]!sfmEffort.Form!cboAreaEff = vbNullString
        Forms![Data Entry - Main]!sfmInterviews1.Form!cboAreaInt = vbNullString
    End Sub

  7. #7
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    Thank you, thank you!! This really helped. I was trying to create my own code like this but wasn't really getting it. I'm inspired to learn more coding to perform important tasks for us!

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

Similar Threads

  1. Query criteria breaks parent/child relationships
    By RankSinatra in forum Forms
    Replies: 2
    Last Post: 02-28-2018, 07:49 PM
  2. Relationships & Cascading Combo Boxes
    By JeRz in forum Database Design
    Replies: 2
    Last Post: 12-26-2016, 08:55 AM
  3. Cascading combo boxes in datasheet form
    By gemadan96 in forum Forms
    Replies: 1
    Last Post: 06-20-2014, 09:59 AM
  4. Replies: 3
    Last Post: 07-03-2013, 01:20 PM
  5. Replies: 0
    Last Post: 05-26-2010, 05:46 PM

Tags for this Thread

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