none
How to Duplicate Records in Access 2010 RRS feed

  • Question

  • I know that mostly everyone wants to remove duplicates but I need to copy them while changing certain fields. For example:

    Phone Number Contract Months Due Month

    123546789 18 31/12/2011

    As this contract is for 18 months, I want to create 18 lines with the same information, just changing the 'due month' on each record to one month later.. So for the example above, i would need 18 lines with the following dates : 31/12/2011, 30/01/2012, 28/02/2012, etc 

    Is this possible?

    Monday, January 21, 2013 10:09 AM

All replies

  • I'd use an unbound form on which the user can enter phone number, contract months and first due month in text boxes named for example txtPhoneNumber, txtContractMonths and txtDueMonth.

    Place a command button cmdAddRecords on the form, with the following On Click event procedure:

    Private Sub cmdAddRecords_Click()
        Dim lngYear As Long
        Dim lngMonth As Long
        Dim lngContractMonths As Long
        Dim i As Long
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        ' Make sure that phone number has been filled in
        If IsNull(Me.txtPhoneNumber) Then
            Me.txtPhoneNumber.SetFocus
            MsgBox "Please enter a phone number.", vbExclamation
            Exit Sub
        End If
        ' Make sure that duration has been filled in
        If IsNull(Me.txtContractMonths) Then
            Me.txtContractMonths.SetFocus
            MsgBox "Please enter the duration of the contract.", vbExclamation
            Exit Sub
        End If
        ' Make sure that first due month has been filled in
        If IsNull(Me.txtDueMonth) Then
            Me.txtDueMonth.SetFocus
            MsgBox "Please enter the first due date.", vbExclamation
            Exit Sub
        End If
        lngContractMonths = Me.txtContractMonths
        lngYear = Year(Me.txtDueMonth)
        lngMonth = Month(Me.txtDueMonth)
        ' Open recordset on the table
        Set dbs = CurrentDb
        ' Change name of table as needed
        Set rst = dbs.OpenRecordset("tblPhonePlan", dbOpenDynaset)
        ' Create series of records in a loop
        For i = 1 To lngContractMonths
            rst.AddNew
            ' Change the name of the fields as needed
            rst![Phone Number] = Me.txtPhoneNumber
            rst![Contract Months] = lngContractMonths
            rst![Due Month] = DateSerial(lngYear, lngMonth + i, 0)
            rst.Update
        Next i
        ' Clean up
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
    End Su


    Regards, Hans Vogelaar

    Monday, January 21, 2013 10:37 AM
  • Hi, 

    I don't think I have explained myself very well. I already have a table with thousands of unique records with phone numbers, contract months and dates, and I don't need to new add records. I need a way to multiply each record depending on the contract month (so if it is a 24 month contract I need to multiply the record 23 times) and automatically calculate the due date some how.

    Thanks

    Monday, January 21, 2013 11:41 AM
  • We'll need to store the records in a new table.

    Start by copying the current table then pasting it. Select the option to paste the structure only, not the records.

    Then run the following code, after changing the names of the tables and fields to the ones you have.

    Private Sub cmdAddRecords_Click()
        Dim strPhoneNumber As String
        Dim lngYear As Long
        Dim lngMonth As Long
        Dim lngContractMonths As Long
        Dim i As Long
        Dim dbs As DAO.Database
        Dim rst1 As DAO.Recordset
        Dim rst2 As DAO.Recordset
        ' Open recordset on the table
        Set dbs = CurrentDb
        ' Change name of table as needed
        Set rst1 = dbs.OpenRecordset("tblPhonePlan", dbOpenForwardOnly)
        Set rst2 = dbs.OpenRecordset("Copy of tblPhonePlan", dbOpenDynaset)
        Do While Not rst1.EOF
            If Not IsNull(rst1![Phone Number]) And Not IsNull(rst1![Contract Months]) _
                    And Not IsNull(rst1![Due Month]) Then
                strPhoneNumber = rst1![Phone Number]
                lngContractMonths = rst1![Contract Months]
                lngYear = Year(rst1![Due Month])
                lngMonth = Month(rst1![Due Month])
                ' Create series of records in a loop
                For i = 1 To lngContractMonths
                    rst2.AddNew
                    rst2![Phone Number] = strPhoneNumber
                    rst2![Contract Months] = lngContractMonths
                    rst2![Due Month] = DateSerial(lngYear, lngMonth + i, 0)
                    rst2.Update
                Next i
            End If
            rst1.MoveNext
        Loop
        ' Clean up
        rst1.Close
        Set rst1 = Nothing
        rst2.Close
        Set rst2 = Nothing
        Set dbs = Nothing
    End Sub


    Regards, Hans Vogelaar

    Monday, January 21, 2013 1:27 PM
  • I'm afraid I'm not very good at vba, and I can't seem to get that code to work.

    I have managed to duplicate the lines by using a query based on the below link, however but now I am stuck on the due date issue, so if you could help with that I would greatly appreciate it

    http://www.dbforums.com/microsoft-access/1619009-help-creating-duplicate-rows.html

    Monday, January 21, 2013 4:27 PM
  • Following the ideas from that thread, you should create a table DueMonths, with a single date/time field DueMonth.

    Fill this table with a list of the last day of each month from, say, 31-Jan-2013 to 31-12-2020.

    Use that instead of the extra table mentioned in the replies in the DBForums thread.


    Regards, Hans Vogelaar

    Monday, January 21, 2013 11:57 PM