Results 1 to 12 of 12
  1. #1
    sarahc25 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Yorkshire, UK
    Posts
    5

    Getting table records to copy to another table

    Hi,

    I'm fairly new to Access building and i've come across a problem that i cannot find a resolution to. Here goes...

    I'm trying to create a command button that will 'copy' a record from one table to another
    (e.g. tblQuote record to tblServiceContract Record*new)

    I've tried an append query but this doesn;t work as my tblServiceContract has a unique primary ID which i need to keep.
    There are only certain fields from Quotes that i need to use to create the new record in ServiceContacts and i keep getting key validation error whatever i try. Can anyone please advise??

    I've also tried the code below:
    Private Sub Command103_Click()
    'No value entered
    If IsNull(Me.SQNumber) Or Me.SQNumber = "" Then
    MsgBox ("Is null or empty")
    Else
    Dim entry As String
    entry = Me.SQNumber
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM TblQuotes1 WHERE(contractconfirmed = true)")
    Set rs2 = db.OpenRecordset("TblServiceAgreements1")
    With rs2
    .AddNew
    .Fields("Frequency") = rs!Frequency
    .Fields("SQNumber") = rs!SQNumber
    .Fields("Customer_ID") = rs!Customer_ID
    .Fields("Price") = rs!Price
    .Fields("Site_ID") = rs!Site_ID
    .Fields("CoordinatorID") = rs!CoordinatorID
    .Fields("Manhours") = rs!Manhours


    .Fields("Region_ID") = rs!Region_ID
    .Update
    .Close
    End With
    rs.Close

    End If
    End Sub


    Still Learning................

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    and i keep getting key validation error whatever i try.
    Do you have Relationships defined in the Relationships Window of your Access db? I am not perfectly familiar with the error you are getting, but I would guess it has something to do with a relative record not existing in a parent table. Did you create a referential integrity rule in the Relationships Window that says there must be a related value in another table.

    I would expect a different error message if it was a duplicate index issue.

    Do you have multiple Primary Keys defined in your TblServiceAgreements1?

    I would make sure that the Primary Key in your TblServiceAgreements1 is assigned a value. So make sure it is an Autonumber type or you assign it an unique value.

    Also, look at your Relationships Window and understand any rules you defined for the following columns ...
    Region_ID
    Site_ID
    Customer_ID
    CoordinatorID

  3. #3
    sarahc25 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Yorkshire, UK
    Posts
    5
    thanks ItsMe but i have all my relationships defined and the ID fields listed in the codes all are all 'child' with relationships to the main tables. I tried deleting the ID fields from the code to copy but it still didn't work.
    I've screen shot the error
    Click image for larger version. 

Name:	error.png 
Views:	16 
Size:	237.7 KB 
ID:	23240

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    but i have all my relationships defined and the ID fields listed in the codes all are all 'child' with relationships to the main tables
    Right, when you define relationships in the relationships window and define constraints that enforce referential integrity, you need to make sure that an .Update does not violate a Constraint.

    Aside from that, the message that you show in the screenshot explains that you are creating duplicate values in an indexed field that does not allow for duplicates. It is likely this message is because one of your Foreign Key Fields in your table has a Property setting that indicates, Indexed = Yes (No Duplicates). You can view the properties of your fields in Design View of your table.
    ...message if it was a duplicate index issue.

    Do you have multiple Primary Keys defined in your TblServiceAgreements1?

    I would make sure that the...

  5. #5
    PC-Gram is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Location
    Denmark
    Posts
    5
    First of all: Make sure that
    Set rs = db.OpenRecordset("SELECT * FROM TblQuotes1 WHERE(contractconfirmed = true)")
    actually retrieves at least one record. Otherwise the system will try to append a null record and that may give you this error.
    (comment .update out and add somthing like msgbox rs!Frequency)

    Next: Try to accomplish the task manually

    Thirdly I prefer to add records using SQL only like in: DoCmd.RunSQL "INSERT INTO TblServiceAgreements1 .....etc"
    Before I actually run this sentence I would write the SQL-sentence to some output field in my form. Then I would copy-paste it into the SQL-part of a query and try to run the query to make sure it does the trick.

  6. #6
    sarahc25 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Yorkshire, UK
    Posts
    5
    Thank you both for your replies. I think i am basiclly trying to do an append sql query to move a recordsource from Quotes to Service Contracts, but i'm still coming across the the Key Error. I have Primary Keys in both tables as ID fields which are locked as unique ( no duplictes), which really need to keep to ensure intergrity of the recordsets if i changed these to no but kept them as autonumber would it harm the db? it seems to be the only way to solve the issue at the moment.
    Click image for larger version. 

