none
How to Create Duplicate Records while Incrementing in another attribute? RRS feed

  • Question

  • Hello,

    I do have some issues with trying to add duplicate records and increment them simultaneously.

    What I want to perform is the following:

    ID Num,      Record Description

    1                John

    2                Jack

    3                Phil

    4                Jill

    5                John

    6                Jack

    7                Jill

    8                Phil 

    Note that the records would be duplicated while IDNum attribute continues to increase by 1.

    Should I perform a SQL query, VBA coding, or both?

    If it is possible, then can anyone provide me some examples of any code snippets?

    Regards,

    JohnDBCTX

      


    jp

    Saturday, August 26, 2017 4:43 AM

Answers

  • The easiest way to do this would be to make IDNum an AutoNumber field; Access will automatically increment it for each new record. You could create a command button cmdDuplicate with an On Click event procedure like this:

    Private Sub cmdDuplicate_Click()
      On Error GoTo ErrHandler
      If IsNull(Me.IDNum) Then
        Exit Sub
      End If
      If Me.Dirty Then Me.Dirty = False RunCommand acCmdSelectRecord RunCommand acCmdCopy RunCommand acCmdPasteAppend Exit Sub ErrHandler: If Err <> 2501 Then MsgBox Err.Description, vbExclamation End If End Sub

    If you prefer IDNum not to be an AutoNumber field, you have to do a bit more work:

    Private Sub cmdDuplicate_Click() On Error GoTo ErrHandler If IsNull(Me.WijnID) Then Exit Sub End If
    If Me.Dirty Then Me.Dirty = False RunCommand acCmdSelectRecord RunCommand acCmdCopy RunCommand acCmdPasteAppend ' Set the new IDNum Me.IDNum = DMax("IDNum", "NameOfTable") + 1 Exit Sub ErrHandler: If Err <> 2501 Then MsgBox Err.Description, vbExclamation End If End Sub


    where NameOfTable is the name of the table that contains IDNum.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Monday, August 28, 2017 5:38 AM
    • Marked as answer by JohnDBCTX Tuesday, August 29, 2017 2:06 AM
    Saturday, August 26, 2017 9:24 AM
  • You'll find an example of how to duplicate records, amongst other things, in Defaults.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to copy the current record is really intended to show how to do so, while at the same time duplicating the rows in a related table.  In your case you are not interested in the latter, so the code can be shortened to:

    Private Sub cmdDuplicateRecord_Click()

        Dim lngNewEmployeeID As Long
        Dim strSQL As String
        
        ' ensure current record is saved
        Me.Dirty = False
        
        ' get next EmployeeID value
        lngNewEmployeeID = DMax("EmployeeID", "Employees") + 1
        
        ' insert new row into Employees table
        strSQL = "INSERT INTO Employees" & _
            "(EmployeeID,FirstName,LastName,Department,City,DateAppointed) " & _
            "SELECT " & lngNewEmployeeID & ",FirstName,LastName,Department,City,DateAppointed " & _
            "FROM Employees " & _
            "WHERE EmployeeID = " & Me.EmployeeID
            
        CurrentDb.Execute strSQL, dbFailOnError
        
        ' navigate to new record
        With Me.RecordsetClone
            .FindFirst "EmployeeID = " & lngNewEmployeeID
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
        
    End Sub

    You'll see that the next EmployeeID value in sequence is first determined by calling the DMax function, and adding 1.  The new value is then inserted into the duplicate record.  It is immaterial whether the EmployeeID value is an autonumber (as in my demo) or a straightforward long integer number data type, its value will be incremented by 1 regardless.

    Ken Sheridan, Stafford, England

    • Marked as answer by JohnDBCTX Tuesday, August 29, 2017 2:06 AM
    Saturday, August 26, 2017 12:34 PM
  • Of any of the database management software that I have mentioned previously, which ones would enable users to have more capability?
    That's far too vague a question to answer.  You started this thread with a question about a simple task which is easily accomplished in Access.  You then followed this up with an extension of the original task, which is again easily accomplished in Access.  In neither case do you need to use anything but a native Access .accdb file as the back end.

    If you wish to broaden your question as which product would best suit your requirements as a back end to an Access front end, then start a new thread and give full details of your business requirements.  

    I am now discontinuing monitoring of this thread.


    Ken Sheridan, Stafford, England

    • Marked as answer by JohnDBCTX Tuesday, August 29, 2017 2:05 AM
    Monday, August 28, 2017 11:00 PM

All replies

  • The easiest way to do this would be to make IDNum an AutoNumber field; Access will automatically increment it for each new record. You could create a command button cmdDuplicate with an On Click event procedure like this:

    Private Sub cmdDuplicate_Click()
      On Error GoTo ErrHandler
      If IsNull(Me.IDNum) Then
        Exit Sub
      End If
      If Me.Dirty Then Me.Dirty = False RunCommand acCmdSelectRecord RunCommand acCmdCopy RunCommand acCmdPasteAppend Exit Sub ErrHandler: If Err <> 2501 Then MsgBox Err.Description, vbExclamation End If End Sub

    If you prefer IDNum not to be an AutoNumber field, you have to do a bit more work:

    Private Sub cmdDuplicate_Click() On Error GoTo ErrHandler If IsNull(Me.WijnID) Then Exit Sub End If
    If Me.Dirty Then Me.Dirty = False RunCommand acCmdSelectRecord RunCommand acCmdCopy RunCommand acCmdPasteAppend ' Set the new IDNum Me.IDNum = DMax("IDNum", "NameOfTable") + 1 Exit Sub ErrHandler: If Err <> 2501 Then MsgBox Err.Description, vbExclamation End If End Sub


    where NameOfTable is the name of the table that contains IDNum.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Monday, August 28, 2017 5:38 AM
    • Marked as answer by JohnDBCTX Tuesday, August 29, 2017 2:06 AM
    Saturday, August 26, 2017 9:24 AM
  • You'll find an example of how to duplicate records, amongst other things, in Defaults.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to copy the current record is really intended to show how to do so, while at the same time duplicating the rows in a related table.  In your case you are not interested in the latter, so the code can be shortened to:

    Private Sub cmdDuplicateRecord_Click()

        Dim lngNewEmployeeID As Long
        Dim strSQL As String
        
        ' ensure current record is saved
        Me.Dirty = False
        
        ' get next EmployeeID value
        lngNewEmployeeID = DMax("EmployeeID", "Employees") + 1
        
        ' insert new row into Employees table
        strSQL = "INSERT INTO Employees" & _
            "(EmployeeID,FirstName,LastName,Department,City,DateAppointed) " & _
            "SELECT " & lngNewEmployeeID & ",FirstName,LastName,Department,City,DateAppointed " & _
            "FROM Employees " & _
            "WHERE EmployeeID = " & Me.EmployeeID
            
        CurrentDb.Execute strSQL, dbFailOnError
        
        ' navigate to new record
        With Me.RecordsetClone
            .FindFirst "EmployeeID = " & lngNewEmployeeID
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
        
    End Sub

    You'll see that the next EmployeeID value in sequence is first determined by calling the DMax function, and adding 1.  The new value is then inserted into the duplicate record.  It is immaterial whether the EmployeeID value is an autonumber (as in my demo) or a straightforward long integer number data type, its value will be incremented by 1 regardless.

    Ken Sheridan, Stafford, England

    • Marked as answer by JohnDBCTX Tuesday, August 29, 2017 2:06 AM
    Saturday, August 26, 2017 12:34 PM
  • I have tried your demo, and it was very complex.

    Okay.  What if I have tried the following SQL statement below?

    INSERT INTO tblNames ( ID, FirstName )
    SELECT Max(tblNames.ID)+4 AS MaxOfID, tblNames.FirstName AS FName
    FROM tblNames
    GROUP BY tblNames.ID, tblNames.FirstName;
    

    Notice that the value, "4", would add those duplicate records while the ID field increments by 1 each from 4 to 8.

    ID	FirstName
    1	John
    2	Jack
    3	Jill
    4	Phil
    5	John
    6	Jack
    7	Jill
    8	Phil

    But what if a user wants to duplicate the same records but this time up to ID count of 12 just to generate the output when a user clicks by way of a command button?

    ID	FirstName
    1	John
    2	Jack
    3	Jill
    4	Phil
    5	John
    6	Jack
    7	Jill
    8	Phil
    9	John
    10	Jack
    11	Jill
    12	Phil

    The only problems I have tried are the following:

    Using the DMax, DCount, DMin, Max, Count, and Min reserved aggregate functions.

    All of them which I supplement the value, "4" did not work correctly.

    How could I properly supplement numerical values with a running calculation such as the following SQL code snippet below?

    INSERT INTO tblNames ( ID, FirstName )
    SELECT Max(tblNames.ID)+[running calculation to replace numerical value] AS MaxOfID, tblNames.FirstName AS FName
    FROM tblNames
    GROUP BY tblNames.ID, tblNames.FirstName;
    
    In brackets
    running calculation to replace numerical value

    would refer to multiples of total of original records while they duplicate set of first names.

    Regards,

    JohnDBCTX



    jp

    Monday, August 28, 2017 6:54 AM
  • How could I properly supplement numerical values with a running calculation such as the following SQL code snippet below?
    INSERT INTO tblNames ( ID, FirstName )
    SELECT Max(tblNames.ID)+[running calculation to replace numerical value] AS MaxOfID, tblNames.FirstName AS FName
    FROM tblNames
    GROUP BY tblNames.ID, tblNames.FirstName;
    In brackets
    running calculation to replace numerical value

    would refer to multiples of total of original records while they duplicate set of first names.


    A solely SQL solution like that would require recursive querying, which JET/ACE SQL does not support.  It also assumes that the primary key values are an unbroken series of integer values starting at 1, which would be a rash assumption unless  steps have been taken to generate an unbroken sequence, including the updating of the key values in the event of a row being deleted.

    A code solution on the other hand, which loops through a recordset of the *initial* rows in the table three times and inserts one row at each iteration of the loop, would be simple.  The key could be an autonumber, or computed at each iteration of the loop to ensure maintenance of an unbroken sequence.


    Ken Sheridan, Stafford, England

    Monday, August 28, 2017 10:38 AM
  • I have some books on other types of SQL that does not support JET/ACE database drive.

    Should I need to migrate to SQL Server Express?

    The only problem is that I may need to learn more about its enhanced governed syntax structures.

    That way, I can perform running calculations quickly, so I do not need to perform any VBA coding.

    Regards,

    JohnDBCTX


    jp

    Monday, August 28, 2017 11:24 AM
  • Should I need to migrate to SQL Server Express?
    I don't know what recursive querying functionality SQL Server Express supports; we used Oracle in my organisation.  Recursive querying is not trivial, however, and to move to a flavour of SQL which supports it, merely for what you are attempting, would be taking a sledgehammer to crack a nut.  For what you want to do standard Access is perfectly adequate, and the VBA code necessary to do it is very simple.  Why are you so averse to using VBA to build and execute SQL statements?

    Ken Sheridan, Stafford, England

    Monday, August 28, 2017 12:00 PM
  • Well,

    Since I am increasingly intrigued, I found out Access can import different types of data, including the Oracle extension your organization uses ODBC (Oracle DataBase Connectivity)

    Here is a screenshot of the dialog below:

    So I have understood you that recursive calculations, such as running calculations, are not commonplace. In other words, it is not that important. Therefore, it may not be easy, primarily, for any user to perform recursive calculations using any type of SQL.  I am likely to learn how to write procedures using either SQL Server Express version, Oracle, or MySQL ( a subsidiary app of Oracle ).  

    Of any of the database management software that I have mentioned previously, which ones would enable users to have more capability?

    Regards,

    JohnDBCTX

    This dialog box displays the following choices - Oracle, Paradox, DBase, SQL Server other than Access file extensions by default.


    jp

    Monday, August 28, 2017 6:58 PM
  • Of any of the database management software that I have mentioned previously, which ones would enable users to have more capability?
    That's far too vague a question to answer.  You started this thread with a question about a simple task which is easily accomplished in Access.  You then followed this up with an extension of the original task, which is again easily accomplished in Access.  In neither case do you need to use anything but a native Access .accdb file as the back end.

    If you wish to broaden your question as which product would best suit your requirements as a back end to an Access front end, then start a new thread and give full details of your business requirements.  

    I am now discontinuing monitoring of this thread.


    Ken Sheridan, Stafford, England

    • Marked as answer by JohnDBCTX Tuesday, August 29, 2017 2:05 AM
    Monday, August 28, 2017 11:00 PM