Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2016
    Posts
    3

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

    My Database was working OK but I needed to chase some of my relationships to enforce data integrity as my report wasn't pulling of the names correctly just the ID number now I'm unable to add new data.

    Can anyone help me please?

    I've attached the sheet below with dummy data on it.

    Help Desk V3.1.zip

    Many Thanks for any help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what changes did you make?
    What do you think the error message is telling you?

    Is it possible that the changes you have made are the root cause of the reported problem. If so, and just taking a wild guess here, you have two options here
    ... revert back to your previous design and put up with the data errors
    OR
    ... make certain that data in the foreign keys pre exists in the referenced table where it will be a prinary key. Ferinstance table orders has the customer ID in it, a relationship defined to table customers. The primary key in customers is refernced as the foreign key in orders. This means that the column type should be the same (unless you use autonumber in customers in which case the datatype in orders is long integer). To record a customer order you first need to have created a customer row in table customers. But bear in mind if you use a combo or list box containing the customer details in your order form and that form is open when you create a new customer you need to refresh /requery the combo/list box containing customer details in the irder form to use the 'see' the new customer.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2016
    Posts
    3
    The only changes I made was to make all my joining records Referential Integrity.

    I don't really know what it trying to tell me, sounds like its trying to change a record in tEvent_type table but I don't want anything to change in this table and I've not asked it to change any data here.

    This is my first time working with a database and I'm in at the deep end I feel so appreciate any simple terms to help me fix this issue.

    I guessed the changes have caused it and I though about going back but as I need the data to show in the report it not a very good option for me!

    I went though and changed all my datatypes to long integer from integer but still same issue.

    Thank you for your time and patience.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    When you define a realtionship using RI then before you can insert something in the 'child' table then there must be a row with the same value in the 'parent' table. Your error message is telling you there isn't. I gave up looking at your db with the mussing files and other stuff.

    Its down to how you have defined the relationships AND how you insert the data. My suspuscion is that now that you have defined RI relątionship the insert is failing because you are not providing information now required. It could be that, ferinstance when inserting a row there is no userid in the parent table
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2016
    Posts
    3
    Quote Originally Posted by healdem View Post
    When you define a realtionship using RI then before you can insert something in the 'child' table then there must be a row with the same value in the 'parent' table. Your error message is telling you there isn't. I gave up looking at your db with the mussing files and other stuff.

    Its down to how you have defined the relationships AND how you insert the data. My suspuscion is that now that you have defined RI relątionship the insert is failing because you are not providing information now required. It could be that, ferinstance when inserting a row there is no userid in the parent table
    Sorry if my database is confusing its my first time making one! I really appreciate your assistance,

    I only thing in my "tEvent_type" table is Ref No. for the Event and then a Drop down-list for my combo box.

    So when I submit a fault/event it gives me the ref-no. wish is what I want but when I run a report I want it to tell me what the Ref no is.

    All the values in "tNotes" are taken from "tEvent_type" so there all definitely in that table.

Posting Permissions

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