Results 1 to 7 of 7
  1. #1
    cpbrin2000lbs is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    5

    Parent/Child link breaks when form with subform is inserted into a third form

    I am working in an access database where I want to have a form with a sub form that has a tab control with multiple sub-sub forms. The top form links between primary_key1 and foreign_key1 on the sub form with the tab control, and the sub form links between primary_key2 and foreign_key2 on the sub-sub forms. Opened directly the sub form and all the sub-sub forms are linked together and function as intended. However, when inserted into the top form and opened access opens a popup asking for a value for primary_key2. Clearing the linking properties on all the sub-sub forms and testing each one, I determined that it is doing this for each of the sub forms.

    Here is the database minus some fields so that it is small enough to attach:cpbrin2000lbs database.zip

    As I am not sure where the problem is, I’ll provide as many details on the database structure as I can think of, a
    pologies for extraneous information. The form in question is called Flight_Frms. Below is the relationship view of the relevant tables. The Operations table is the record source for the top form, Flights for the sub form, the other four are the record sources for the sub-sub forms. The operations form and flight forms are formatted as single forms, the sub-sub forms are a mix of continuous and datasheet forms.





    Table Field Field Size
    Operations ID (primary key) Long Integer
    Operations 10 other fields Date/time, Long Integer, Short Text, or Attachment
    Flights Flight_ID (primary key) Long Integer
    Flights Operation_ID (foreign key) Long Integer
    Flights 57 other fields Date/time, Long Integer, Short Text, Yes/No, Hyperlink, or Attachment
    FlightCrewAssignments_JcnTbl ID (primary key) Long Integer
    FlightCrewAssignments_JcnTbl OperationID (foreign key) Long Integer
    FlightCrewAssignments_JcnTbl FlightID (foreign key) Long Integer
    FlightCrewAssignments_JcnTbl Crew_RoleID (foreign key) Long Integer
    FlightCrewAssignments_JcnTbl CrewID (foreign key) Long Integer






    preemptive thanks for your assistance
    Cameron
    Last edited by cpbrin2000lbs; 01-05-2021 at 07:39 AM. Reason: attached a condensed version of DB rather than external link

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Whatever you did, the link is asking me to create an account. Try again.....

    A lot of people won't download from an external account (Box, OneDrive, Dropbox) so try attaching it here. See the link in the header "How to Attach Files"

  3. #3
    cpbrin2000lbs is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    5
    Thanks Steve,

    I was forgetting to compact the database after deleting fields yesterday and so was having problems getting it small enough even with compression into a zip file. It is attached to the original post now.

    Cameron

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Table relationships are confusing and may be suffering from circular reference https://www.codeproject.com/articles...atabase-design

    Why would you have OpperationID in FlightCrewAssignments_JcnTbl?

    (Why spell Opperation with two p's?)

    Tables have foreign key fields for EquipmentID and BatteryID but I don't see lookup tables for Equipment and Batteries.

    Why use a query for RecordSource when there is no filtering or calculations in query and no table joins? - just use table.

    Why does EquipmentCheckC_Qry join Inventory table to itself? What is purpose of EqupmentChecklist form? If you want to enter data into Inventory, I don't see how this can be done by linking through FlightID in EquipmentCheckout table.

    It is baffling why links are broken. I created a new main form bound to Operations and installed Flights_Subform on that new form. There is no code behind the new form. Everything works. I deleted all code behind Flight_Frms and now it works. Issue is with 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.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In my opinion, there are issues with the table designs and relationships. These should be fixed before doing anything else.
    - There are field names with punctuation/special characters. Object names should be only letters/numbers (exception is the underscore). Do not begin object names with a number.
    - There do not use look Up FIELDS in tables, instead use combo boxes on forms.
    - "Type" is a reserved word in Access and shouldn't be used for an object name.
    - Having "ID" as the name of every PK field is bad. Take the time to give (all) fields descriptive names.
    - In EVERY module (form, report, standard), the top two lines should be
    Code:
    Option Compare Database
    Option Explicit
    If the PK field type is Autonumber, the Foreign key fields should (must) be Long Integer type. A FK field (long integer) should never have a default value set.
    See Microsoft Access Tables: Primary Key Tips and Techniques
    I like to use suffixes of "_PK" and "_FK" to be able to see relationships easier. (at least for me)
    I also created a new form (no code), added the subforms and did not have any errors (except where the FK value was missing).

  6. #6
    cpbrin2000lbs is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    5
    Thankyou June7, ssnafu, and mike60smart for looking through this database for me, and especially to mike60smart for skyping me to go over the database with me. I now have the database in working order.

    @June7

    Why would you have OpperationID in FlightCrewAssignments_JcnTbl?
    This is a relic of a discarded database design that used the operation rather than the flight as the foreign key and I have now deleted this field.

    (Why spell Opperation with two p's?)
    Just poor spelling on my part and an over reliance on spell checkers, by the time I noticed I was doing it the error was widely propagated in the database and I am fixing them as I see them.

    Tables have foreign key fields for EquipmentID and BatteryID but I don't see lookup tables for Equipment and Batteries.
    those foreign keys are both looking up the primary key in the inventory table and using a query to limit the combobox to only the appropriate items, so just sloppy naming.

    Why use a query for RecordSource when there is no filtering or calculations in query and no table joins? - just use table.

    Why does EquipmentCheckC_Qry join Inventory table to itself? What is purpose of EqupmentChecklist form? If you want to enter data into Inventory, I don't see how this can be done by linking through FlightID in EquipmentCheckout table.
    The idea behind this subform was for the user to have previously selected a kit that they were checking out in the Equipment Assignments sub form and then the queries would populate a list of items in those kits that the user could then use as a checklist for ensuring that they had all the items they needed before leaving the office. It worked in the first edition of the database and was fun to build, but was not really useful and overly complicated so I have decided just to create a static checklist for each kit that I will print and laminate so users can have it when needed without pulling up the database. Consequently this is form is no longer in the database.

    It is baffling why links are broken. I created a new main form bound to Operations and installed Flights_Subform on that new form. There is no code behind the new form. Everything works. I deleted all code behind Flight_Frms and now it works. Issue is with code.
    I think it is an interaction between the code and the fields with special characters as when I was working with mike60smart he noticed that the problem persisted with new forms in some cases as well. More details below.

    @ssnafu


    There are field names with punctuation/special characters. Object names should be only letters/numbers (exception is the underscore). Do not begin object names with a number.
    I think this one in particular was causing problems and I have now fixed it.

    There do not use look Up FIELDS in tables, instead use combo boxes on forms.
    I did a few of these when I learned it was possible as it seemed shiny, but as I am using combo boxes as you suggested I will go through and remove them to avoid future problems.

    "Type"is a reserved word in Access and shouldn't be used for an object name.
    Didn't know that was on the list, I'll fix that

    Having "ID" as the name of every PK field is bad. Take the time to give (all) fields descriptive names.
    Both you and mike60smart chastised me for this and I can now happily report that the field names are now all updated and when I went through to enforce structural integrity I updated the names an formating of the foreign keys to match, and changed the default values to null rather than 0. Finding and correcting all the changes in the code was a bit of a pain, but worthwhile.


    __________________________________________________ ___________________________________


    All is working as intended after recreating the tab control, renaming the offending control names, and reformatting relationships to enforce structural integrity, and the other various database cleanup items you suggested. Most of the code remains the same except that it is updated for the new field names, and the code related to the forms I deleted is also deleted. I think the Tab Control was somehow corrupted either caused by or compounded by an OLE/Direct x error caused by controls which contained a special character in their name, namely: ˚ to indicate degrees of latitude and longitude. This problem further produced errors by creating hanging records without accurate foreign keys. However to be honest I made so many corrections it is possible that one of the others was also a primary cause


    The largest mystery to me remains that the broken parent/child link only surfaced when the form with the tab control was opened in another form and not when opened directly. However as it is working I am content to leave this sleeping dog lie.

    Again many thanks,

    Cameron
    Last edited by cpbrin2000lbs; 01-14-2021 at 04:09 PM. Reason: edited for clarity

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to try and help. Good luck with the rest of your project....

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. Replies: 3
    Last Post: 11-16-2016, 09:24 AM
  3. Replies: 6
    Last Post: 12-13-2013, 12:38 PM
  4. Replies: 3
    Last Post: 07-10-2013, 08:54 AM
  5. Problem using Parent/Child Form/Subform
    By EvanRosenlieb in forum Forms
    Replies: 4
    Last Post: 06-27-2011, 05:25 PM

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