Solved

DAO Record Copy

Posted on 2001-07-16
11
565 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

708 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

12 Experts available now in Live!

Get 1:1 Help Now