Solved

DAO Record Copy

Posted on 2001-07-16
11
566 Views
Last Modified: 2008-11-26
Using VBA, I need a method to copy the last row of a recordset and add it as a new record.  I'm assuming there is a DAO method that can accomplish this.

rst.MoveLast
???

I've tried using rst.clone but I don't want to copy the eniter recordset, I just want to copy the last row.  There is no primary key so duplicates are allowed.
0
Comment
Question by:jbauer22
  • 7
  • 3
11 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 6285830
the easiest way is to run a short action query that does all this for you.

by using the last record's PK in your where clause (criteria) you can be sure you only select one record. or, you can use the VALUES keyword to accomplish the same thing.

can you open a recordset and get to the "last record" or do you need help with that too?

dovholuk
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6285839
you can try:

Private Sub Command0_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim rsClone As Recordset
    Dim i As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblWhatever", dbOpenDynamic, , dbOptimistic )
    If Not rs.EOF Then
        Set rsClone = rs.Clone
        rsClone.MoveLast
        rs.AddNew
        For i = 0 To rs.Fields.Count - 1
            rs(i) = rs.Clone(i)
        Next
        rs.Update
    End If
    Set rsClone = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6285884
here is the code for dovholuk's solution.  If you use this method, please give the points to him, not to me.  I'm just posting an example of how to implement his idea.

    Dim db As Database
    Dim rs As Recordset
    Dim SQL As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblWhatever", dbOpenSnapshot)
    If Not rs.EOF Then
        rs.MoveLast
        SQL = "INSERT INTO tblWhatever SELECT * FROM tblWhatever WHERE KeyID=" & rs("KeyID")
        db.Execute SQL, dbFailOnError
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6285924
well since cjswimmer got the ball rolling... here's what i had in mind.

'************************** BEGIN COPY **************************
currentdb.execute "INSERT INTO TableToInsertInto " _
     & "( Field1, Field2, Field3...Fieldn ) " _
     & "SELECT Field1, Field2, Field3...Fieldn " _
     & "FROM YourTableNameHere " _
     & "WHERE PrimaryKeyField = " & SomeVariableThatHoldsThePrimaryKeyValue

if currentdb.recordsaffected = 0 then
     'no record found raise error.
     msgbox "oopsie"
     exit sub
end if

msgbox "insert successful"
'************************** END COPY **************************

alternatively you could use a recordset. but i'm not quite sure how much help you need...

as always, thank cjswimmer for helping out.

dovholuk
0
 
LVL 6

Accepted Solution

by:
cjswimmer earned 50 total points
ID: 6286430
jbauer22, here's an function that I wrote for you.  I'll describe the arguments below it.

Public Sub DuplicateRecord(ByVal SourceName As String _
                        , ByVal KeyFieldName As String _
                        , ByVal KeyFieldDelim As String _
                        , ByVal OldKeyFieldValue As Variant _
                        , ByVal NewKeyFieldValue As Variant _
                        , ParamArray FieldList() As Variant)
On Error GoTo ErrorHandler
    Dim db As Database
    Dim rs As Recordset
    Dim SQL As String
    Dim strFieldList As String
    Dim strFieldName As String
    Dim i As Integer
    strFieldList = ""
    For i = 0 To UBound(FieldList)
        If FieldList(i) = KeyFieldName Then
            MsgBox "Cannot include the Key Field in the Field List.  " _
                & "Use the specified parameters instead.", vbExclamation, "ERROR"
            Exit Sub
        End If
        strFieldList = strFieldList & IIf(i > 0, ", ", "") & FieldList(i)
    Next
    Set db = CurrentDb
    If IsNull(OldKeyFieldValue) Then
        Set rs = db.OpenRecordset("SELECT " & KeyFieldName & " FROM " & SourceName, dbOpenSnapshot)
        If Not rs.EOF Then
            rs.MoveLast
            OldKeyFieldValue = rs(0)
        End If
        rs.Close
    Else
        If DCount("*", SourceName _
                , KeyFieldName & "=" & KeyFieldDelim & OldKeyFieldValue & KeyFieldDelim) = 0 Then
            Set rs = Nothing
            Set db = Nothing
            MsgBox "Couldn't find the record you specified", vbExclamation, "ERROR"
            Exit Sub
        End If
    End If
    If Len(strFieldList) = 0 Then
        SQL = "SELECT * FROM " & SourceName
        SQL = SQL & " WHERE " & KeyFieldName & "=" & KeyFieldDelim & OldKeyFieldValue & KeyFieldDelim
        Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
        For i = 0 To rs.Fields.Count - 1
            strFieldName = Trim(rs.Fields(i).Name)
            If strFieldName <> KeyFieldName Then
                If InStr(1, strFieldName, " ") Then
                    If Left(strFieldName, 1) <> "[" Then
                        strFieldName = "[" & strFieldName
                    End If
                    If Right(strFieldName, 1) <> "]" Then
                        strFieldName = strFieldName & "]"
                    End If
                End If
                strFieldList = strFieldList & IIf(Len(strFieldList) > 0, ", ", "") & strFieldName
            End If
        Next
        rs.Close
    End If
    SQL = "INSERT INTO " & SourceName & " ("
    If Not IsNull(NewKeyFieldValue) Then
        SQL = SQL & KeyFieldName & ", " & strFieldList & ")"
        SQL = SQL & " SELECT " & KeyFieldDelim & NewKeyFieldValue & KeyFieldDelim
        SQL = SQL & " AS temp" & Format(Now, "mmddyyhhnnss")
        SQL = SQL & ", " & strFieldList
    Else
        SQL = SQL & strFieldList & ")"
        SQL = SQL & " SELECT " & strFieldList
    End If
    SQL = SQL & " FROM " & SourceName
    SQL = SQL & " WHERE " & KeyFieldName & "=" & KeyFieldDelim & OldKeyFieldValue & KeyFieldDelim
    db.Execute SQL, dbFailOnError
    Set rs = Nothing
    Set db = Nothing
Exit Sub
ErrorHandler:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "ERROR"
    Resume
End Sub'***************************************************

SourceName is the table or query you're pulling from
KeyFieldName is the Key Field name in the source
KeyFieldDelim is the delimiter for the key field, can be ""
KeyFieldValue is a value that you can pass to specify a particular record.
         Pass Null if you want to just use the last record from the source
FieldList() is an array of field names that you want to copy.

For FieldList, you can either pass a contructed array, or you can just keep adding to the parameter list to include additional fields.  Lets say for example, you wanted to copy a record from the table 'tblCustomer'.  The primary key field is a text field called CustomerID.  You want to copy the record for the customer id 'Johnson1234' as a new record with the id 'Johnson9876' and you only want to copy the fields 'FirstName', 'LastName' and 'Address'.  You would use:

    Call DuplicateRecord("tblCustomer" _
        , "CustomerID" _
        , "'" _
        , "Johnson1234" _
        , "Johnson9876" _
        , "FirstName", "LastName", "Address")

If you leave the FieldList argument blank, all of the fields will be duplicated.  Let me know if you need more of an explanation and I hope this helps you out.

cjswimmer


0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 6

Expert Comment

by:cjswimmer
ID: 6286459
If there is more than one record that matches the value you pass as the OldKeyFieldValue, then all of the records that match will be copied.  
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6286806
cjswimmer:

do you not like the concatination operator (_) ???

just wondering.

dovholuk
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6286827
heh heh...sorry, I'm working with a 22 inch screen in 1280x1024 so sometimes I forget to reformat the lines to look nice.

0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6286835
if you mean from all the string variable concatenation and not the length of the lines, then you're right.  I do try to stay away from the '_' symbol when building string variables because I can comment out specific lines as I'm debugging if I need to.
0
 
LVL 2

Author Comment

by:jbauer22
ID: 6295287
cjswimmer - Since you put the most effort in I award you the points.  I figured out how to duplicate the last row using a different method.

rstMaestro.Update
Set rstDup = rstMaestro.Clone
rstMaestro.AddNew
       
            For i = 1 To rstMaestro.Fields.Count - 1
            rstDup.MoveLast
            x = rstDup.Fields(i)
            rstMaestro.Fields(i) = x
            Next i

Thanks for all the input.

Jefferson
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6295344
Thanks for then points!  Your code almost matches my first post.  I hope you realize that you are skipping a field in your code though.  The Fields collection is zero based, so by using

   For i = 1 To rstMaestro.Fields.Count - 1

you are skipping the field

   rstDup.Fields(0)

because 'i' will never be zero.

Just thought you should know.

cjswimmer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now