Solved

DAO Record Copy

Posted on 2001-07-16
11
569 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

791 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