Name:	error2.png 
Views:	14 
Size:	244.6 KB 
ID:	23242
    Thanks

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    but i'm still coming across the the Key Error.
    That error is a different error. Each time you post an issue/error, it is a different issue/error.
    if i changed these to no but kept them as autonumber would it harm the db?
    Most tables in Access will have ONE primary key. In Access, it is possible to assign more thane one column as a Primary Key. Adding multiple Primary Keys to a single table will cause Access to behave a specific way. This may not be desirable.

    Right now, I do not know what advice to offer because you are approaching this with the shotgun approach and I do not know what the current issue is. I suggest you stay with one topic and do your best to answer questions specific to said issue/topic before moving on to another issue/topic.

  8. #8
    PC-Gram is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Location
    Denmark
    Posts
    5
    It's very common to insert records in a table with a primary key field with auto numbering and when doing so you must never try to assign any value to that key field. The system will take care of that.
    So I recommend you try the following
    1) Open TblServiceAgreements1 and insert the desired record manually
    2) Do the same thing but using using a query in the SQL-view "insert into TblServiceAgreements1 (field,field,field) values (value,vaue,value)"
    3) when 1) & 2) works either continue with your recordset/addNew approach or use doCmd.RunSQL

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Instead of 'moving' records between tables, could you just have a field to indicate record status? Or maybe just save the QuoteID as foreign key in the Contracts table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    sarahc25 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Yorkshire, UK
    Posts
    5
    ItsMe - i'm sorry if appear to be coming across as a shotgun approach but i was simply trying to explain my error as i understood it. I tried to build the sql as suggested by PC-gram to see if it was my coding that was causing an issue and the best way i see of doing so was through a query sql language ( as it's not something i'm wholly familiar with as a complete novice).

    PC-Gram - thanks again but i was trying to avoid manual update as the two tbls are used by different departments (via forms) and something could get 'lost' in manual updates. Will try though.

    June7 - both tables serve different functions even though they contain some similar fields and if PC-Grams suggestion doesn't work i may have to re-visit.

    Thanks all - i'll go away and do more learning.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by sarahc25 View Post
    ...
    Thanks all - i'll go away and do more learning.
    Nobody is asking you to or wants you to go away. For me, I was having difficulty understanding your questions because I was still trying address the previous. I prefer to address one issue at a time. That's all.

  12. #12
    sarahc25 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Yorkshire, UK
    Posts
    5
    Quote Originally Posted by ItsMe View Post
    Nobody is asking you to or wants you to go away. For me, I was having difficulty understanding your questions because I was still trying address the previous. I prefer to address one issue at a time. That's all.
    Thanks i only meant to try and learn so i can narrow down my requests for help to make all our lives easier- i'll be back soon

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

Similar Threads

  1. copy records within the same table
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 01-02-2012, 10:33 PM
  2. Replies: 9
    Last Post: 12-19-2011, 06:30 PM
  3. Replies: 1
    Last Post: 12-18-2011, 01:52 AM
  4. Replies: 3
    Last Post: 10-24-2011, 11:42 AM
  5. Replies: 2
    Last Post: 04-02-2010, 07:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums