Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: Copy and Paste button

    Hello this is my 1st one so pardon my ignorance ....
    ---
    Im working on a form that contains 8 text field boxes, 6 drop down controls.
    and a submit button.

    it collects info of customer calls.. Name Phone address, call day /pm etc...

    what i am trying to accomplish:

    I need a copy button that would just copy all the field info once is entered and a paste button that allows me to paste all the info in the same fields.

    we dont need to edit the info that would be too easy... so i just need a replica of the initial info that way i can change it into a new record if needed.

    This is what i got so far BUT is not that easy it does copy the info entered in the "CSR_name" however i dont know how to get the rest of the fields in there i tried commas and parenthesis i googled stuff around and i dont if is possible... Maybe a macro could do it, but i dont know anything about macros and the youtube videos i found dont really show anything about copying or pasting ...

    Thanks! and sorry in advance if i wasnt clear enough also if code doesnt show in the nice box...

    Code:

    Private Sub Command109_Click()
    DoCmd.GoToControl "CSR_name"
    DoCmd****nCommand acCmdCopy

    End Sub


    Private Sub Command108_Click()
    DoCmd****nCommand acCmdPasteAppend
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a "quick and dirty" solution:

    1. Add "{Copy}" (without quotes) to the Tag property of each control you want to copy the value.

    2. 1 command button named Command_Copy will copy the values of the controls mentioned in (1) into a buffer.
    1 command button named Command_Insert will insert the copied values from the buffer into the controls.

    3. Paste this code in the module of the form:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_varCopy As Variant
    
    Private Sub Command_Copy_Click()
    
        Dim i As Long
        
        For i = 0 To UBound(m_varCopy, 2)
            m_varCopy(1, i) = Me.Controls(m_varCopy(0, i)).Value
        Next i
        
    End Sub
    
    Private Sub Command_Insert_Click()
    
        Dim i As Long
        
        For i = 0 To UBound(m_varCopy, 2)
            Me.Controls(m_varCopy(0, i)).Value = m_varCopy(1, i)
        Next i
    
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        Dim ctl As Control
        Dim lngCount As Long
        
        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "{Copy}") <> 0 Then lngCount = lngCount + 1
        Next ctl
        ReDim m_varCopy(0 To 1, 0 To lngCount - 1)
        lngCount = 0
        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "{Copy}") <> 0 Then
                m_varCopy(0, lngCount) = ctl.Name
                lngCount = lngCount + 1
            End If
        Next ctl
                   
    End Sub
    Although there are several limitations to this method, this works whatever the number of controls can be and whatever names these controls can have.
    Have a nice day!

  3. #3
    Join Date
    Jun 2012
    Posts
    5
    Sinndho

    Thank you very much for the quick reply!! ... I just added the code and i am getting the following error

    Run-time error '13';
    Type mismatch

    I proceed to Debug and get the follwing line highlited in yellow

    For i = 0 To UBound(m_varCopy, 2)

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To Copy the entire Current Record and Paste it into a New Record only requires three lines of code:
    Code:
    DoCmd. RunCommand acCmdSelectRecord
    DoCmd. RunCommand acCmdCopy
    DoCmd. RunCommand acCmdPasteAppend
    You'll need to delete the [Space] I placed between DoCmd. and RunCommand. If I didn't insert the [Space] when posting, this site plugs in the ****! I understand why they do it, but I wish they'd fix it, as it'd very aggravating for this type of a forum!

    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

  5. #5
    Join Date
    Jun 2012
    Posts
    5
    Thanks Missinglinq... ok i understand about the spaces... So as an example is it suppose to look like this or where do i insert the fields

    Code:
    DoCmd. RunCommand acCmdSelectRecord  "CSR_name" "Cust_name" "Cust_address"
    DoCmd. RunCommand acCmdCopy
    DoCmd. RunCommand acCmdPasteAppend

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Where you run the code is up to you! I'd probably place the code behind a Command Button, or if a Datasheet View Form is involved, in the DoubleClick event of one or more Textboxes, as Command Buttons don't show up in Datasheet View Forms.

    And the code needs to be exactly as I gave it to you! The line

    DoCmd. RunCommand acCmdSelectRecord

    selects all Fields in the Record. There is no need to list the Fields as you've done with the Red portion of

    DoCmd. RunCommand acCmdSelectRecord "CSR_name" "Cust_name" "Cust_address"

    and it will cause an error to be thrown.

    Also note that the line of code

    DoCmd. RunCommand acCmdPasteAppend

    creates the New Record, and after it executes, you will be on the new, copied Record, not on the original Record.

    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

  7. #7
    Join Date
    Jun 2012
    Posts
    5
    On my phone* forgive my abbreviations ... Si ur saying that all I need is to add a botton put those 3 lines and call it a day? ... And once I'm on the new record I can edit it as I see fit and submit it as a new record ?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Check that the Form_Open event handler is executed, specially this line:
    Code:
        ReDim m_varCopy(0 To 1, 0 To lngCount - 1)
    and also that lngCount is a positive number when the line is executed.
    Have a nice day!

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Davect View Post
    On my phone* forgive my abbreviations ... Si ur saying that all I need is to add a botton put those 3 lines and call it a day? ... And once I'm on the new record I can edit it as I see fit and submit it as a new record ?
    Yes!

    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
    Jun 2012
    Posts
    5
    Thanks !!! I just tried it... It worked like a charm ...

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    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

Tags for this Thread

Posting Permissions

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