Start learning with our library of video tutorials taught by experts. Get started

Access 2010: Forms and Reports in Depth

Populating pre-printed documents


From:

Access 2010: Forms and Reports in Depth

with Adam Wilbert

Video: Populating pre-printed documents

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.
Expand all | Collapse all
  1. 1m 27s
    1. Welcome
      1m 2s
    2. Using the exercise files
      25s
  2. 15m 53s
    1. Introducing forms
      2m 41s
    2. Designing for the end user
      45s
    3. Exploring the database
      1m 49s
    4. Creating a form with the Form Wizard
      6m 43s
    5. Refining the form in Layout view
      3m 55s
  3. 24m 33s
    1. Organizing the form elements
      7m 14s
    2. Formatting
      4m 48s
    3. Modifying the form through its properties
      6m 56s
    4. Adding a header and some polish
      5m 35s
  4. 1h 2m
    1. Introducing form controls
      3m 48s
    2. Using lines and rectangles
      2m 48s
    3. Organizing screen space with tabs
      4m 47s
    4. Adding buttons
      5m 3s
    5. Linking to external content
      4m 15s
    6. Entering and selecting data
      5m 8s
    7. Controlling input with option groups
      6m 0s
    8. Attaching documents
      6m 49s
    9. Attaching images
      5m 8s
    10. Understanding the subform control
      4m 13s
    11. Adding charts
      7m 9s
    12. Linking controls
      7m 41s
  5. 21m 42s
    1. Creating the main menu
      8m 49s
    2. Adding a splash screen with startup options
      5m 35s
    3. Creating a customer form
      7m 18s
  6. 45m 20s
    1. Grouping and sorting data
      4m 36s
    2. Understanding report structure
      6m 12s
    3. Building reports from wizards
      5m 0s
    4. Building reports from queries
      6m 34s
    5. Formatting conditionally
      6m 59s
    6. Calculating fields
      4m 35s
    7. Adding the finishing touches
      4m 49s
    8. Populating pre-printed documents
      6m 35s
  7. 15m 8s
    1. Printing reports
      3m 6s
    2. Tweaking the design
      7m 10s
    3. Automating the workflow with macros
      4m 52s
  8. 58s
    1. Next steps
      58s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
Please wait...
Access 2010: Forms and Reports in Depth
3h 7m Intermediate Feb 14, 2012

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.

Topics include:
  • Designing for the end user
  • Organizing form elements
  • Formatting a form
  • Adding headers
  • Linking to external content
  • Entering and selecting data
  • Adding charts
  • Creating a main menu
  • Creating a customer form
  • Understanding report structure
  • Building reports from wizards and queries
  • Printing reports
Subjects:
Business Forms Databases
Software:
Access Office
Author:
Adam Wilbert

Populating pre-printed documents

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.

Share a link to this course
Please wait... Please wait...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Access 2010: Forms and Reports in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked