Results 1 to 11 of 11
  1. #1
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20

    Multiple subform records creates duplicate record in form

    Hey all -



    I posted here a few days ago looking for help, and now I have a new issue... This is a database to track teacher information and participation in an environmental education program. Everything seemed to be going great until I noticed something funky with my form and the records it displays.

    The main table (tblTeacherInfo) is for teacher information, the other table (tblProgramsAttended) tracks program attendance and it pulls from a third table (tblProgramInfo) that lists the types of programs. I have posted a screenshot of the relationships window, as well as two screenshots to show that records 4 & 5 in form view are duplicate. Now, the tables do not have duplicate records - it just shows it in the form. (Note: I "redacted" some personal info from the screenshots.)

    The form lists teacher info and has a subform which shows the program attendance. The duplicate form record only occurs if I enter more than one record in the subform. If I delete one of the duplicate records, it also deletes the second record in the subform. If I add a second record to the subform, the duplicate does not appear until I close and reopen the database. Any ideas what I need to do to fix this issue? Happy to provide more information - just please keep in mind I am a novice, so define those acronyms and database jargon.

    I have an earlier version of this database (a control, if you will) where I was not having the duplication issue. I have gone back through the older version and redone any changes I can think of or see, but can't get the issue to occur in my control, or fixed in the latest version.

    I was experiencing a strange error earlier whenever I clicked into the subform that said "The expression On Enter you entered as the event property setting produced the following error: Object or class does not support the set of events." - I resolved this by deleting the [Event Procedure] that had somehow appeared in the "On Enter" field of the Properties window for the subform. Somehow I feel that this error and my duplication issue are related (it's too coincidental), but I've been wrong before.

    Thanks,
    Joe

    Click image for larger version. 

Name:	DB Image - Relationships.jpg 
Views:	30 
Size:	91.8 KB 
ID:	25828Click image for larger version. 

Name:	DB Image - Form Record 4.jpg 
Views:	30 
Size:	134.8 KB 
ID:	25826 Click image for larger version. 

Name:	DB Image - Form Record 5.jpg 
Views:	29 
Size:	135.0 KB 
ID:	25827

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is the record source for your main form? It doesn't look as though it is the table of teachers. Maybe it is a query?

  3. #3
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20
    No the main form is built off of the "tblTeacherInfo" - although I've built a few queries I haven't actually used those for anything. I wanted to make sure my tables and forms were functioning correctly.

    The big idea for this database is that someone five years from now can use this to continue to input new teachers and update program attendance (as well as use the form for a reference for this data), so I want the form to be the major utility used.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It would help if you could post your database. There don't seem to be many records, you could change the names and other pertinent data first.

  5. #5
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20
    aytee111
    It would help if you could post your database.
    I'm sure it would, but the forum's file upload is not working for me this morning I considered just using the older version that does not have this issue, but I would really like to know the cause/solution. Plus I don't feel like doing all of that data entry again (there is still a lot to put in).

    I will try again later, but in the meantime, do you (or anyone) have a guess to point me in the right direction?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Compact and Repair the db first and then zip up the db. It should upload just fine.

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Might it have something to do with the fact that the primary key on tblProgramInfo is linked to ProgramType on tblProgramsAttended? Doesn't look right.

    The ProgramID is the primary key on tblProgramsAttended, this also doesn't look right

  8. #8
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20
    Okay zip file attached (thanks, RuralGuy).

    @aytee111 - I thought the issue might be due to my relationships, but after looking at and updating my control DB (which now has the same tables, fields and relationships), I wasn't so sure. Still, I would not be surprised to learn there is a better way to establish these relationships. Originally I had just two tables, but added tblProgramInfo because I wanted to be able to add new programs in the future (i.e. now I have a new field trip to offer and want that reflected in my data - very easy to just add that to the table; before I had the types of programs entered manually in a lookup wizard).

    Thanks for the support so far - hope we can solve the issue. Down at the bottom of my thread was a link to a similar issue, but I couldn't glean any new info from that.
    Attached Files Attached Files

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    In the Record Source of the [Teacher Information Form] form, remove the tblProgramsAttended reference with the Lookup Field and see what happens. Here's a link to review: http://access.mvps.org/access/lookupfields.htm

  10. #10
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20
    Quote Originally Posted by RuralGuy View Post
    In the Record Source of the [Teacher Information Form] form, remove the tblProgramsAttended reference with the Lookup Field and see what happens. Here's a link to review: http://access.mvps.org/access/lookupfields.htm
    Okay I just deleted everything I typed explaining how I didn't understand you 100 %

    I compared this "Record Source" field with my control database, and the only thing it said was "tblTeacherInfo" instead of this big string of text in the one with the duplication issue:

    SELECT tblTeacherInfo.*, tblProgramsAttended.ProgramType FROM tblTeacherInfo INNER JOIN tblProgramsAttended ON tblTeacherInfo.TeacherID = tblProgramsAttended.TeacherID;

    Once I cut all that out and made it the same as the control, the duplicate records are gone! How would this have even gotten altered? Possibly when I was adding new fields to the subform?

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you press the "..." button next to the Record Source property, Access will create a query for you and put it in design view which is easier to work with than SQL (in my opinion). Just thought you'd like to know.

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

Similar Threads

  1. Form creates a duplicate table record when saving
    By TeamTraveler in forum Forms
    Replies: 5
    Last Post: 05-11-2015, 09:34 AM
  2. Replies: 14
    Last Post: 08-01-2013, 09:46 PM
  3. Replies: 5
    Last Post: 08-21-2012, 12:30 PM
  4. Replies: 5
    Last Post: 06-29-2010, 01:24 PM
  5. subform creates duplicate record
    By jheintz57 in forum Forms
    Replies: 0
    Last Post: 03-29-2010, 11:50 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