Results 1 to 13 of 13
  1. #1
    woodpecker is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    11

    form not allowing me to add a new record

    my database was created in 2015 and it has been working perfect until today.
    when entering a new record on a form I receive this error message


    You cannot add or change a record because a related record is required in table . (Error 3201)

    any ideas what when wrong?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    None. The error message is self-explanatory.
    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
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    did you add a relationship join between tables? or redesign the entry form? in any case it is referential integrity being enforced between tables..... as if you are entering the child record for which there is no existing mandatory parent record yet.....

  4. #4
    woodpecker is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    11
    Quote Originally Posted by NTC View Post
    did you add a relationship join between tables? or redesign the entry form? in any case it is referential integrity being enforced between tables..... as if you are entering the child record for which there is no existing mandatory parent record yet.....
    I did not change anything it just started doing this not sure what to do the DB has 3 years of data in it

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I'm assuming you have a main/subform setup.
    When you get the error are you trying to create a new record on the main form or the subform? If it's the subform (child), have you created the record on the main form (parent) first?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    Dehn0045 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    11
    Is it possible that the related table was built with limited number of records and now you have used up all of those records? For example, maybe a calendar table with all possible dates was built, but only 2 or 3 years of dates were entered at the beginning. If there is nothing to automatically add new dates when they run out, you might end up with this error. Just an idea, good luck.

  8. #8
    woodpecker is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    11
    NAMIOct2017.zip

    Open DB select admin password is admin select Mary's switchboard then data entry switchboard then select program attendance and try to enter a new record

    Thank you

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    What format is your file? It has a 7z extension which means nothing to me. I can unzip it but not open it.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The PeopleID column in tblAttendance has every row blank.
    Delete the tblPeople_1 relationships and hide this table (it doesn't actually exist in the Nav Pane, it's an alias of tblPeople), then your form will work.
    The (poorly named - because Name is an Access reserved word) Name field in tblAttendance is numeric and does work OK as a link back to tblPeople.
    However, you cannot set referential integrity between tblPeople and tblAttendance until you clean up your data to remove existing 112 orphans in tblAttendance. Below query from the wizard shows the orphans.
    Code:
    SELECT tblAttendance.Name FROM tblAttendance LEFT JOIN tblPeople ON tblAttendance.[Name] = tblPeople.[ID] WHERE (((tblPeople.ID) Is Null));
    I suspect that your "working perfect until today" database has been compromised by someone fooling around with the relationships, as several tables have 1,2 or even 3 aliases. Pretty hard to keep from screwing up the relationship links with that ambiguity.
    Click image for larger version. 

Name:	People.JPG 
Views:	14 
Size:	47.5 KB 
ID:	33790
    A word of advice, you have around 50 tables and every one has a primary key named "ID". These keys have relationship links to other tables and those foreign key names do little to show the programmer (you and me) what table the link points to.
    The working convention is to name all primary keys with a _PK suffix, like tblPeople_PK, then the link pointing back in tblAttendace be named tblPeople_FK (instead of Name). It makes life a lot easier.
    The _PK is for Primary Key, and _FK is for Foreign Key.
    And speaking of reserved words, Date is also an Access reserved word. In tblAttendance you should change that field name to something like AttendanceDate, which describes it exactly as a bonus.
    Last edited by davegri; 04-30-2018 at 07:42 AM. Reason: PK FK add

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Davegri
    How did you open it? I tried changing it to accdb and got incorrect file format. Didn't try mdb which perhaps is what it is?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Right click to open, then right click and open the .7z file which showed the accdb in 7zip.
    Copied the accdb and pasted to my HELP folder.

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oh I see. Double zipped just to be sure....
    As I don't have 7zip, not sure I can unzip that anyway so I'll leave it in your more than capable hands
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 9
    Last Post: 11-25-2017, 01:49 AM
  2. Replies: 8
    Last Post: 10-25-2017, 01:59 PM
  3. Replies: 1
    Last Post: 06-09-2015, 07:45 AM
  4. Replies: 2
    Last Post: 10-10-2014, 06:27 AM
  5. Subform not always allowing new record creation
    By Canadiangal in forum Forms
    Replies: 1
    Last Post: 03-28-2013, 11:00 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