Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Duplicate Record Error on the AFterUpdate Event

    Hello All,

    I am trying to create a Duplicate Record using the wizard, but I am receiveing an error.

    I am using two combo boxes to populate data into an input form (code below).

    Both combo boxes populate the data well. Now I want to add a Duplicate Record button to duplicate a record. I thought I could just use the wizard to create a button, but it is not working. It is erroring out on the Combo Box 2 - AfterUpdate Event. :-(

    This is the Run-time error I am receiveing from the dup rec button.

    Run-time error 3020:
    Update or CancelUpdate without AddNew or Edit.


    Here is the code I am using on the input form:

    <BEGIN CODE>

    Combo Box 1 - AfterUpdate Event:


    Private Sub cboProjNumber_AfterUpdate()

    Me.ProjNum_txt.Value = Me.cboProjNumber.Column(1)
    Me.ProjectMgr_txt.Value = Me.cboProjNumber.Column(2)
    Me.Vendor_txt.Value = Me.cboProjNumber.Column(3)
    Me.SiteID_txt.Value = Me.cboProjNumber.Column(4)
    Me.OrderStatus_txt.Value = Me.cboProjNumber.Column(5)

    If IsNull(Me.cboProjNumber) Then
    Me.FilterOn = False
    Else
    Me.Filter = "Proj_Number = """ & Me.cboProjNumber & """"
    Me.FilterOn = True
    End If
    End Sub

    Combo Box 2 - AfterUpdate Event:
    Private Sub cboEquipNumber_AfterUpdate()

    Me.ItemQty_txt.Value = Me.cboEquipNumber.Column(1)
    Me.EquipType_txt.Value = Me.cboEquipNumber.Column(2)
    Me.EquipDescription_txt.Value = Me.cboEquipNumber.Column(3)

    End Sub

    If IsNull(Me.cboProjNumber) Then
    Me.FilterOn = False
    Else
    Me.Filter = "Proj_Number = """ & Me.cboProjNumber & """"
    Me.FilterOn = True
    End If

    End Sub


    FORM ON CURRENT EVENT:
    Private Sub Form_Current()
    With Me
    If .NewRecord Then .cboProjNumber = .cboProjNumber.DefaultValue
    End With
    End Sub

    <END CODE>

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    There's no need for vb code. Run a query. Most everything can be done with queries.
    YOUR query should pull the record you want duplicated and append it to the table. 1 query, no code.

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Quote Originally Posted by ranman256 View Post
    There's no need for vb code. Run a query. Most everything can be done with queries.
    YOUR query should pull the record you want duplicated and append it to the table. 1 query, no code.
    Ugh! Never have duplicated a record using a query.

    I need to create a duplicated record with a new PK. I want to duplicate most of the data and remove 3 fields so I can update those fields.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mlrnet View Post
    Ugh! Never have duplicated a record using a query.

    I need to create a duplicated record with a new PK. I want to duplicate most of the data and remove 3 fields so I can update those fields.
    It's a rather simple task and you don't need to actually create a query object in your database: you can have a SQL expression executed by the JET engine (= the database engine or the SQL interpretor) in a VBA module.

    Let's suppose that we have a table like this:
    Code:
    CREATE TABLE [table1]
        ( [Pk] COUNTER CONSTRAINT PK PRIMARY KEY,
          [Col1] TEXT(10) NULL,
          [Col2] TEXT(10) NULL,
          [Col3] TEXT(50) NULL,
          [Col4] TEXT(50) NULL
        );
    With the following contents:
    Code:
    Pk	Col1		Col2		Col3		Col4
    ------------------------------------------------------------------
    1	Row1_Col1	Row1_Col2	Row1_Col3	Row1_Col4
    2	Row2_Col1	Row2_Col2	Row2_Col3	Row2_Col4
    3	Row2_Col1	Row2_Col2	Row2_Col3	Row2_Col4
    Let's now imagine that I want to create a new row that will duplicate Col1, Col2 and Col4 (but not Col3) from the second row (Pk:2). The SQL expression of a query for doing so would be:
    Code:
    INSERT INTO Table1 ( Col1, Col2, Col4 )
        SELECT Col1, Col2, Col4
         FROM Table1
        WHERE Pk = 2;
    In a procedure of a VBA module, we can use that SQL expression like this:
    Code:
    Sub DuplicateRow2()
    
        Const c_SQL As String = "INSERT INTO Table1 ( Col1, Col2, Col4 ) " & _
                                "SELECT Col1, Col2, Col4 FROM Table1 WHERE Pk = 2;"
                                
        CurrentDb.Execute c_SQL, dbFailOnError
        
    End Sub
    If we want to use the same technique in a bound Form where the row to be duplicated is the current row, with the additional constraint that the newly created row should become the current record for the form, we can use:
    Code:
    Private Sub Command_Duplicate_Click()
    
        Const c_SQL As String = "INSERT INTO Table1 ( Col1, Col2, Col4 ) " & _
                                "SELECT Col1, Col2, Col4 FROM Table1 WHERE Pk = "
                                
        Dim strSQL As String
        
        strSQL = c_SQL & Me!Pk & ";"
        CurrentDb.Execute strSQL, dbFailOnError
        Me.Requery
        Me.RecordsetClone.MoveLast
        Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark
    
    End Sub
    There are many variations on the same theme. If the row to be duplicated is selected in a combo, we can use:
    Code:
        strSQL = c_SQL & Me.Combo_Pk.Value & ";"
    Have a nice day!

  5. #5
    Join Date
    Sep 2008
    Posts
    150
    Hi Sinndho,

    I really appreciate you and ranman256 responses.

    I did try to use your code below, but it is not working. I may not have updated it incorrectly. I am using a combo box that is attached to a query to populate the data.

    So, when I click on the button it looks like it added a new record, but when I check the table the dup record was not created. I had to comment out the “CurrentDb.Execute strSQL, dbFailOnError” because it gave me a Run-time error: Syntax error in INSERT INTO statement.

    Obviously, there is an issue with my Insert Into statement, but I can't seem to find it.

    Here is the code I am using:

    Private Sub InputForm_DupRec_Button_Click()

    Const c_SQL As String = "INSERT INTO tbl_Equipment (ProjNum, ProjectMgr, Vendor) " & _
    "SELECT ProjNum, ProjectMgr, Vendor FROM tbl_Equipment WHERE Pk = "

    Dim strSQL As String

    strSQL = c_SQL & Me.cboProjNum.Value & ";"
    'CurrentDb.Execute strSQL, dbFailOnError
    Me.Requery
    Me.RecordsetClone.MoveLast
    Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark

    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. If you comment the line CurrentDb.Execute strSQL, dbFailOnError no action will be performed as this is the line that actually execute the INSERT statement.

    2. Is ProjNum the Primary Key of the table tbl_Equipment ? In such a case, it should not be present in the columns list:
    Code:
    Const c_SQL As String = "INSERT INTO tbl_Equipment (ProjectMgr, Vendor) " & _
    "SELECT ProjectMgr, Vendor FROM tbl_Equipment WHERE Pk = "
    3. The condition (WHERE...) is the SELECT part of the insert query should refer to the actual name of the Primary Key of the table tbl_Equipment , Pk was just a placeholder in my example.

    4. If you can't solve the problem, do as follows:
    a) Change the code like this:
    Code:
    - - - 
    strSQL = c_SQL & Me.cboProjNum.Value & ";"
    Debug.Print strSQL
    Stop
    b) When the code stops executing, open the immediate window (Ctrl+G) and copy the text printed into it (it's the string contained in the variable strSQL).
    c) Create a new query, switch to SQL view and paste the string copied in b).
    d) Switch the query to DataSheet view. The error that will be reported by the query interpretor should be more explicit than the one reported by the VBA interpretor.
    Have a nice day!

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Everyone has their own way of doing this,but I've been using the following for years.

    Since this kind of thing is usually done on a Record that was just created, you need to save it first, then copy and paste it. You can then set as many Fields as you need to be empty to Null:

    Code:
    Private Sub DupRecord_Click()
     
     If Me.Dirty then Me.Dirty = False 'Save current Record, if it’s not already save
    
     DoCmd.RunCommand acCmdSelectRecord
     DoCmd.RunCommand acCmdCopy
     DoCmd.GoToRecord , , acNewRec
     DoCmd.RunCommand acCmdPaste
    
    Me.1stFieldToLeaveEmpty = Null
    Me.2ndFieldToLeaveEmpty = Null
    Me.3rdFieldToLeaveEmpty = Null
        
    End Sub

    If this is a Datasheet View Form, you can place the code in the DoubleClick event of one or more Textboxes, instead of using a Command Button.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Missinglinq View Post
    Everyone has their own way of doing this,but....
    an even quicker (and dirtier ) approach woudl be to copy and paste the the row in a form
    select the bar to the left of the row you want to copy
    <ctrl + C> to copy the selected row
    add a new record
    <ctrl + P> to paste the selected row
    then edit the data
    ..the big advantage form my perspective is no programming effort required
    ..the big disadvantage form my perspective is that its all to easy so users coudl well end up duplicating rows uneccesarily, compromising the data integrity. they 'forget' to change the 3 columns that are different

    if you are adding data and want to carry forward the last entered rows then I guess you could always stuff the previous row's values into form global variables in the forms before or after update event and then have the button
    1) add a new row
    2) copy the values form your forms global variables into the relevant controls.

    I wouldn't personally use a query for this, but then again I don't like the idea of letting users copy rows... it just doesn't 'feel' right, has the possible whiff of flaky normalised design. it may well be fine, but it doesn't 'sound' right
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree that the big drawback would be forgetting to change those 3 columns; only if those Fields were set up to be unique, with validation, could you be sure that copied data was replaced in the New Record.

    As for the copying of the last Record entered meaning that the database was non-normalized, that would depend on what the business needs required.

    A database which had someone doing 'production' style data entry, documenting, for instance, the work done by each of three dozen telemarketers, on each of the previous 30 workdays, tracking for each day

    • How many cold calls were made
    • How many contacts were actually made
    • How many sales of ProductA were made
    • How many sales of Accessory Kits for ProductA were made

    would be normalized, but very tiresome, because of all of the repetitive data entry required!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Missinglinq View Post
    As for the copying of the last Record entered meaning that the database was non-normalized, that would depend on what the business needs required.
    nope Im not suggesting that you duplicate the last row (added or modified), just that you copy the values from that row and then if required insert that data into a new row.

    no mater, they are different ways of removing fur from a feline
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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