CODEWITHSUNDEEP
ms-accessms-access-2007vba
Chuck
Chuck

Reputation: 1226

Duplicating just one record on import

This may seem like a rather odd question but I would like to duplicate the first record (preferably to the next spot in the database). I want to do this as our clients are asking for samples of our mail merge and it has to be a live file. I currently use a dialog box to import the file and most clients are standard comma delimited .txt files.

Private Sub Command38_Click()
    Dim f As Object
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strUpdate As String
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant
    Dim P As String
    Dim DeleteEverything As String

        DoCmd.SetWarnings False
        DeleteEverything = "DELETE * FROM [tcppltr]"
        DoCmd.RunSQL DeleteEverything

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    f.InitialFileName = "G:\access\TCPP\"
    f.Filters.Clear
    f.Filters.Add " Armored TXT Files", "*.asc"
        If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
            P = strFolder & strFile
            DoCmd.TransferText acImportDelim, "TCPP Import Specification", "tcppltr", P, False
        Next
        End If
End Sub

My first idea was to just have the mail merge print a duplicate of the first record which would be better as we don't need duplicates of the shipping labels and everything else that will come from this record but I wasn't sure there was even a way to do that only for the mail merge without creating a separate table with the duplicate record for use only with the mail merge. That seemed terribly inefficient in my opinion.

I am open to other suggestions on how to do this besides just duplicating a record on import.

Thanks in advance for everyone's time and help in this matter!

Upvotes: 0

Views: 84

Answers (1)

ashareef
ashareef

Reputation: 1846

What this basically does is open two recordsets. The first recordset points to the record you want to duplicate (in this case I did a MoveFirst you can specifically pick whichever record or modify this code to duplicate multiple records). Currently this only copies 1 record.

Dim db As Database
Dim rs1 As Recordset, rs2 As Recordset
Dim i As Long
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Table1", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Table1", dbOpenDynaset)
rs1.MoveFirst
rs2.AddNew
For i = 1 To rs2.Fields.Count - 1
    rs2.Fields(i) = rs1.Fields(i)
Next
rs2.Update
rs1.Close
rs2.Close

If you want an SQL solution I think you need to know before hand the names of all the fields and use an SELECT and INSERT

Upvotes: 1

Related Questions