Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Below is the following code that I have used to duplicate a record in a table where there is a Primary Key that is a data type of AutoNumber. My form does have subforms, but that information is not necessary to duplicate.

    I was using the following post to help me right the code: http://www.wopr.com/index.php?showtopic=43...+current+record

    ***CODE***
    Private Sub Command66_DuplicateRecord_Click()
    Dim lngOldID As Long, lngNewID As Long
    Dim fld() As Variant
    Dim x As Integer
    Dim exclude As String

    exclude = "ReportCombo,CostSqFt,ModelCost"
    ' Save current ID
    lngOldID = Me.auto

    With RecordsetClone
    ReDim fld(.Fields.Count - 1)
    .Bookmark = Me.Bookmark
    On Error Resume Next
    For x = 0 To .Fields.Count - 1
    fld(x) = .Fields(x)
    Next
    .AddNew
    For x = 0 To .Fields.Count - 1
    If (Not .Fields(x).Attributes And dbAutoIncrField) And _
    InStr(exclude$, .Fields(x).Name) = False Then
    .Fields(x) = fld(x)
    End If
    Next
    ' Pick up new ID
    lngNewID = .Fields("ID")
    .Update
    On Error GoTo 0
    End With
    CurrentDb.Execute "INSERT INTO [tblWeightAdjustmentHistory] SELECT " & _
    lngNewID & " AS auto, MBR, FloristName, OwnerID " & _
    "FROM [tblWeightAdjustmentHistory] WHERE auto=" & lngOldID

    End Sub
    ______________________________

    The problem is that the AutoNumber field becomes "0." I believe this is happening because of the statement in the code "On Error Go To 0"

    Can someone explain to me why this going to error instead of creating a new AutoNumber in the Auto field?

    Thanks,
    Karen

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why don't you add auto to the exclude string:

    exclude = "auto,ReportCombo,CostSqFt,ModelCost"

    That way, the auto field won't be set by the code.

    By the way, are you absolutely sure that auto is an AutoNumber field?

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help, Hans!

    Unfortunately, the code is still putting a 0 in the auto field. I double checked and the auto field is the data type AutoNumber.

    Here is my revised code:

    Private Sub Command66_DuplicateRecord_Click()
    Dim lngOldID As Long, lngNewID As Long
    Dim fld() As Variant
    Dim x As Integer
    Dim exclude As String

    exclude = "auto"
    ' Save current ID
    lngOldID = Me.auto

    With RecordsetClone
    ReDim fld(.Fields.Count - 1)
    .Bookmark = Me.Bookmark
    On Error Resume Next
    For x = 0 To .Fields.Count - 1
    fld(x) = .Fields(x)
    Next
    .AddNew
    For x = 0 To .Fields.Count - 1
    If (Not .Fields(x).Attributes And dbAutoIncrField) And _
    InStr(exclude$, .Fields(x).Name) = False Then
    .Fields(x) = fld(x)
    End If
    Next
    ' Pick up new ID
    lngNewID = .Fields("ID")
    .Update
    On Error GoTo 0
    End With
    CurrentDb.Execute "INSERT INTO [tblWeightAdjustmentHistory] SELECT " & _
    lngNewID & " AS auto, MBR, FloristName, OwnerID " & _
    "FROM [tblWeightAdjustmentHistory] WHERE auto=" & lngOldID

    End Sub

    Any other suggestions?

    Thanks,
    Karen

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you change

    If (Not .Fields(x).Attributes And dbAutoIncrField) And _
    InStr(exclude$, .Fields(x).Name) = False Then

    to

    If InStr(exclude$, .Fields(x).Name) = False Then

    If that doesn't help, could you post a stripped down and zipped copy of the database (in Access 2000 format)?
    • Make a copy of the database and work with that.
    • Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    • In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    • Remove or modify data of a confidential nature.
    • Perform a compact and repair (Tools/Database Utilities).
    • Make a zip file containing the database.
    • Attach the zip file to a reply.
    Perhaps we could find the cause of the problem.

  5. #5
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The recommended code change did not appear to work.

    Attached is the file as recommended.

    Thanks!
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This code worked for me.
    Code:
    Private Sub Command66_DuplicateRecord_Click()
    Dim lngOldID As Long
    Dim sql As String
    ' Save current ID
    lngOldID = Me.auto
    On Error GoTo 0
    
    sql = "INSERT INTO [tblWeightAdjustmentHistory] SELECT  MBR, FloristName, OwnerID FROM [tblWeightAdjustmentHistory] WHERE auto=" & lngOldID
    CurrentDb.Execute sql
    
    End Sub
    There are two alternative ways to create records. One option is to open a recordset, and add a record to that. The other is to run an Insert sql statement (which is the same as an append query. Your code had an attempt at both methods. You need one or the other, not both. I have just used the Insert SQL option.
    Autonumbers are assigned automatically, so you just ignore them in your query. there is no need to increment the ID. Access does it for you automatically.
    Regards
    John



  7. #7
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, John!

    This helped fix my issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •