Results 1 to 4 of 4

Updating Tables to prevent the Record Can't Update error

  1. #1
    Join Date
    Jul 2019
    Posts
    16

    Updating Tables to prevent the Record Can't Update error


    In the attached I have updated the database from my previous thread, but am really still having the same issue, just in a different way. In the Sponsor Request Form, I have to update subforms with data from previous subform entries. For example, I enter in Items in the Order Details subform. Then I go to update the Product Details Subform, but have to provide the Order Details (PK #) in order to save the record, or I get the error "You cannot add or change a record because a related record is required in table "Order_Details_Table"" Once I enter the number the record will save successfully. The same thing happens when I enter data in the Ship Details Subform, but it needs the Product Details (PK#).

    Can you please provide guidance on how to resolve.

    Thanks!
    Attached Files Attached Files

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    357
    Well, those relationships won't work. This is what they should look like...
    Click image for larger version. 

Name:	Relationships.png 
Views:	18 
Size:	38.8 KB 
ID:	39273
    Products are attached to Orders and Orders get shipped out. You also need to get rid of the 0 Default Data Types. Not only can't you make relationships but it will break if you an Order with no Product ID, it will not accept 0.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    See if this is what you are wanting to do.

    Warning: your naming scheme is not to best...... so I changed some names. You have (a lot of) spaces in object names. Also you have field names like "Phone#" (should NOT use special characters in object names)
    Autonumber type fields should not be displayed on forms/reports. See Microsoft Access Tables: Primary Key Tips and Techniques

    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


    ***************
    I set the Master/child fields:
    Main form is linked to sfOrder_Details,
    sfOrder_Details is linked to sfProduct_Details and
    sfProduct_Details is linked to sfShip_Details. (at least I hope so.... )


    I use a suffix of "_PK" for primary key fields and "_FK" for foreign key fields.
    I prefix forms with "frm" and sub forms with "sf".



    Is this close??
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    Join Date
    Jul 2019
    Posts
    16
    Thank you all for your assistance and guidance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookUp Error When Updating Record It Calls
    By spartyon in forum Programming
    Replies: 8
    Last Post: 07-03-2019, 02:22 PM
  2. Replies: 2
    Last Post: 04-25-2018, 06:08 AM
  3. error in updating the record in table
    By moises in forum Access
    Replies: 6
    Last Post: 01-06-2015, 02:52 AM
  4. Replies: 2
    Last Post: 01-04-2013, 09:04 PM
  5. Replies: 8
    Last Post: 02-09-2012, 02:02 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
  •  
Tech Forums: Microsoft Office Forums