Results 1 to 13 of 13
  1. #1
    Join Date
    May 2013
    Posts
    5

    Unanswered: You Cannot Add Or Change A Record Because A Related Record Is Required In Table 'empd

    I am having a little challange with this database I have designed and maybe someone can point me in the right direction. I have a main form "EmpDate" and a subform "subWorkHours". In the main form "EmpDate" I want to add the employee id and then the work date which I want carried through all records in the subform so they don'y have to be changed until you go to a different employee. This seems to work but when I go to the subform and try and select the JobID from a lookup combo box I get the error "You cannot add or change a record because a related record is required in table 'EmpDate'". I don't need them in this table. Can anyone take a look at my database and see what I might do to fix this. I have attached the database. Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    There are a few issues I noticed:

    1. All PK must be of Long Integer type. Your PK in LaborCodes is of type Double. You will need to delete your relationship, clean up your data (I noticed multiple rows with the same PK value), change LaborCode to Long Integer, change LaborCode in your WorkHours table to Long Integer and then re-establish your relationship.

    2. You can not have a relationship between the PK of two tables, PK's are unique identifiers for each individual table. So, you need to delete the relationship between tables EmpDate and WorkHours that was established based on AutoID. I believe you want the relationship to be based on EmpID. Then, for your Linked fields in your EmpDate form delete the link that is on AutoID.
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your problem is that you have defined a 1:1 link between two AutoNumber fields. This is not a good idea as such fields may be incremented unexpectedly and get out of step with each other.

    I have redefined the primary key on EmpDate, along with its relationship to WorkHours, so it will now work as you requested. I have also amended the combo boxes so that they display the name or description of the relevant record, and amended the tab order of the subform so that the cursor doesn't go backwards.
    Attached Files Attached Files
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Aug 2013
    Posts
    7

    Adding new record, Need Help please.

    I am trying to build an access data base for the company i work with but i keep getting this message when i try to create anew record through a form, so what am i doing wrong here is it the table relation linking or what please help, thanks.
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2005
    Posts
    146
    You are being stopped as a result of not satisfying your relationships. The one side table always needs to have a record before the many side table. With that said, you are trying to create one big form that enters data into all your tables at once. That can't be done. You need the one side table in the main form and you can have all the many side tables as subforms with a proper Master/Child link set up. This will keep everything properly organized.

  6. #6
    Join Date
    Aug 2013
    Posts
    7

    Adding new record, Need Help please.

    Thank you very much for the fast replay, but since am new in database can you please show me a simple example for that, it wold be great if you demonstrate on some of the table as main form and sub forms, many thanks.

  7. #7
    Join Date
    Jan 2005
    Posts
    146
    frmEquipment is the main form based on table Equipment and frmEquipmentOrientation is the subform based on table EquipmentOrientation and they all linked together based on SAPEquipmentID.

    Good luck with your project.
    Attached Files Attached Files

  8. #8
    Join Date
    Aug 2013
    Posts
    7

    Adding new record, Need Help please.

    I am amazed from your fast replay and i can not thank you enough, when i tried to create a new record it gave me an error message so i don't know what i am doing wrong, attached is the error message, thank you so much.
    Attached Thumbnails Attached Thumbnails Error message.PNG  

  9. #9
    Join Date
    Jan 2005
    Posts
    146
    That's because you have too many relationships between your tables. You have the table Equipment as both the one side and many side depending on which tables. That just can't happen. Access is stopping you because no matter what you do you have not satisfied your relationships.

  10. #10
    Join Date
    Aug 2013
    Posts
    7

    My project

    thank you for explaining to me, can you demonstrate the right way of linking these tables, thanks for all the help.

  11. #11
    Join Date
    Jan 2005
    Posts
    146
    Turn off referential integrity. This will give you the relationship but not require an entry in each table.
    Attached Thumbnails Attached Thumbnails Relationship.jpg  

  12. #12
    Join Date
    Aug 2013
    Posts
    7

    Need Help!!

    Hi again i tried to restructure all my data base ad this attached file is the one am stuck into, Mu problem is when i create anew record it will create in all tables connected together which is great but when i delete a record it only delete the record from just one of the tables, maybe i have problem with my linking between the tables.
    thanks for all the help
    Attached Files Attached Files

  13. #13
    Join Date
    Jan 2005
    Posts
    146
    Your DB is working correctly. You only have relationships setup with your Site table. That means if you delete a record from ClientProfile it will delete any related record in Site. If you delete a record from ClientAddressType it will delete any related record in Site.

    You don't have a relationship between ClientProfile and ClientAddressType so nothing would happen.

    You can't safely combine tables into a query and use that query for a data entry form. You need to have a main form with subforms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •