DAO Record Copy

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.
LVL 2
jbauer22Asked:
Who is Participating?
 
cjswimmerConnect With a Mentor Commented:
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
 
dovholukCommented:
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
 
cjswimmerCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cjswimmerCommented:
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
 
dovholukCommented:
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
 
cjswimmerCommented:
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
 
dovholukCommented:
cjswimmer:

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

just wondering.

dovholuk
0
 
cjswimmerCommented:
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
 
cjswimmerCommented:
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
 
jbauer22Author Commented:
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
 
cjswimmerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.