Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Oct 2006
    Location
    Birmingham, AL
    Posts
    18

    Unanswered: Problem with new records

    When I try to add a new record I get this error message...

    "You cannot add or change a record because a related record is required in table 'XXXXX'."

    Can someone explain this error message to me? I am not sure what to look for in the table that is displayed in the error message.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the table where you are trying to add a record has a foreign key to another table (XXXXX). the new record you want is trying to use a value for the foreign key that does not (yet) exist in table XXXXXX.

    insert the new key entry in table XXXXXX first and everything should be fine.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2006
    Location
    Birmingham, AL
    Posts
    18
    Not sure exactly what this means...

    Could you explain a little more?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it means that the table you are trying to insert into is related to table XXXXX

    open up your relationships diagram and you should see it

    the relationship will be defined on a pair of columns -- one in table XXXXX and the other in the table you are trying to insert into

    what are these columns? what is the value that you are trying to insert? does it exist in the column in the related table? (hint: no)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    example:

    tblManufacturers:
    idManufr, PK
    strManufr

    with data
    1, Ford
    2, Toyota

    tblModels:
    idModel, PK
    idManufr, FK on tblManufacturers
    strModel

    no issue to insert Ford Mustang or Toyota Prius
    but when you try to insert Lamborghini Murcilago you will get your related record error since Lamborghini does not exist in tblManufacturers.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Oct 2006
    Location
    Birmingham, AL
    Posts
    18
    These are great suggestions, but it seems to me like I have everything the way it needs to be. The table responsible for the error message already has the information I am trying to input present, and the related tables also contain the information I am trying to input. Is there any way I could get some further assistance?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dannycolpo
    Is there any way I could get some further assistance?
    yes, there is, but you need to give further information

    start with giving the table layouts (use enterprise manager to script the CREATE TABLE statements), then give some sample data, then give the actual query which received the error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by r937
    ...start with giving the table layouts (use enterprise manager to script the CREATE TABLE statements...
    I'm pretty sure Access doesn't have EM to script table designs. (Unless the newest version has something).
    Inspiration Through Fermentation

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    argh, you are right, geek old friend

    i guess i forgot which forum i was in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    need some data!

    you can go partway to rudy's request with
    Tools/Analyze/Documenter
    ...Tables
    ...Options - choose the middle option in each of the three groups
    ...and GO
    then File/Export ...html will do, no template, and GO, and save, and ZIP, and post.

    it's difficult to believe the related field in XXXXX already exists (otherwise you wouldn't receive the error msg). give one example of the data you are trying to insert (data for any field not allowed null/empty + fieldname (datatype we have from your Documenter efforts)) plus the value of PK for the related record in XXXXX that coresponds to the FK you are using for the insert.

    ?? did you use the dreadful lookup "feature" in your table design?
    (you know the thing - it pretends your foreign keys in tblYYYY are text fields in tblZZZZ but they are really numeric/autonumber). this vicious little "feature" screws-up the thinking of even serious access deviants.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Oct 2006
    Location
    Birmingham, AL
    Posts
    18
    Great, thanks for the suggestions and the offers to help. I'm not very experienced with this, so let me know if I provided everything you have asked for.

    Example:
    Name, Date, Minutes, Application, Volume, Units
    Field, 11/30/2006, 240, Fescue seed, 100, #

    PK:
    ID
    22

    I may have omitted some pertinent info in my original post. It wasn't a query that invoked the error message; it occurs when I try to enter my data into a form and save the record by leaving the form. Does that make a difference?
    Attached Files Attached Files

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what does the # in units stand for
    is that your Fk to another table
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry looked at your objects and simply put dont understand how they relate to the problem you are reporting

    Id suggest post the db, or the part of the db that is causing problems

    incidentally i'd be carefull how you name your columns. in an ideal world the name should be meaningfull to humans and not use SQL reserved words..
    t may not be a problem in Access/JET but it could bite you big time later on

    Im thinkingg specificaly of Date
    Id also steer clear of generic terms like ID... its the ID of what?

    incidentally is that table design correct.. you apply 4 spearate chemicals/items as a single treatement?, and those 4 items vary.

    for me personally Id like to see your relationships in the grahpical form (tools | relationships), id like to see your table design as per Access, and Id like to see some sample data, showing how the child table references the parent table(s).

    But thats probably becasue Im from the stone age, unlike these young superstars like R937 amongst others
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    young? me? i'm pushing 60
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by r937
    young? me? i'm pushing 60
    at least you aren't pulling it

    as for the question you said you get this error when exiting a form, are you tryies to exit the form without completing the record?
    Last edited by m.timoney; 01-10-07 at 11:20.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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