Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133

    Duplicating part of a record from a form

    I found complex code to create a duplicate record but found this solution online which seems much simpler. This basically duplicates the record but then updates specific fields to be blank. However, I am not sure why when I put this code in my form, it does duplicate the record but the fileds that should be blank still have the same as the previous records. I am not sure how to debug this as there are no errors.

    Private Sub Duplicate_Click()
    On Error GoTo Duplicate_Click_Err

    On Error Resume Next
    DoCmd.RunCommand acCmdSelectRecord
    If (MacroError = 0) Then
    DoCmd.RunCommand acCmdCopy


    End If
    If (MacroError = 0) Then
    DoCmd.RunCommand acCmdRecordsGoToNew
    End If
    If (MacroError = 0) Then
    DoCmd.RunCommand acCmdSelectRecord
    End If
    If (MacroError = 0) Then
    DoCmd.RunCommand acCmdPaste
    End If
    If (MacroError <> 0) Then
    Beep
    MsgBox MacroError.Description, vbOKOnly, ""
    End If

    Me.SalesOrder = ""
    Me.MainSN = "NEW"
    Me.ACSN = ""
    Me.PrimSN = ""
    Me.PanelSN = ""

    DoCmd.Save

    MsgBox "Record Duplicated. PLEASE Update Sales Order Number"

    Duplicate_Click_Exit:
    Exit Sub

    Duplicate_Click_Err:
    MsgBox Error$
    Resume Duplicate_Click_Exit

    End Sub

  2. #2
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Take out the
    Code:
    On Error Resume Next

    line and you'll get errors so you can debug it.

  3. #3
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 10 Access 2013
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,456
    Here is a simple code I use to duplicate records into a new record.

    Code:
    Private Sub copyrecordbutton_Click()
    On Error GoTo Err_copyrecordbutton_Click
    Dim txtOld1 As Variant
    Dim txtOld2 As Variant
    Dim txtOld3 As Variant
    Dim txtOld4 As Variant
    txtOld1 = txtcurrent1.Value
    txtOld2 = txtcurrent2.Value
    txtOld3 = txtcurrent3.Value
    txtOld4 = txtcurrent4.Value
    RunCommand acCmdRecordsGoToNew
    txtnew1.Value = txtOld1
    txtnew2.Value = txtOld2
    txtnew3.Value = txtOld3
    txtnew4.Value = txtOld4
    Exit_copyrecordbutton_Click:
    Exit Sub
    Err_copyrecordbutton_Click:
    MsgBox Err.Description
    Resume Exit_copyrecordbutton_Click
    End Sub

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,942
    Code:
    Private Sub Duplicate_Click()
    
    'If this is a new Record being copied...save it first
    
    If Me.NewRecord Then
     Me.Dirty = False
    End If
     
    'Copy and Paste entire Record
    
    DoCmd.RunCommand acCmdCopy
    DoCmd.GoToRecord , , acNewRec
    DoCmd.RunCommand acCmdPasteAppend
       
    'Blank any Controls desired/set Control to new value
    
    Me.SalesOrder = Null
    Me.MainSN = "NEW"
    Me.ACSN = Null
    Me.PrimSN = Null
    Me.PanelSN = Null
    
    MsgBox "Record Duplicated. PLEASE Update Sales Order Number"
    
    End Sub

    FYI, DoCmd.Save saves Design Changes made to a Form, not the data entered in a Record.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133
    Tried all suggestions above.

    1. If I remove the error, the message I get is that details.salesorder is required. (I do have this as a required field but not unique so I am not sure why it would give this error and not copy it from the previous record)
    2. When I try using your code on txtOld1 = DateBuilt.Value I get an error "Object required"
    3. When I try using your code the error I get is "The command or action 'Copy' isn't available now. and the line highlighted is DoCmd.RunCommand acCmdCopy

    I also saved my save line to be

    DoCmd.RunCommand acCmdSaveRecord

    Thanks,

  6. #6
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 10 Access 2013
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,456
    Show the codes you adapted from mine and then do the same with the code from Linq. We cannot offer any further help without seeing what you have actually created.

  7. #7
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133
    MissingLinq
    ----------

    Private Sub Command109_Click()

    DoCmd.RunCommand acCmdCopy
    DoCmd.GoToRecord , , acNewRec
    DoCmd.RunCommand acCmdPasteAppend

    Me.SalesOrder = Null
    Me.MainSN = "NEW"
    Me.ACSN = Null
    Me.PrimSN = Null
    Me.PanelSN = Null

    DoCmd.RunCommand acCmdSaveRecord

    MsgBox "Record Duplicated. PLEASE Enter Sales Order Number"

    End Sub

    Alansidman
    -----------
    Private Sub Command109_Click()

    Dim txtOld1 As Date
    Dim txtOld2 As String
    Dim txtOld3 As Variant
    Dim txtOld4 As Variant

    txtOld1 = DateBuilt.Value
    txtOld2 = Createdby.Value
    txtOld3 = CustomerCombo.Value
    txtOld4 = Combo59.Value

    RunCommand acCmdRecordsGoToNew

    DateBuilt.Value = txtOld1
    Createdby.Value = txtOld2
    CustomerCombo.Value = txtOld3
    Combo59.Value = txtOld4
    SalesOrder.Value = "NEW"

    MsgBox "Record Duplication SUCCESSFUL! PLEASE Enter New Sales Order Number"

    End Sub

  8. #8
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 10 Access 2013
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,456
    Have you checked your references to make sure none are missing. In the VBE, Tools-->References and check to see if you get a missing message.

    Also, look at this link and see if this is your issue:

    http://www.techonthenet.com/access/q...j_required.php

  9. #9
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133
    When I go to References, no errors appear. Only the first 4 objects are checked
    Visual Basic for Applications
    Microsoft Access 16.0 Object Library
    OLE Automation
    Microsoft Office 16.0 Access database engine Object

    The link doesn't seem to be my issue as I have lots of code in all the forms and is not similar to my issue.

    Thanks,

  10. #10
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133
    I think the issue was I set the wrong type for the variables. I changed some this seems to work now.

    I set string for text and long for number. I tried both date and variant for date field but not working. Which type should I be using?

  11. #11
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133
    All is working now. I had a typo in my date field My bad.

    Thanks all for your help.

  12. #12
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133
    Just an updated question on this case. I have the following code

    Private Sub Duplicate_Click()

    Dim Field1A As Long
    Dim Field2A As Long
    Dim Field3A As Long
    Dim Field4A As Long
    Dim Field5A As Long
    Dim Field6A As Long
    Dim Field7A As Long
    Dim Field8A As Long
    Dim Field9A As Long

    Field1A = Field1
    Field2A = Field2
    Field3A = Field3
    Field4A = Field4
    Field5A = Field5
    Field6A = Field6
    Field7A = Field7
    Field8A = Field8
    Field9A = Field9

    DoCmd.GoToRecord , , acNewRec

    Field1 = Field1A
    Field2 = Field2A
    Field3 = Field3A
    Field4 = Field4A
    Field5 = Field5A
    Field6 = Field6A
    Field7 = Field7A
    Field8 = Field8A
    Field9 = Field9A


    However if a field is blank I get an error "Invalid Use of Null" So I modified the code as follows for each line

    If Field1 <> null then
    Field1A = Field1
    End If

    If Field2 <> null then
    Field2A = Field2
    End If

    .......

    Is there a way to write a Loop code which would check each field for null before storing the value in a variable. Keep in mind that the field names are different names and i just used Field1, 2, etc... in this forum for simplicity.

    Thanks,

  13. #13
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Just change your variable types to Variant instead of Long. Then they'll be able to accept Nulls. Since you're not doing any calculations on the values, but are just storing them temporarily, Variant should work fine.

  14. #14
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    But if you really, really, really want to skip the Null fields instead, then you'd use a variable array instead of 9 separate variables, so you can loop through them. And you can refer to the controls using the syntax:

    Me.Controls("Field" & i & "A")

    where i is a counter variable that increments from 1 to 9.

    But, again, just changing the type to Variant will solve your problem.

  15. #15
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133
    Thanks, The variant worked. I would have loved the loop, except for as I mentioned my field was just a sample but the actual database uses different names for each field.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-11-2016, 06:27 PM
  2. Replies: 18
    Last Post: 02-22-2016, 02:51 AM
  3. Replies: 19
    Last Post: 05-13-2013, 01:26 AM
  4. Replies: 4
    Last Post: 02-13-2013, 10:46 AM
  5. duplicating and editing the record
    By Airis in forum Forms
    Replies: 1
    Last Post: 04-12-2010, 07:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums