Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DAO Record Copy

Posted on 2001-07-16
11
Medium Priority
?
578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

715 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