Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: copy entire row (record) using recordset

    Hi there,

    I've been looking into using recordsets, but i was wondering whether it is possible to copy an entire row (record) from a table and append to the same table. I would have to look into how you avoid copying all the fields as the first field is a primary key which is set to autonumber.

    I can see how it can be done using sgl but i seems to have to list every single field, which i wanted to avoid if possible as it is time consuming.

    thanks for any help (pointers)
    marcus

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can try this:
    Code:
    Function CopyRow(ByVal Source As String, ByVal Criteria As String, Optional ByVal PKAuto As Variant = 0) As Boolean
    '
    ' Purpose:  Copy one row from a table or query into a new one.
    ' -------
    ' Source:   Can be the name of a table or the name of an updatable query.
    ' ------
    ' Criteria: A string expression for the row to be copied (eg. "Name = 'Smith'").
    ' --------  If several rows match the criteria, only the first found is copied.
    ' 
    ' PKAuto:   The name or the ordinal position (with the first column = 0) of the
    ' ------    Autonumber column that cannot be copied (usually the P.K.).
    '
    ' Returns True in case of success, False otherwise.
    '
        Dim rst As DAO.Recordset
        Dim varData As Variant
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordset(Source, dbOpenDynaset)
        With rst
            .FindFirst Criteria
            If .NoMatch = False Then
                If IsNumeric(PKAuto) = False Then
                    For i = 0 To .Fields.Count - 1
                        If .Fields(i).Name = PKAuto Then
                            PKAuto = i
                            Exit For
                        End If
                    Next i
                End If
                varData = .GetRows
                .AddNew
                For i = 0 To .Fields.Count - 1
                    If i <> PKAuto Then .Fields(i).Value = varData(i, 0)
                Next i
                .Update
                CopyRow = True
            End If
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Are you talking about doing this within a Form that is based on the Table? If so, five simple lines of code will do the job:

    Code:
    If Me.Dirty Then Me.Dirty = False
    
     DoCmd. RunCommand acCmdSelectRecord
     DoCmd. RunCommand acCmdCopy
     DoCmd. GoToRecord , , acNewRec
     DoCmd. RunCommand acCmdPaste


    Simply delete the Space between

    DoCmd. RunCommand

    for each line; it's needed because a feature on this site intended, I believe, to prevent the posting of email addresses.

    BTW, this automatically excludes Autonumber/PKs.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Of course it works, but the topic of the question was: copy entire row (record) using recordset.
    Have a nice day!

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks for your help guys, but i'm still struggling.

    I can't use the runcommand copy / paste method as i have an unbound form that contains a subform based off a query. i tried the code but it said can't go to record at the point " DoCmd. GoToRecord , , acNewRec" ...not sure why?

    The function code provided by Sinndho will probably be the way i'll go as i need to expand on this as i would like to also copy records from other tables which share the same string held in a field called Ref_ID.

    I created a module with the code but wasnt sure what i should put as PKAuto. Do i put the name of the PK field here?
    Not sure how to call the function either sorry for being such a dip


    The Table name is tbl_PSA

    thanks

    marcus

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi there,

    It may be worth telling you what i have to do "manually" to copy files to give you an idea what i'm trying to achieve?

    Here goes...

    Overview.
    It's basically a quotation database. Ideally, I create a quote and send it to the customer and the customer is happy :-) but what normally happen is the customer would like something changed or a bigger discount. So i have to revise the quote. I like to keep a copy of the different versions of quotes so i copy the existing quote and append a /b or /c to the Ref_ID field and update the Issue number in a text field which gets displayed on the final quote to the customer.

    I have a main form called Main which is unbound. Within form Main i have a tab control containing different subforms.

    The first tab call pagPSA contains a subform called Main_sub1 which is a continous form showing all the records from table "PSA"

    Table "PSA" contains a Primary key field called "Ref_ID" which is a text field (not an autonumber which i originally thought). For example 1000/a or 1001/a

    When i click a record in Main_sub1 the "Ref_ID" value is sent to a text field in the main form also called Ref_ID. The Ref_ID in the main form is used as the link master for the various subforms under the other tabs. So when i click on a tab called Customer it will look at the "Ref_ID" string in the main form and pull up the customer details corresponding to the Ref_ID. That's the same for the products i add to the quote. Everytime i add a product it append the Ref_ID number to the record. So when i click on the Tab called Products it calls up all the products matching the Ref_ID in the main form.


    Manual Operation.
    So customer want to revise their quote.

    I have to make a note of the Ref_ID string e.g 1000/a
    I open the table PSA and copy the record 1000/a to a new record.
    i have to give it a unique numner as it's a PK so i up the revision so 1000/b
    I then have to open the other tables ie Customers and copy 1000/a record and paste it as a new record but change the number to 1000/b
    and then open Products and do the same.
    However in products table i have mutiple records with the Ref_ID 1000/a if i have added several items to the quote. (no PK in this table)
    I have to copy and paste each record and update the Ref_ID to 1000/b . Obviously if i have 20+ records this gets very time consuming and open to errors.

    So when i'm finished i open form Main and click on record 1000/b. This will then pull the corresponding info from the various tables depending on which tab/subform i am looking at.

    Maybe i should post this in a new thread but i wanted to start by copying one record and see if i could apply that to other tables.

    Please help, you could save me hours of copying and pasting !!! :-)

    thanks
    marcus

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by marcusmacman View Post
    The function code provided by Sinndho will probably be the way i'll go as i need to expand on this as i would like to also copy records from other tables which share the same string held in a field called Ref_ID.
    If you want to copy from one table to another, you need to use 2 Recordsets (or reuse the same). You also need to pass an additional parameter that contains the name of the destination table:
    Code:
    Function CopyRow(ByVal Source As String, ByVal Destination As String, ByVal Criteria As String, Optional ByVal PKAuto As Variant = 0) As Boolean
    '
    ' Purpose:  Copy one row from a table or query into a new one.
    ' -------
    ' Source:   Can be the name of a table or the name of a query that contains the original data.
    ' ------
    ' Destination: Can be the name of a table or the name of an updatable query in which you want to copy the data.
    ' ------
    ' Criteria: A string expression for the row to be copied (eg. "Name = 'Smith'").
    ' --------  If several rows match the criteria, only the first found is copied.
    ' 
    ' PKAuto:   The name or the ordinal position (with the first column = 0) of the
    ' ------    Autonumber column that cannot be copied (usually the P.K.).
    '           eg. 1 (Int or Long: the Autonumber is in the second column of the table/query.
    '               Primary_Key (String: the name of the Autonumber column is "Primary_Key").
    '
    ' Returns True in case of success, False otherwise.
    '
        Dim rst As DAO.Recordset
        Dim rstDest As DAO.Recordset
        Dim varData As Variant
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordset(Source, dbOpenSnapshot)
        With rst
            .FindFirst Criteria
            If .NoMatch = False Then
                If IsNumeric(PKAuto) = False Then
                    For i = 0 To .Fields.Count - 1
                        If .Fields(i).Name = PKAuto Then
                            PKAuto = i
                            Exit For
                        End If
                    Next i
                End If
                varData = .GetRows
                Set rstDest = CurrentDb.OpenRecordset(Source, dbOpenDynaset)
                With rstDest 
                    .AddNew
                    For i = 0 To .Fields.Count - 1
                        If i <> PKAuto Then .Fields(i).Value = varData(i, 0)
                    Next i
                    .Update
                    .Close
                End With
                CopyRow = True
            End If
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Quote Originally Posted by marcusmacman View Post
    I created a module with the code but wasnt sure what i should put as PKAuto. Do i put the name of the PK field here?
    You can use the name of the PK, or you can use it's ordinal position, with the first column = 0.
    Have a nice day!

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    ok so i created a button and on the onclick even put the following code:-

    Private Sub Command274_Click()
    CopyRow("PSA","PSA", Ref_ID = "10000/a", 0)
    End Sub

    I get a complie error Expected: =

    Source = PSA which is table containing the row i want to copy
    Destination is also PSA
    Criteria say Field "Ref_ID" where string = 10000/a
    and the primary key is ordinal position 0

    could you tell me what i'm missing please?

    thanks
    Marcus

  9. #9
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If you're using the second version of the function, it should be:
    Code:
    Private Sub Command274_Click()
        CopyRow "PSA", "PSA", "Ref_ID = '10000/a'", 0
    End Sub
    Or:
    Code:
    Private Sub Command274_Click()
        If CopyRow ("PSA", "PSA", "Ref_ID = '10000/a'", 0) = True Then
            MsgBox "Row was copied", vbInformation, "CopyRow"
        Else
            MsgBox "Row was not copied", vbInformation, "CopyRow"
        endif
    End Sub
    Moreover, if the primary key is ordinal position 0, you can omit the last (optional) parameter which is 0 by default:
    Code:
    CopyRow "PSA", "PSA", "Ref_ID = '10000/a'"
    Have a nice day!

  10. #10
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    If CopyRow ("PSA", "PSA", "Ref_ID = '10000/a'", 0) = True Then
    MsgBox "Row was copied", vbInformation, "CopyRow"
    Else
    MsgBox "Row was not copied", vbInformation, "CopyRow"
    endif


    i tried this but it will not complie saying expected variable or procedure?

    thanks again for your help
    Marcus

  11. #11
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by marcusmacman View Post
    i tried this but it will not complie saying expected variable or procedure?
    1. Where did you place the function?
    2. Where do you call it from?
    3. Which row does the compiler highlight when it delivers the error message?
    Have a nice day!

  12. #12
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    I placed it in a module and declared it as a Public Function.
    I called it from the Main form using the Onclick event.
    The complier highlights "Private Sub Command83_Click()" with an error saying expected a variable or procedure.

    then I put the code inside the main form and that has done the trick :-)

    I had to change my table slightly and i still need to do a bit of work but it's getting there.

    I had to change the table PSA>field> Ref_ID as this is a primary key field and needs a value. I created a default value in design view and called it 'temp' so when it copies the row and pastes to a new row it automatically has temp in the Ref_ID field.
    I need then to change this to a new value as it obviously doesn't work twice as no duplicates is selected.

    The only problem i can see is if in another table i have more than one record sharing the same Ref_ID number and i want to copy all the rows say with Ref_ID '10000/a' as your comment in the code says it copies only the first record it finds matching the criteria? so that may be a problem,

    thanks for your help
    marcus

  13. #13
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    For dealing with sets (i.e. several rows matching the criteria), using a dynamic SQL solution would be more efficient, specially if there are many rows to be copied:
    Code:
    Public Function SQLCopyRows(ByVal Source As String, ByVal DESTINATION As String, ByVal Criteria As String, Optional ByVal PKAuto As Variant = 0) As Long
    '
    ' Purpose:  Copy one or several rows from a table or query into another one.
    ' -------
    ' Source:   Can be the name of a table or the name of a query that contains the original data.
    ' ------
    ' Destination: Can be the name of a table or the name of an updatable query in which you want to copy the data.
    ' ------
    ' Criteria: A string expression for the row to be copied (eg. "Name = 'Smith'").
    ' --------  If several rows match the criteria, all matching rows are copied.
    '
    ' PKAuto:   The name or the ordinal position (with the first column = 0) of the
    ' ------    Autonumber column that cannot be copied (usually the P.K.).
    '           eg. 1 (Int or Long: the Autonumber is in the second column of the table/query.
    '               Primary_Key (String: the name of the Autonumber column is "Primary_Key").
    '
    ' Returns the numer of rows that were copied.
    '
        Dim rst As DAO.Recordset
        Dim strFldList As String
        Dim strSQL As String
        Dim lngCount As Long
        Dim i As Long
        
        lngCount = DCount("*", Source, Criteria)
    
    ' If at least one row matching the criteria.
    '
        If lngCount > 0 Then
            Set rst = CurrentDb.OpenRecordset(Source, dbOpenSnapshot)
            With rst
    
    ' Build the fields list.
    '
                If IsNumeric(PKAuto) = False Then
                    For i = 0 To .Fields.Count - 1
                        If .Fields(i).Name <> PKAuto Then
                            If Len(strFldList) > 0 Then strFldList = strFldList & ", "
                            strFldList = strFldList & .Fields(i).Name
                        End If
                    Next i
                Else
                    For i = 0 To .Fields.Count - 1
                        If i <> PKAuto Then
                            If Len(strFldList) > 0 Then strFldList = strFldList & ", "
                            strFldList = strFldList & .Fields(i).Name
                        End If
                    Next i
                End If
                .Close
            End With
            Set rst = Nothing
    
    ' Build the INSERT query, execute it and returns the number of row copied.
    '
            strSQL = "INSERT INTO " & DESTINATION & " ( " & strFldList & " ) " & _
                     "SELECT " & strFldList & " FROM " & Source & " WHERE " & Criteria
            CurrentDb.Execute strSQL, dbFailOnError
            SQLCopyRows = lngCount
        End If
        
    End Function
    However , here 's a "pure" VBA solution:
    Code:
    Public Function CopyRows(ByVal Source As String, ByVal DESTINATION As String, ByVal Criteria As String, Optional ByVal PKAuto As Variant = 0) As Long
    '
    ' Purpose:  Copy one or several rows from a table or query into another one.
    ' -------
    ' Source:   Can be the name of a table or the name of a query that contains the original data.
    ' ------
    ' Destination: Can be the name of a table or the name of an updatable query in which you want to copy the data.
    ' ------
    ' Criteria: A string expression for the row to be copied (eg. "Name = 'Smith'").
    ' --------  If several rows match the criteria, all matching rows are copied.
    '
    ' PKAuto:   The name or the ordinal position (with the first column = 0) of the
    ' ------    Autonumber column that cannot be copied (usually the P.K.).
    '           eg. 1 (Int or Long: the Autonumber is in the second column of the table/query.
    '               Primary_Key (String: the name of the Autonumber column is "Primary_Key").
    '
    ' Returns the numer of rows that were copied.
    '
        Dim rstSrc As DAO.Recordset
        Dim rstDst As DAO.Recordset
        Dim varData As Variant
        Dim i As Long
        
        Set rstSrc = CurrentDb.OpenRecordset(Source, dbOpenSnapshot)
        Set rstDst = CurrentDb.OpenRecordset(DESTINATION, dbOpenDynaset)
        With rstSrc
            If IsNumeric(PKAuto) = False Then
                For i = 0 To .Fields.Count - 1
                    If .Fields(i).Name = PKAuto Then
                        PKAuto = i
                        Exit For
                    End If
                Next i
            End If
            .FindFirst Criteria
            Do Until .NoMatch = True
                rstDst.AddNew
                For i = 0 To .Fields.Count - 1
                    If i <> PKAuto Then rstDst.Fields(i).Value = .Fields(i).Value
                Next i
                rstDst.Update
                CopyRows = CopyRows + 1
                .FindNext Criteria
            Loop
            .Close
        End With
        rstDst.Close
        Set rstSrc = Nothing
        Set rstDst = Nothing
        
    End Function
    Have a nice day!

Posting Permissions

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