Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22

    You cannot add or change a record because a related record is required in table

    Good Morning,



    I have a problem with my access form, it said "You cannot add or change a record because a related record is required in table". I have attached the access file. Please help me. Thanks.


    Best Regards,
    Yoo
    Attached Files Attached Files

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    In Relationships, normally you would put the Primary Key value of the 1 side of the relationship in a Foreign Key field of the many side table.

  3. #3
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    So, What can I do on this access file? Thanks

  4. #4
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    Can someone help me for this problem in a simple way? The following is the link of my database so you can check for more detailed.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Options:

    1. Make StaffNumber the primary key in Staff and eliminate StaffID field

    2. Save StaffID in Outstanding
    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.

  6. #6
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    I have tried it and it is not working

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to save StaffNumber in Outstanding:

    1. delete StaffID field

    2. delete relationship

    3. set StaffNumber in Staff as primary key

    4. reset relationship

    5. modify the StaffNumber combobox to remove StaffID from RecordSource

    6. modify Staff form to eliminate StaffID textbox
    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.

  8. #8
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    Thank you for your help. However, it is coming error message "The expression On Click you entered as the event property setting produced the following error:Ambiguous name detected:exitbutton_click". it did not go throught. Please help.

  9. #9
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    I have modified the staff_report file as well. All of the buttons are not working and it did not allow me to add new record. Please help. Thanks.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't get any error messages. All buttons work. Record is added.

    Why do you have the Add button on Outstanding open a query instead of moving to new record on form, like you do on Staff_Report form? Do not use query to add new records to both tables. Use form/subform arrangement instead.
    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
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    Thank you for your reply. Is it able to use query to add new records for both tables?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I doubt it if relationship is set to enforce relational integrity. Anyway, I've never tried. Why would you want to?
    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.

  13. #13
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    I would like to do the seperate add record on query. Firstly, I would add all staff of record on staff record. After that, I will add outstanding record and make the combo box for all staff record and show on screen.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The query is an INNER join of 1-to-many related tables. Change it to RIGHT join and can add records to Outstanding:
    SELECT Outstanding.*, Staff.StaffNumber, Staff.LastName, Staff.FirstName, Staff.TeamCode, Staff.StaffNumber FROM Staff RIGHT JOIN Outstanding ON Staff.StaffNumber = Outstanding.Staffnumber
    ORDER BY Staff.StaffNumber
    WITH OWNERACCESS OPTION;

    However, the risk of messing up the Staff data is very high. Allowing data entry/edit directly with tables and queries is poor design.

    Much better to use form/subform arrangement.

    Should you be saving StaffNumber in Outstanding ApprovalBy field instead of staff name?
    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.

  15. #15
    Mahou is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    22
    Thank you for your reply, I have questions. if I already have staff record on database. I just input the outstanding record on staff record daily and print out the report to give to staff for reference. So, what shall I do. Thanks

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Related Record Required
    By ungratefulninja in forum Database Design
    Replies: 7
    Last Post: 11-02-2012, 10:21 AM
  2. Replies: 3
    Last Post: 08-21-2012, 01:10 PM
  3. Replies: 7
    Last Post: 07-02-2012, 10:50 PM
  4. Go To Record with related table Primary Key
    By jamiebull21 in forum Forms
    Replies: 3
    Last Post: 02-17-2012, 09:36 AM
  5. ...related record required...
    By degras in forum Forms
    Replies: 11
    Last Post: 11-03-2011, 12:20 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