Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Discover how to manage data entry and reporting tasks more efficiently using Access 2010. Author Adam Wilbert presents lessons on designing forms, organizing and displaying data with form controls, creating flexible queries, and building a form-based navigation system. The course also shows how to build reports from wizards and queries, highlight important data with conditional formatting, and automate reporting processes with macros.
We've been hearing of the so-called paperless office for quite some time now, but that promise is yet to become a reality. There's just no getting away from having to fill in paper documents sometimes. Luckily with a little planning and help from Access we can streamline some of the process when it comes time to filling in preprinted paper documents and forms with information from our database. For this exercise we'll look at populating information about our employees into a preprinted government employee form such as this form i-9. The first thing we need to do is get an image of our preprinted document. We can get this image in a number of ways; you can scan it in using a scanner or export it from a PDF file.
However you do it for the best printed quality you should capture your image at 300 pixels per inch. It's also important that it's not scaled. Next, we need to prep this image a bit to overcome what I consider to be a bit of a quirk with Access reports. In Access you can't set page margins to zero, so if we put an image of our document that's exactly the same size as our printer paper Access will try and wrap that image on the multiple pieces of paper. To overcome this we need to crop down our image, a quarter of an inch on all sides. So our final image will be 8"x10.5" at 300pixels per inch this works out to 2400pixels x 3150pixels.
Then when we import the image back into Access it can accommodate those quarter inch margins. Now if you need additional resources for image preparation, you can find them here at Lynda.com, in the course of Scanning Techniques for Business and Home, or Acrobat X Tips and Tricks. Now once we have a preprinted document in it, it's time to get started in Access. Now back in Access we can start building our i-9 report .We go up to the Create tab, we will do a new report in Design View. First let's go and get rid of these page headers. I'll right-click on this Detail bar and turn off the Page Header/Footer.
Next we'll double check our margins. I'll go to Page Setup and make sure our margins here are set to quarter-inch, which they are. Then we'll go ahead and set up this Detail section to accommodate the image. Under the Property Sheet for the Reports I'll go to the Format tab, let me go ahead and set the width of my document to 8 inches. Up here in the Picture I'm going to click where it says None, and I'll click the Build button to select the image. We'll go out to my Desktop>Exercise folder, Chapter 5 and then I've got my i-9 Cropped image that's already been prepared.
I'll say OK, I'll change my Picture Size Mode from Clip to Zoom, and then I'll set the Detail section height. I'll click on the Details here and then I'll change the height here to 10.5. Now that background is set up, we can go ahead and start adding our fields. We'll go to the Design tab and we'll turn on our Add Existing Fields window. We'll click on Show all tables, the Employees table, and then we'll start adding the fields. So I want FirstName, now let's double- click to add them, LastName, Address, City, State, Zip, and we're going to need the middle name as well.
But we'll have to convert to it to initial and we'll do that in just a moment. So I will just have the MiddleName. Okay, now Access moved all these with labels associated and I don't need the labels because we're putting this on a form that already has labels, so we can go ahead and get rid of all of these. So I highlight all the labels here in the left side, we'll get rid of those, I'll press the Delete key, and for the purpose of the exercise I'm going to go ahead and color my font right now, I'm going to make a big, bright, bold, red, so you can see it. We go to the Format tab; I'll change it to Red, Bold, okay. Now let's start moving them into position.
On this report it goes LastName and then FirstName. We'll take the LastName and I'll drag it there. First name is going to go over here. MiddleName will go over here, Address is below, Address with the City, State, and Zip. Now we can work on the alignments a little bit, I'm going to move the Address up just a little bit here and make it wider. The City, I'm going to make wider as well, the LastName and the FirstName I'll make wider just to make sure I don't have any long names to get off.
And then actually I'm going to move them up a little so they don't overlap the line below. Next, I'm going to highlight all of these and make sure they have a transparent background and no border. So in the Format tab, we'll do Shape Fill>Transparent, Shape Outline>Transparent. Okay, the last thing I need to do is format this MiddleName, right now it's showing the whole MiddleName whereas the form is just asking for the Middle Initial. Let's switch to the Design tab and open the Property Sheet. I'll click on the MiddleName field here and we'll look at the All tab, now I can and change with the MiddleName textbox displays with a formula, but first I need to change the name here.
If I change the formula first then the name will create a circular reference, because it will think I'm referencing itself instead of referencing the field from the original table. So I'm going to change the name of this textbox here to Middle Initial, then I can change the Control Source. So instead of just MiddleName I actually want to use a function. I'm going to right-click in this box and say Zoom, so I can have some room to type. I am going to change the font here, so it's little easier to see. The function that I can use to extract just the first letter for the MiddleName is called the left function.
So what I'm going to do here is write = Left, open a parentheses, type in a square bracket and then move to the end. Close the square bracket, comma 1, and then close parentheses. So now what happens is the Left function is going to extract the one of the first left character from the MiddleName field. We'll go ahead and say OK, and I'm going to make sure that's Right Aligned so it's again at the right-side here. I'll go to Format and make that textbox right aligned. Alright, let's go ahead and check it out.
Go to Design view>View>Print Preview, and take a look at our form. I can see that the text that is lining up in the boxes pretty well, I might want to adjust the spacing a little bit, but more or less it's looking pretty good. Now the display under the screen looks a little bit chunky and that's just because of the way it's rendering this zoomed out view. If I go and view this I would say like 200%, we'll see the text looks a lot sharper and it'll print a lot nicer than it looks from the screen. We also have two options when it comes times to print this. I can either print this document as is, and I will print the report in the background and the data at the same time or if I actually do want to print on a preprinted piece of paper, I could go back and delete the image from the background of this report and print just the data, and it should line up just right on the paper, so there we go.
It's how we can setup our Access database to help us print on the preprinted reports and documents. With a little ingenuity and some time spent in the Design View we can get everything lined up just right.
There are currently no FAQs about Access 2010: Forms and Reports in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.