Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2022
    Posts
    16

    Copy all fields from last record

    I have a database, that for the most part is one giant table that has over 100 fields. I created a main customer page form that has my customer's main data then a bunch of sub-forms, that basically are tied together by a customer ID. I created a copy last record button but all it does is copy the main page record and not the many sub-forms that I have.

    As a work around I can go into the table view and copy the entire record and past it and it does what I need it to do. Then I go in and make some minor changes as 90% of the info needs to be copied.

    Is there a way I can do this with a button? I basically need to copy all the forms, sub-forms.



    Thanks for the help.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You could write a series of sql queries to copy the records. It might look something like this. Seeing as you have over 100 fields in one table this might get kind of hairy. (This is one of many reasons you probably shouldn't have 100 fields in one table)

    Code:
    Public Sub copy_customer(customer_id As Long)
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim qry As String
        
        Set db = CurrentDb
        
        'Build a sql query that selects a record and inserts it again as a new row, exclude the primary key field if it's autonumber
        qry = "INSERT INTO child_table (field_1, field_2, field_3)"
        qry = qry & vbCrLf & "SELECT field_1, field_2, field_3"
        qry = qry & vbCrLf & "FROM child_table"
        qry = qry & vbCrLf & "WHERE child_table.parent_id=" & customer_id
        db.Execute qry, dbFailOnError
        
        'repeat for all your child tables
        qry = "INSERT INTO child_table (field_1, field_2, field_3)"
        qry = qry & vbCrLf & "SELECT field_1, field_2, field_3"
        qry = qry & vbCrLf & "FROM child_table"
        qry = qry & vbCrLf & "WHERE child_table.parent_id=" & customer_id
        db.Execute qry, dbFailOnError
    
    ExitHandler:
        Set db = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description & vbCrLf & vbCrLf & qry, vbInformation, "copy_customer error #" & Err.Number
        Debug.Print "Error on the following query:" & vbCrLf & qry
        Resume ExitHandler
    End Sub

  3. #3
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    One table with 100 fields, 90% of the info copied to the new record? That sounds like bad design. Explain us more about the table.
    Groeten,

    Peter

  4. #4
    Join Date
    Jul 2022
    Posts
    16
    Actually, it's over 240 fields in one giant database. I know it's crazy, but it works so far. I am just trying to get all those fields copied to new record.

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sounds like a spreadsheet copied into a access table.
    Which is extremely likely to be a really bad design with tons of repeating data and no real child tables.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    So the form and subforms are all based on the same table? The subforms are just for different parts/groups of the table's fields?

    Can you post your db?

  7. #7
    Join Date
    Jul 2022
    Posts
    16

    Database

    It has a lot of company info. I will see if I can attach an empty version.

    Yes, the form and sub-forms, are all linked to the same giant table.

    Example Main page is General Customer info like Name, Address, Notes and order information. Then I have 15 sub-forms, that branch off and collect other data but ultimately all the data is stored in the same table. I have an ID that connects them all. It's only a database that I use and don't share.


    I have a copy button on the main page and all it does is create a new record with the data off the main page. I need all the data copied (sub-forms) and all. This way I can go in and edit the few fields that are different. I know if I go to the table itself I can copy and paste the last record and it works. I just want a button that does the same thing. Copies 1 to record 2 and I can edit the difference.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You could try this: here is a subroutine that will try to copy a record. Past this sub either in a code module or in the code of your main page
    Code:
    Sub copy_record(table_name As String, key_field_name As String, primary_key_value As Long)
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim field_names As String
        Dim qry As String
    
        Set db = CurrentDb
        Set tdf = db.TableDefs(table_name)
        
        ' Iterate through each field in the table
        For Each fld In tdf.fields
            ' Exclude primary key field
            If fld.Name <> key_field_name Then
                field_names = field_names & "[" & fld.Name & "], "
            End If
        Next fld
        
        ' Remove the trailing comma
        field_names = Left(field_names, Len(field_names) - 2)
        
        qry = "INSERT INTO " & table_name & " (" & field_names & ")"
        qry = qry & vbCrLf & "SELECT " & field_names
        qry = qry & vbCrLf & "FROM [" & table_name & "]"
        qry = qry & vbCrLf & "WHERE [" & key_field_name & "] = " & primary_key_value
        
        'Inspect the query that we built to make sure it's correct. If it's correct then you can uncomment the db.execute line below
        Debug.Print qry
        
        'uncomment the next line at you're own risk!
        'db.Execute qry, dbFailOnError
        
    ExitHandler:
        Set db = Nothing
        Set tdf = Nothing
        Set fld = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description & vbCrLf & vbCrLf & qry, vbInformation, "copy_record error #" & Err.Number
        Debug.Print "Error on the following query:" & vbCrLf & qry
        Resume ExitHandler
    End Sub
    Then in your button's code you could call it like this

    Code:
       copy_record "customer_table", "customer_id", Me.customer_id
    Then you would add some code after the copy_record to navigate the main form to the new record

  9. #9
    Join Date
    Jul 2022
    Posts
    16

    Attached Database

    I was unable to to do this. I attached an empty version of the database and looking for help? This database is not that great but I don't want to start all over. Any help is much appreciated.
    Attached Files Attached Files

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Well your table "Order Tracking" has numerous things wrong.

    Needs to be normalised
    Has Spaces in Fieldnames
    Has Lookup Fields at Table Level
    Numerous Field's Row Sources are Hard Coded ie "AQVPN";"ADI";"ipflex".....etc
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Join Date
    Jul 2022
    Posts
    16
    Oh no that may be why I get errors.

  12. #12
    Join Date
    Jul 2022
    Posts
    16
    I sent you a message on teams.

  13. #13
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by oldmanbumper View Post
    I sent you a message on teams.
    So you no longer appreciate the help of the other possible helpers?
    Groeten,

    Peter

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    If that message is to I think it is, they regularly offer help outside of the forums.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Unlikely really?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 08-04-2021, 10:02 AM
  2. Replies: 21
    Last Post: 08-28-2018, 02:53 PM
  3. Replies: 2
    Last Post: 09-09-2013, 08:16 AM
  4. Replies: 10
    Last Post: 03-05-2013, 03:10 PM
  5. Replies: 10
    Last Post: 03-21-2011, 02:46 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
  •  
Other Forums: Microsoft Office Forums