Results 1 to 12 of 12
  1. #1
    Cheryl is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    May 2015
    Posts
    7

    Main form with 3 subforms and 2 sub subforms - main form duplicating records

    I have a main form called Course Details. The course shows at the top of my form with some details also showing on the first tab below. In the first tab below I have 2 subforms. One to show audits and the other to show duplicates (if the course has been duplicated elsewhere).



    On the 2nd tab I have another subform called units. On this sub form I have 2 sub subforms one for Audits and one for duplicates.

    The main course form is linked to the course audit table and course duplicate table 1 to many with the main course form being the parent.

    The unit details table is also a child of the course table, and also a parent to the unit audit table and the unit duplicate table.

    It all looked like it was working well. I could add a course and add many details about audit and duplicates for the course. I then tried adding unit details to the course and that worked well too. I also added unit audit details and unit duplicate details and it all seemed to work well.

    When I went back to the first tab (course details) and checked each record it seemed to duplicate each record to compare with how many units I had added. E.g. if I had 2 courses and added 5 units to a course, I would then have five instances of that course showing in the course area. When I closed the form and checked my course table only one instance was showing there.

    How do I stop extra instances showing on my form?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    Probably because of the form RecordSource. Is it a query or SQL statement with multiple tables?
    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
    Cheryl is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    May 2015
    Posts
    7
    Sorry bit of an Access noob here.

    The main course page is made from a table (just used the form wizard), the rest are made from queries. I created the queries then used the form wizard.

    Thanks you for looking at this for me June7

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    You said "The main course form is linked to the course audit table and course duplicate table 1 to many with the main course form being the parent." - this indicates the RecordSource is a query that includes 2 tables, one of which is the 'many' side of a relationship. This would cause duplication of the 'parent' record data. Is purpose of main form to show the parent record or the dependent records?

    Normally a form can do data entry/edit for only one table. Bind main form to the 'parent' table and subforms to 'dependent' tables.
    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
    Cheryl is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    May 2015
    Posts
    7
    The main form is the Course form.

    This is how my tables are set out
    Course (parent) - 1
    Course Audit (child of course) - Linked to course as Many
    Course Duplicates (child of course) - Linked to course as Many
    Units (child of course) linked to Course as Many
    Unit Audit (child of Units) - Linked to Units as Many
    Unit Duplicate (child of Units) - Linked to Units as Many

    A course can have many course duplicates, course audits and units. A unit can have many unit audits and unit duplicates.

    All information is entered on the Main form (Course). This form has 2 tabs. A Course tab and a Unit tab. The course tab shows the course details, Course Audit details and Course Duplicate details, The unit table shows the units, Unit Audits and Unit Duplicates. So you would open the form add the course, list any duplicates or audits, click the unit tab and add all units for that course while also adding any audit or duplicate info for each unit. I hope I am explaining this ok.

    After creating the forms I created queries.
    qryCourseDuplicates
    qryCourseAudit
    qryUnit
    qryUnitAudit
    qryUnitDuplicates

    I then created a form for all of those from the queries and a Main Course form from the Course Table. I added the CourseAudit and CourseDuplicate forms to Course main as subforms on the first tab called Courses. On the Unit Details form I added UnitAudit and UnitDuplicates forms as subforms. I then added the Unit Details form to the Main Course form under the Unit Details tab as a subform.


    Does this sound correct? Should I start all over again? Could you give me an overview on how you would have created this db?

    Click image for larger version. 

Name:	Database.jpg 
Views:	21 
Size:	234.6 KB 
ID:	20650

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    You have ID field linking to ID field for two relationships. Cannot link autonumber field to another autonumber field. Are all these ID fields autonumber type?

    I really don't understand what this database is for. Who are faculty and who are teachers - they aren't the same thing?


    Advice no spaces or special characters/punctuation (underscore is exception) in naming convention.
    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.

  7. #7
    Cheryl is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    May 2015
    Posts
    7
    The ID linking to ID fields are Lookups (the MoodleCourseDetails-D table looks up the course details, course name, number etc) This is so I can have a combo box in my form. The person entering data can choose a course and it will auto fill the course details (same in the unit). (one to one relationship) Yes they are both Auto numbers, Primary Keys. Is it ok in this instance or should I create a fk for these tables?

    Faculty and teachers are not the same thing. Faculty is like a section (e.g. Art, IT etc)

    Thank you re the spaces etc. I thought I was ok with this but noticed the space in Duplicate URL

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    Cannot link autonumber fields to each other. Primary key can be autonumber but foreign key must be a number type, even in 1-to-1 relationship.

    Also space in [Date audited], [Audited by], [Requested by]
    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.

  9. #9
    Cheryl is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    May 2015
    Posts
    7
    I created my combo boxes using this youtube video. They link Primary keys?

    https://www.youtube.com/watch?v=rSPS24EfijI

    I have found if I don't link them this way the combo box auto populates the next record when it should be blank?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    I can't watch youtube videos. Doesn't matter - can't link autonumber fields. Can link primary keys if they are not BOTH autonumber. This means a 1-to-1 relationship.

    I don't understand 'populates the next record'.
    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.

  11. #11
    Cheryl is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    May 2015
    Posts
    7
    Sorry June7. I seem to have everything working now except for the original problem. I will try to explain it better.

    A course can have many units. I created a course main form with a subform called units.

    I entered 2 course details on the main form.
    I didn't enter any unit details for the first course. I could click back and forth and see both courses. I checked my course table and could see them both there.
    I then entered 4 units for the 2nd course. When I looked at the main form it was then showing the 2nd unit duplicated 4 times (e.g. a copy for each unit that I was adding). The form now shows 5 of 5 course records.
    I checked the course table again and could still only see 2 courses.

    It seems to be a visual problem only. The tables look ok.

  12. #12
    Cheryl is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    May 2015
    Posts
    7
    It's working now.

    Opened the form in Design view and changed the record source to just the name of the main form table.

    Thank you for all your help June 7

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

Similar Threads

  1. Adding subforms on a main form
    By ludovic_44 in forum Forms
    Replies: 14
    Last Post: 04-28-2014, 09:45 AM
  2. Replies: 19
    Last Post: 05-13-2013, 01:26 AM
  3. Multiple subforms to one main form
    By mick3911 in forum Forms
    Replies: 8
    Last Post: 02-25-2013, 09:40 PM
  4. Replies: 3
    Last Post: 08-29-2012, 02:42 PM
  5. Replies: 0
    Last Post: 12-16-2008, 07:49 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