Copy in Access using VBA

Question

I created a copy button in an Access form to copy the Data in the fields that users enter so they can paste it in an internal system.

I created VBA code "on the click:

Private Sub Command6_Click()
     On Error GoTo Err_cmdDuplicate_Click
     DoCmd.RunCommand acCmdSelectRecord
     DoCmd.RunCommand acCmdCopy

     Exit_cmdDuplicate_Click:
     Exit Sub

     Err_cmdDuplicate_Click:
         MsgBox Err.Description
         Resume Exit_cmdDuplicate_Click

 End Sub

I am having 2 problems:
it copies all the data with the headers but pastes it vertically rather than horizontally. I guess it needs to be formatted. I have to add since the code was grabbing everything in the form even the information that I didn't want. I created a query then a report based on the query then made the copy button with the code behind it.

Where is the data being pasted to? You can set the clipboard text directly via VBA, so you can select which data fields get copied: https://stackoverflow.com/questions/14219455/excel-vba-code-to-copy-a-specific-string-to-clipboard — Tim Williams, Jul 13 '18 at 22:59
As @TimWilliams says - it matters what the destination is. Different methods for text vs Excel. Excel allows you to use CopyFromRecordset without header, Text might have to be coded in a loop to form a string for the clipboard — dbmitch, Jul 14 '18 at 01:47

score 0 · Answer 1 · answered Jul 24 '18 at 21:26

this is the code Private Sub cmdCopy_Click() On Error GoTo Err_cmdDuplicate_Click

'Copies values from agent entered data fields into 'required format for TAS and copies to 'system clipboard.

'control name and type are as follows: 'CboTeam 'CboTax 'TboCallBack 'TboCaller 'TboBusName 'CboAuthType 'TboAuthID 'CboContact 'TboDetail 'TboBal 'TboDelqs

Application.Echo False Me.PasteBox.Visible = True

Me!PasteBox.Value = _ "Team: " & Me!CboTeam & vbNewLine & _ "Tax Type: " & Me!CboTax & vbNewLine & _ "Phone: " & Me!TboCallBack & vbNewLine & _ "Caller: " & Me!TboCaller & vbNewLine & _ "Business Name: " & Me!TboBusnAME & vbNewLine & _ "Authentication Method: " & Me!CboAuthType & vbNewLine & _ "Authentication ID: " & Me!TboAuthID & vbNewLine & _ "Contact Reason: " & Me!CboContact & vbNewLine _ & vbNewLine & _ "Call Detail:" & vbNewLine & _ Me!TboDetail & vbNewLine _ & vbNewLine & _ "Balance: " & Me!TboBal & vbNewLine & _ "Delinquent Periods: " & Me!TboDelqs

Me.PasteBox.SetFocus DoCmd.RunCommand acCmdCopy

Me.cmdcopy.SetFocus

Me.PasteBox.Visible = False Application.Echo True

Exit_cmdDuplicate_Click: Exit Sub Application.Echo True

Err_cmdDuplicate_Click: MsgBox Err.Description Application.Echo True Resume Exit_cmdDuplicate_Click Application.Echo True End Sub

Copy in Access using VBA

1 Answers1