423,309 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,309 IT Pros & Developers. It's quick & easy.

VBA- Copy in Access

P: 3
I have been asked to create 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 crated button and created VBA code "on the click:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2.  On Error GoTo Err_cmdDuplicate_Click
  3.    DoCmd.RunCommand acCmdSelectRecord
  4.     DoCmd.RunCommand acCmdCopy
  5.  
  6. Exit_cmdDuplicate_Click:
  7.     Exit Sub
  8.  
  9. Err_cmdDuplicate_Click:
  10.      MsgBox Err.Description
  11.     Resume Exit_cmdDuplicate_Click
  12.  
  13. End Sub
I am having 2 problems:
it copies all the data with the headers but and it paste it vertically other than horizontally . which I guess it needs to be formatted but I am not sure how since I am not VBA SAVVY. I have to add since the code was grabbing everything in the form even the information that I didn't want to and it looked like a chaos ,I created a query then a report based on the query then made copy button with the code behind it.
I appreciate your help in advance.

Attached Images
File Type: jpg PIC2.jpg (24.4 KB, 123 views)
File Type: jpg PIC3.jpg (42.1 KB, 126 views)
File Type: jpg PIC1.jpg (16.6 KB, 122 views)
Jul 13 '18 #1
Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,532
If it is only certain fields that you need to copy, then you will ave to write the VBA to select those particular fields and save them separately. The code you posted simply copies the entire record.
Jul 14 '18 #2

zmbd
Expert Mod 5K+
P: 5,279
the fields that users enter so they can paste it in an internal system
Looks like you are using a word document, is this correct?
If so, take a look at:
home > topics > microsoft access / vba > insights > application automation
The two examples are for Outlook and Excel; however, simple step to do this same thing with Word. Before we can help you do that we would need a bit more information about what you are doing with the information... may be easier to create a report in Access or to link the Word document to the database using the mail-merge if the access-report can't handle something (although, since Access2003 I've been moving more and more of my stuff from external Word merges to internal reports).
Jul 14 '18 #3

P: 3
no I was just trying to show where its pasted
Jul 24 '18 #4

P: 3
I found the code
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopy_Click()
  2. On Error GoTo Err_cmdDuplicate_Click
  3.  
  4.  
  5.  
  6. 'Copies values from agent entered data fields into
  7. 'required format and copies to
  8. 'system clipboard.
  9.  
  10. 'control name and type are as follows:
  11. 'CboTeam
  12. 'CboTax
  13. 'TboCallBack
  14. 'TboCaller
  15. 'TboBusName
  16. 'CboAuthType
  17. 'TboAuthID
  18. 'CboContact
  19. 'TboDetail
  20. 'TboBal
  21. 'TboDelqs
  22.  
  23.  
  24. Application.Echo False
  25. Me.PasteBox.Visible = True
  26.  
  27. Me!PasteBox.Value = _
  28. "Team:  " & Me!CboTeam & vbNewLine & _
  29. "Tax Type: " & Me!CboTax & vbNewLine & _
  30. "Phone:  " & Me!TboCallBack & vbNewLine & _
  31. "Caller:  " & Me!TboCaller & vbNewLine & _
  32. "Business Name:  " & Me!TboBusnAME & vbNewLine & _
  33. "Authentication Method:  " & Me!CboAuthType & vbNewLine & _
  34. "Authentication ID:  " & Me!TboAuthID & vbNewLine & _
  35. "Contact Reason:  " & Me!CboContact & vbNewLine _
  36.     & vbNewLine & _
  37. "Call Detail:" & vbNewLine & _
  38. Me!TboDetail & vbNewLine _
  39.     & vbNewLine & _
  40. "Balance:  " & Me!TboBal & vbNewLine & _
  41. "Delinquent Periods:  " & Me!TboDelqs
  42.  
  43.  
  44.  
  45. Me.PasteBox.SetFocus
  46. DoCmd.RunCommand acCmdCopy
  47.  
  48. Me.cmdcopy.SetFocus
  49.  
  50.  
  51. Me.PasteBox.Visible = False
  52. Application.Echo True
  53.  
  54.  
  55. Exit_cmdDuplicate_Click:
  56.     Exit Sub
  57. Application.Echo True
  58.  
  59. Err_cmdDuplicate_Click:
  60.      MsgBox Err.Description
  61. Application.Echo True
  62.     Resume Exit_cmdDuplicate_Click
  63. Application.Echo True
  64. End Sub
Jul 24 '18 #5

zmbd
Expert Mod 5K+
P: 5,279
So is this the code that you are using now... or is this Code that you found somewhere online?

You also haven't answered if you've tried to do this by creating report in Access based on your query or a mail-merge document in Word based on your query. Both methods can yield some very sophisticated reports and are my first goto methods verses VBA.
Jul 25 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.