Copy and Paste button

Davect · #1 (**permalink**) 06-23-12, 17:00

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

Sinndho · #2 (**permalink**) 06-23-12, 18:55

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.

Davect · #3 (**permalink**) 06-23-12, 21:02

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)

Missinglinq · #4 (**permalink**) 06-23-12, 21:10

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)>

Davect · #5 (**permalink**) 06-23-12, 22:19

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

Missinglinq · #6 (**permalink**) 06-23-12, 23:15

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)>

Davect · #7 (**permalink**) 06-24-12, 00:36

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 ?

Sinndho · #8 (**permalink**) 06-24-12, 01:38

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.

Missinglinq · #9 (**permalink**) 06-24-12, 08:47

Quote:

Originally Posted by Davect

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)>

Davect · #10 (**permalink**) 06-25-12, 06:22

Thanks !!! I just tried it... It worked like a charm ...

Missinglinq · #11 (**permalink**) 06-25-12, 08:14

Glad we could help!

Linq ;0)>

Internet Services

Web Development

Digital Marketing

Consumer Tech