Copy To Clipboard
By Richard Rost
3 years ago
Copy & Paste Data to/from the Clipboard in Access
In this Microsoft Access tutorial, I'm going to teach you how to use the clipboard to copy and paste data in Microsoft Access.
Mei Ling from Palo Alto, California (a Platinum Member) asks: I spend a lot of time copying and pasting customer name and address information from my Access database to either a spreadsheet, an email, a Word document, or my label writer. Is there any way you can set it up so that with one click, I can copy their entire name and address?
Members
There is no Extended Cut, but here's the database for the Gold members.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Usage
FirstName.SetFocus
DoCmd.RunCommand acCmdCopy
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Setfocus, DoCmd.RunCommand, acCmdCopy, acCmdPaste, how to use the clipboard in microsoft access, copy to clipboard, cut to clipboard, paste from clipboard, selstart, len, echo off, hide screen writes
Intro In this video, I will show you how to copy data to the Windows clipboard and paste it back again in Microsoft Access using simple VBA code. We will learn how to set up buttons to quickly copy individual fields or an entire address block from your forms, use string concatenation to combine multiple fields, manage control visibility to avoid screen flashing, and even create a paste button to insert clipboard contents into a selected field. Techniques for setting focus and appending pasted data are also covered.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm Richard Rost, your instructor. Today, we are going to learn how to copy data to the clipboard and paste it back again in Microsoft Access.
Today's question comes from Miling in Palo Alto, California, one of my platinum members. Miling says, I spend a lot of time copying and pasting customer name and address information from my Access database to either a spreadsheet, an email, a Word document, or my label writer. Is there any way you can set it up so that with one click, I can copy their entire name and address?
Yes, of course we can do this with a little bit of VBA code. This is a developer video. Do not be scared if you have never done any VBA programming before. Do not worry, I have you covered. Go watch this video first. It is free. It is on my website and on my YouTube channel. It is about 20 minutes long and teaches you everything you need to know to get started.
Also, go watch my concatenation video. That is where you can put the values from two or more fields together into one string. That is how we can put the name and the address fields all together. Go watch those and come on back.
Here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to. Here in the customer form, let's say I want to make a button. Let's just do one field first. Let's say I just want to copy the first name field to the Windows clipboard. How do we do that?
Let's go to Design View. I am just going to copy one of these buttons. Copy, paste. Let's put it down here in the button. Let's call this guy a copy first name, copy FN. We will right-click, go to Properties. Let's make it called CopyFN_btn. Right-click on it. Go to Build Event. I know that was off your screen. Right-click, Build Event.
First thing you have to do, if you want to use the clipboard functions, is that you have to go to that control. You have to set the focus on that control. There is another command called GoToControl. I like this one. FirstName.SetFocus. That says, jump the cursor to the first name field. It should highlight all the data in that field.
Now, we are going to run a command that says Copy. It looks like this: DoCmd.RunCommand. There is a whole ton of commands in here you can run. The one you are looking for is acCmdCopy. Yes, there is also Cut and Paste.
So, it is going to go to the first name field and run the Copy command. Then the data should be in your clipboard. Let's come back out here. Close it. Close it. Open it. Click the button.
If you want to test it, I'll just click here in Country and paste on my keyboard (Ctrl+V). There it is.
Now you know how to use the clipboard.
What if you want to put a bunch of fields together and copy them all? I know exactly what you are talking about, Miling. If you have an email you want to send and you want to say, the customer's address is whatever, you do not want to sit here and have to copy this and copy that. You want to put it all together and just go. I have the same exact thing in my database.
What we need is another field to assemble everything. So, I just take one of these other fields, copy, paste. Slide it over here and make some room for it.
Open this guy up. I do not want Country in there, so get rid of the control source. We do not want to have it bound to any field in our table. It is just an unbound box. Usually with unbound boxes, I will make them gray or something like that to set them off a little bit.
Let's give it a good name. What do you want to call it? Let's call it the CopyBlock. So what we are going to do is use some string concatenation. We are going to assemble the fields in this box the way we want to copy and paste. You can do whatever you want.
Let's copy this button, copy, paste. This will be my copy address, copyADDR. Give it a good name. Always give it a good name: CopyADDR_btn.
Click, Build Event. Now, this time, first we have to assemble the fields. CopyBlock = FirstName & " " & LastName & vbNewLine & Address & vbNewLine & City & " " & State & " " & ZipCode & vbNewLine & Country
That is your whole big address block: first name, last name, address, city, state, zip, and country, all put together. Now that is all assembled in CopyBlock.
Now we can do the same stuff we did here: copy that, paste it up there, and instead of FirstName.SetFocus, we are going to use CopyBlock.SetFocus.
Compile your code (Debug > Compile). Go back over here. Close it, open it, and copy it. There it is. I got Richard in my Country. That is fine. We will get rid of that. Leave it blank for us, hit it, and there it goes.
Now, I am just going to test it. Let's open up Notepad or whatever you have. Here is my Notepad. Paste. There it is. It is that easy. Build it the way you want.
Now, you might not want to see this guy lingering around here. If that is the case, you can make it invisible, but you have to briefly make it visible while you do the copy operation. For example, you could take this and set its Visible property to No. What I usually do is make them really tiny. In this case, it might flash very briefly. Usually, for hidden fields that are hidden all the time, I like to make them red and then hide them so you only see them in Design View. Then, I stick it over the top of another one because, again, you will only see it in Design View and you will know it is there.
But this guy might flash very briefly on the screen. We are going to leave it there. If you want to save space: copy first name, copy address. We will stick this next to it, and then you can fly this up to that.
If you just try to run this now, you are going to get an error: "Cannot move the focus to the control CopyBlock." Why? Because it is not visible. So what we can do is, before we set focus, we say CopyBlock.Visible = True.
Now, after the operation, we can do CopyBlock.Visible = False to hide it again. Maybe give a beep to indicate to the user that the operation is done. Save it, come back out here.
Oh, you cannot hide it if it has the focus. I forgot about that. You cannot hide it if it has the focus, so you have to put the focus back on the button, for example. So, right here, we are going to say CopyADDR_btn.SetFocus. All right, it's in my notes and I still forgot about it.
Ready? Close it. Open it. Copy. And it is done. See how it displays itself briefly.
You could try turning off screen updating. This is a more advanced trick. Be careful with this. You can use DoCmd.Echo False. This turns off screen updates completely, so nothing Access does is visible during that time. The problem is, if you have any error messages or anything else that pops up, you will not see it because screen updating is off. Do not forget to turn it back on afterward.
If any of these commands generate an error, you are in trouble because your application will look like it locked up. Use this sparingly. But it is useful for situations like this where you want to prevent flashing on the screen.
Ready? Let's try it. Anything flash? I do not know if the video recording software is picking that up or not, but now you can hit the button and it does not flash at all.
That is pretty much it. Like I mentioned before, there is also a Cut and a Paste operation. So if you are copying stuff from other places, let's say you want to paste it in your Notes field. Let's do a Paste button. Right-click, Build Event.
Oh, we did not name our button. See, it says Command34. Always give your buttons a good name. Trust me, you will thank me later. I did not used to name them. My buddy Alex talked me into it. I used to always just put a comment up there, like "this is the button for paste," but it is better to give it a name like PasteButton. It takes two seconds, but I am still not in the habit of doing it, and it has been years.
So we are in the paste button's event. The same thing: set the focus to where you want to go. Notes.SetFocus. Then we are going to paste: DoCmd.RunCommand acCmdPaste.
Save it, close it, open it. If you go somewhere else, like back out to Notepad, let me copy something out of an email or something. We can come in here and just hit paste. Look at that.
Now, the set focus will normally overwrite what is in there. If you want to append it to the end, all you have to do is put the cursor at the end. How do you do that? After you set focus to it, you say Notes.SelStart = Len(Notes). SelStart means the start of the selection. If you do not specify a length, it starts where you want. The length of Notes is how long it is, so you are basically moving the cursor to the end.
I cover these in more detail in my other classes. This is like bonus material. We are already done. I am just going over some other cool stuff.
Save it. Let's throw in a Debug > Compile, come back out here. Now, this time, the paste button is going to append it on the end instead of overwriting what is in there. Click, and look at that.
You can even check to see if this is null and, if so, insert another new line. There are all kinds of things you can do with this. I could spend hours just doing cool stuff like this.
Speaking of spending hours doing cool stuff like this, if you like this stuff, if you enjoy my videos, if you like hanging out with me and learning how to do VBA, I have dozens, maybe hundreds of hours of VBA and developer programming on my website. I just finished Developer 44. I did not finish it, it is in progress. I posted the first two lessons. That is all about customizing the ribbon and customizing right-click shortcut menus.
I love this stuff. This is what I do. This is my passion. I would probably do this stuff even if it was not my job. If you want to learn with me, if you enjoy my style and want to learn some Access with me, check them out. There is my link right there. I will put a link down below in the description you can click on.
That is going to be your TechHelp video for today. I hope you learned something, my friends. Live long and prosper. I will see you next time.Quiz Q1. What is the primary purpose of the video tutorial?
A. To show how to import data from Excel into Access
B. To demonstrate copying and pasting data using VBA in Access
C. To create database relationships in Access
D. To perform queries using SQL in Access
Q2. Why would you use concatenation when copying customer information to the clipboard?
A. To increase database performance
B. To combine multiple fields into one formatted string for copying
C. To sort records before copying
D. To encrypt the data before copying
Q3. Which VBA command is used to copy data to the Windows clipboard in Access?
A. DoCmd.RunCommand acCmdCut
B. DoCmd.RunCommand acCmdPaste
C. DoCmd.RunCommand acCmdCopy
D. DoCmd.RunCommand acCmdDelete
Q4. What must you do before running the Copy command on a field or control in Access VBA?
A. Hide the control first
B. Set focus to the control you want to copy from
C. Delete the previous value in the control
D. Lock the control
Q5. What is the function of setting the Visible property of a control to False in this context?
A. It deletes the control from the form
B. It hides the control from the user interface
C. It makes the control editable
D. It protects the control from being copied
Q6. Why should you avoid hiding a control immediately after setting focus to it in your copy operation?
A. The clipboard will not work if the control is visible
B. Access will display an error because you cannot hide a control while it has focus
C. The value will not be copied correctly
D. The form will close unexpectedly
Q7. What is the purpose of using DoCmd.Echo False in VBA code?
A. It locks the database so no changes can be made
B. It turns off screen updating to prevent screen flicker
C. It encrypts the clipboard data
D. It disables keyboard input
Q8. What can happen if an error occurs when DoCmd.Echo is set to False and not reset to True?
A. Clipboard commands will stop working
B. The database will automatically close
C. The application may appear to hang or freeze
D. Nothing, there is no impact
Q9. When creating a Paste button, which control method is used to paste clipboard contents into a field?
A. DoCmd.RunCommand acCmdCut
B. DoCmd.RunCommand acCmdPaste
C. DoCmd.RunCommand acCmdCopy
D. DoCmd.RunCommand acCmdDelete
Q10. What effect does setting SelStart to the length of the Notes field have when pasting data?
A. It overwrites the entire content of Notes
B. It places the cursor at the beginning of Notes
C. It appends the pasted data to the end of Notes
D. It creates a new Notes record
Q11. Why is it considered good practice to give buttons and controls descriptive names in Access?
A. It makes the form run faster
B. It prevents the form from being closed accidentally
C. It improves code readability and maintenance
D. It keeps the database size smaller
Q12. What is an "unbound" text box in Access forms, as mentioned in the video?
A. A text box linked directly to a table field
B. A combo box that provides lookup values
C. A text box not connected to any data source
D. A button used for navigation only
Q13. What advantage does assembling all address fields into one string have when copying data?
A. It prevents users from editing the original data
B. It allows for easy copying-pasting into emails or documents as a single block
C. It protects the data from being displayed
D. It avoids the need for database backup
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-C; 9-B; 10-C; 11-C; 12-C; 13-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers how to copy data to the clipboard and then paste it back again in Microsoft Access. This topic comes from a student who spends a lot of time copying and pasting customer information from Access into other programs, such as spreadsheets, emails, Word documents, or a label writer. She wanted to know if there is a way to quickly copy the entire name and address of a customer with a single action.
Of course, this is possible with a small amount of VBA programming. Even if you have never written any VBA code before, there is no need to feel intimidated. I recommend watching my free introductory video on VBA programming, which provides all the basics you need to get started. It is available on both my website and YouTube channel. In addition, I suggest reviewing the lesson on string concatenation, which explains how to combine values from multiple fields into a single string. Once you have that knowledge, you will be better prepared for this lesson.
Let me walk you through how this works in the TechHelp free template database, which you can download from my website. In the customer form, say I want to create a button that copies a single field, like the first name, to the Windows clipboard. First, in Design View, I create a new button and name it so it is easy to identify later. Good naming habits are important as your application grows in complexity.
The first step in copying a value using VBA is to set the focus on the control you want to copy from. After focusing on, for example, the first name field, you then use the RunCommand method with the Copy command. This places the contents of that control into your Windows clipboard. You can easily test it by pasting the contents into another application or another field in your form. This demonstrates that you can use VBA to work with the clipboard.
What if you need to copy a whole group of fields, like a complete address block? Instead of copying field by field, you want a single button to combine all relevant fields and copy the entire set at once. To do this, you first create an unbound text box in your form to temporarily hold your formatted string. Clear its control source so it is not bound to any data in your table, and use a distinctive color or put it somewhere noticeable in Design View so you can always find it.
Give this box a clear and descriptive name, such as CopyBlock. In the VBA code for your new button, assemble the address fields together into the CopyBlock control using string concatenation. This combines fields like first name, last name, address, city, state, zip, and country into one block of text, separated by spaces and line breaks as needed. Once you have this text, set the focus to the CopyBlock and copy its contents to the clipboard using the same method as before.
Sometimes, you might not want the user to see this temporary field. You can make the CopyBlock invisible by setting its Visible property to No. If you do that, remember that you cannot set focus to a hidden control. To work around this, make the box visible just before copying, and then hide it again right afterward. To ensure everything works smoothly, especially when hiding controls, move the focus back to the button or another visible control before making CopyBlock invisible. Optionally, you can add a beep or other indicator to let users know the operation is complete.
If you see a brief flicker when showing and hiding the CopyBlock, you can temporarily turn off screen updating using DoCmd.Echo False. However, use this carefully, because turning off screen updates will hide all messages until you turn it back on. If an error occurs while the screen is off, it might appear as if the application is frozen. Just remember to always turn screen updates back on at the end.
There are also Cut and Paste commands available using the same RunCommand method. For example, to create a Paste button that inserts the clipboard contents into a notes field, set the focus to the Notes control and invoke the Paste command. Always give your buttons descriptive names in the properties sheet, like PasteButton, rather than leaving them with generic names like Command34. It makes your code much easier to maintain.
Setting focus to a field and pasting will normally overwrite what's already there. If you prefer to add the new text at the end, move the cursor to the end of the field by adjusting the SelStart property after setting focus. This way, pasted text appends rather than replaces. You can also enhance this further by checking if the field is empty and, if not, inserting a new line before pasting.
I have many more examples and tips that expand on these ideas. VBA allows you to customize Access forms in countless ways, and I enjoy sharing these solutions with you. If you would like, you can find hours of additional instruction on VBA programming and advanced developer techniques on my website. There are ongoing lessons covering everything from custom ribbons to shortcut menus.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Copying a single field value to the Windows clipboard using VBA
Creating a button to copy the FirstName field
Using SetFocus in VBA to select a form control
Using DoCmd.RunCommand acCmdCopy to copy data
Concatenating multiple fields into one string for copying
Creating an unbound text box to assemble and display combined data
Formatting a multi-line address block using concatenation and vbNewLine
Copying a combined address block to the clipboard with VBA
Handling visibility of hidden controls during clipboard operations
Temporarily making a control visible for clipboard actions
Switching focus to another control before hiding a recently used control
Using DoCmd.Echo False to prevent screen flashing during clipboard operations
Using DoCmd.RunCommand acCmdPaste to paste clipboard data
Creating a paste button to insert clipboard data into a form field
Using SelStart and Len to append pasted text to the end of a field |
