Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Posts
    43

    Exclamation Unanswered: help!!!adding a record to multiple table?

    i used a query but when i use insert into there is an error it is not adding record?
    what should i do? i have 3 tables with foreign key on it so some attributes are the same...

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi

    Sounds like your trying to udate a recordset using a query where the table setup in a relationship.

    If you have to seach for a record and then add to that recordset use a the query BUT use a small subform to enter the data in to the tables in the format you want -...

    gareth

  3. #3
    Join Date
    Jun 2004
    Posts
    43
    sir i can't update,insert or delete record because of those relationships... what should i do?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe this simple example helps:

    tblManufacturers:
    IDmanuf, PK
    strManuf, text

    contains data:
    1, "Ford"
    2, "Renault"



    tblModels:
    IDmodel, PK
    IDmanuf, FK foreign key looking at tblManufacturers
    strModel, text

    contains data:
    1, 1, "Escort"
    2, 1, "Focus"
    3, 2, "Espace"
    4, 1, "Mustang


    if you enforce referential integrity, you CANNOT insert "Chrysler Voyager" until you have already defined "Chrysler" in tblManufacturers.
    the impossible record 5 would look like:
    5, ?, "Voyager"
    which is NOT allowed. the value of your foreign key IDmanuf MUST exist in tblManufacturers BEFORE you can insert the record.

    if you do not enforce referential integrity, you can (but must NOT) insert Chrysler Voyager using:
    5, 3, "Voyager"
    ...and your database is immediately corrupt. the foreign key "3" has no meaning, and you cannot recover the manufacturer's name.

    so, before INSERT, make sure that all your foreign key values already exist... if necessary, you insert the foreign keys first (in the above example, after you have inserted "Chrysler" into tblManufacturer, you can then insert "Chrysler Voyager" into tblModels

    for DELETE/UPDATE (with referential integrity enforced):

    you can DELETE any entry in tblModels... it does not hurt referential integrity

    you cannot DELETE an entry in tblManufacturers if it is already being used as a foriegn key (i.e. in the above data, you cannot delete "Ford" because there are Ford cars in tblModels) ...UNLESS... you cascade deletes: with cascaded deletes, all Ford models will be deleted from tblModels if you delete "Ford". (cascaded deletes & cascaded updates can be very dangerous - in most cases it is safer to disallow these "features")

    you can UPDATE strManuf in tblManufacturers. updating "Ford" to "Ford Motor Co" does no harm.

    you cannot UPDATE the second record in tblModels from:
    2, 1, "Focus"
    to:
    2, 3, "Focus"
    because there is no "3" manufacturer.

    does this make sense?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2004
    Posts
    43
    its not possible for me i placed 3 tables in 1 form, and i can't seperate this because i need to have only one form to add

    customer (txtcustomerid,txtfirstname,txtlastname,txtmiddlen ame);"
    contacts (txtfirstname,txtaddress,txtlastname,txtmiddlename ,txttelno);"
    location (txtaddress,txtzip);"

    is this table normalize? or im wrong?

    this are my table and i need all the attribute in adding customer...
    Last edited by oeuf85; 06-19-04 at 14:16.

  6. #6
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi
    ( we have to keep this simple i guess)

    I think you may need to put the result of your seach screen in 1 form as it opens. embedded in this screen could be a subform within a subform - that is a basic good practice.

    That way you can have all the related table information open in one form format

    if you need examples put what you have in here as an attachmentv and im sure we'll sort it out for you


    gareth

  7. #7
    Join Date
    Jun 2004
    Posts
    43
    attachment
    Attached Files Attached Files
    Last edited by oeuf85; 06-20-04 at 00:42.

  8. #8
    Join Date
    Jun 2004
    Posts
    43
    help.... pls edit the attachment, i cant add a record

  9. #9
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Ive looked at the DB your replication 3 tables - why???

    Your expecting to use an append to place the same data in 3 tables - NOT GOOD

    one table with names one with loactions (or addresses) is ok the other is the back details - Thats right

    The back details can be attached to the customer name (id needed) that one you'll have more than one person at the address with that account if needed (shared or joint account)

    YOUR PROBLEM is that query........

    Place a form with the name - addresss - account details if requiremed use a subform within a subform to place the address then account details
    gareth
    Last edited by garethfx; 06-21-04 at 15:09.

Posting Permissions

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