Chapter 8 Access Forms / Reports

Chapter Outline

Creating a Form

Datasheet view for your Form

Designing a Report

Creating a Report

Grouping in a Report

Summary Report / Group Totals

What’s Wrong With this Form or Report

Review of Menus

Creating a Form

A form is just another way of looking at the information in your data table or query. You can have a form that has all of your fields or only some of your fields. When we look at the database in Datasheet view we have to scroll in order to see all of the fields. We do however see several records at once. A form allows you to show (as long as they fit) all the fields on one screen, however you will only see one record at a time. Like a table, a form also has a datasheet view, which can be formatted separately from the datasheet view for your table.

Let’s create a new form. From the main Database window click on the Form tab and I suggest that you use the Wizard to create your form. The first step in creating your form is to specify what table or query that you want the information to come from and then you add the fields to the form that you want. I almost always base my forms on a query, if I want all the records I base the form on the qryTable, if I only want some of them I base the form a selection query. I am going to base this form on my parameter query. This means that I can get all the records or only some of them based upon what I type into the input box that the query gives me. As you can see in Figure 1 I have based my form on the gryLastName and I have added the fields by clicking on the >> (see figure 2). You can add you fields to the form in any order that you want (this will be the order they appear on your form. I am ready to move on to the next step.

You can really choose any layout that you want I would suggest you experiment with the different layouts to see what you get. I however generally choose Columnar for most of my forms.

AS far as style again it is your choice but I prefer the Standard style over the other ones. I can format the actual form after the wizard creates it to suite my own tastes.

The last step is a little misleading as it asks you what title do you want for your from and it really should say what Name do you want for your form. The 3-letter prefix for a form is frm and I am going to name/title my form as frmLastName. Now the wizard creates the form you by placing labels and text boxes on the form, which is why we let the wizard do it for us. The form itself I find ugly and I need to modify it a little (Figure 3).

Notice the status bar at the bottom of the window. This shows us we are on record 1 with 10 records total in our database. The navigation buttons at the bottom of the screen take you to the first record, previous record, next record and last record and lastly add a new record. Try them so you can look at all of your data. When you are done looking at your form, choose the Design View button Figure 4 so we can modify the design.

The concept you need to understand is that everything on the form, including the form itself is an ‘object’. All object have properties that you can change. For example this book is an object. One of the properties is the picture on the cover. We cannot change that but we can change all the properties for the form (there is a picture property for a form).

In design view you can change the properties for any object including the form. You will notice that the edge of your form is around 5.5 inches as shown in Figure 5. Place the mouse pointer on the edge and drag the edge to 6 inches (or more depending on the size of your monitor. Six inches is about the size of a 14-inch monitor. Also you should maximize the window to give yourself more room to work.

Every thing on this form is an object. You will notice in figure 5 that a text box is highlighted. This text box displays the contents of fldPhoneNumber when we are looking at form view. It displays fldPhoneNumber because that is what the control source property is set to (Figure 6). You will notice (Figure 5) the little black square boxes around the textbox, these just like in WORD are resize handles. You can change the size of the textbox by clicking on a resize handle and dragging larger just like you did for a clipart picture. Of course when you do this your text box will be over top of the label Snow shoes?.

Now the wizard put all the labels, textboxes and check boxes on the form for us so we do not have to. It is just easier to let the computer do this. However it is up to us to rearrange the form to our liking. Now one of the first things I do is change the darn font size to 12. If you put the mouse over top of the ruler it will turn into a down pointing arrow. Now if you click you will notice a line drawn down the screen and everything in that line is now highlighted! This is an easy way to highlight a large number of objects at once. What I am going to do is to put my mouse on the top of the screen so it turns into the proper down arrow shape and then I am going to click and drag and highlight as much as I can without highlighting the check boxes (they don’t have a font property). I was able to highlight all but two text boxes so I can just hold the shift key down and click on each text box to get them highlighted. Now take a look at the properties window. You will notice the number of properties that you can change has shrunk dramatically because you can now only see the common properties for all the objects selected. Look for font size and change it 12.

This changes the size of the text to 12 but not the size of the boxes, so the text does not fit! This may sound tricky but it is easy to fix. Put your mouse cursor on any resize handle that is on the bottom of an object and double click to automatically resize the boxes (Figure 7). This puts most of the objects on top of each other but relax we can fix it but first I want you to click and drag on a resize handle that is on the right of an object (most objects are still highlighted) to make all the objects wider. Now you can click any place to remove the highlights. This makes your label objects to wide so I just highlighted the label objects only and double clicked the middle right resize handle to resize them all smaller. You can move all your objects on the screen and put them where you like. If you choose the text box for any field, the label will move with it. If you want to move just the text box, notice that the top left resize handle is different than all the rest, so you can click and drag and only that object will move (your mouse will change from an open hand to a pointing finger). Figure 8 shows how my form view looks after rearranging and formatting it a little.

Pretty neat isn’t it. If you have done any programming in the past this would have taken hours to do the same thing. Ok I will tell you couple hits to get things lined up. For example lets look at the check boxes. What I did was to:

1. Move them all so they were more or less lined up.

2. Highlight just the labels and I set the width property to be 1.1”

3. I set the text align property to be right aligned.

4. I set the left property to 4”

5. Now I highlighted just the checkbox itself (all of them).

6. Since I wanted my boxes to be about ¼” from the label I just added the labels left property to the labels width and added .25 to get 5.35” (4” + 1.1” + .25” = 5.35”). This is what I set the check boxes left property to.

You can do this for the top property as well to get all the objects lined up smoothly. It may take a little while to do but once you are done you do not have to do it again. Speaking of which, you should save your form every now and then when you like the looks of it.

As you click on different objects you can look at all the properties that you can change. Go ahead and play around with this and get the form to look the way that you want it to look.

As you change colors note that you can change the color of your text as well. You should always try to have the text in a color that is pleasing to read and contrasts with the background color. After all black text on a black background is impossible to read. Black text on any light background works well. White text on a dark background like black or dark blue works well. It’s easy to try and see how it looks. Just click on the form view button to see how it will really look. In fact you should be comfortable jumping back and forth from design view to form view and back again.

Datasheet view for your Form

When you look at your form view you should notice that there is a datasheet view for the form as well. You should format your datasheet just like you did for your tables and queries, setting the column width appropriately, freezing a column or two to assist in identifying the record when you scroll to the right.

Designing a Report

Report Section Basics

1. Report Header Section - Will print only at the beginning of your report on the first page.

2. Page Header Section - Will print at the top of every page of your report. Of course on the first page it will be below the report header.

3. Group Header Section - Will print whenever your group changes. You sort your records on a field and have a break put in when the field changes.

4. Detail Section - Will print for every record you have in your data table or query.

5. Group Footer Section - Same as the Group Header section only this section prints after the detail section for that group. The group footer section is used to get totals for that group.

6. Page Footer Section - Will print at the bottom of every page of your report. Commonly used for the page number.

7. Report Footer Section - Will print at the very end of your report. This is used to get totals for all the records in your report.

Figure 10

Forms allow you to view the data on a screen and reports allow you to view the data on paper. With a report view you design a report definition that will be used to print a report. Creating this report definition is really easy to do especially after understanding the basics as shown in Figure 10. The report basics deal more with understanding the terminology more so than with what you should do.

First you need to identify the fields that you will need in your report. I worked for a company that gave me a report every day that had 15 fields on it. I only needed 3 of those 15, so I threw the report away because it was too hard to read. So figure out just what information you need. Think about how you want the information sorted, for example: by name, state, age etc. I am going to generate a report for my holiday list to invite them over for a party. I want a list of their name (spouse and children), cities, states and phone numbers. I will use this list to call on them up and see if they can attend the holiday party.

Creating a Report

When making a report I suggest using the wizard to help you. Making a report is very similar to making a form. The first step is to choose the table or query that you want the information to come from, in this case qryHoliday (since I do not currently have the query I have to make it first).

Using the wizard for creating a report is the same as for a form with a couple new things. Step one is the same, you choose the table or query and pick the fields that you want on your report. Step two ask you if you want to add any grouping levels?, this time we do not so just move on to the next step.

The third step is how you define the way you want the report sorted, I am going to sort my mine by last name and first name but just choosing those fields in the list boxes. The fourth step is about how you want the report laid out. Nine out of time times I never change a thing on this step. The only time I do is when I want to report in Landscape mode. For now I am just going to keep it as a tabular report in portrait. The fifth step I also rarely change and I am just going to use corporate (try some of the others, if you don’t like them just make a new report:). Finally the last step is just like the form in that it asks what title do you want when it should say what NAME do you want. Use rpt as the three letter prefix, I named my report rptHoliday.

Here is part of my report in print preview. I have labeled the different sections that you can see. The Report Header, Page Header and Detail Section. This report also has a Page Footer section but it is not visible in Figure 11.

Lets look at the same report in design view (Figure 12). Notice that just like a form there are objects, text boxes and labels. Try to see in design view and how it relates to Figure 11, print preview. You will notice that both the Report Header and Page Header have nothing but labels in them. The detail section has nothing but text boxes in them. The page footer has text boxes with formulas in them. =Now() is a formula that is in the control source property and it will display today’s date. =”Page “ & [Page] is a formula with the code for the page numbers being enclosed in square brackets [ ].

You can see a Report Footer section but notice that there is no white space under it. The white space is ‘paper’ so to say and since there is none, nothing prints in the Report Footer. If you wanted to have a Report Footer you would have to put your mouse on the bottom border of the Report Footer and click and drag to increase the white space. Then you can put objects in that section. This also holds true for the space between everyone’s name. If you move all the textboxes to the top of the Detail Section and then click and drag the Page Footer up you will get rid of the extra spacing. If you drag the right edge of any section past 6.5” (even 6.51”) you will cause an extra piece of paper to be printed (most likely blank). However in this case I don’t need to modify anything as this report was just what I was looking for.

If you wanted a list of people who liked to go kayaking you would create a similar report only basing the report on qryKayakers (the criteria for the query would be fldKayaker=Yes). In fact you could make a similar report on most any query. One report that I use all the time is my Fast List Report. I use the wizard to create a report based on the qryFastList (query criteria fldFastList=Yes) and this gives me a quick list (short list, fast list) of phone numbers of the people I call most often.

You can see that the report design view is not that much different than a form design view. Go back and look at your form design and you will see a Form Header, Form Detail, and Form Footer section, we only used the Form Detail Section. They both have the same toolbars and all the objects are pretty much the same except that forms are designed for the screen and reports are designed for the printer. This is where the report basics in Figure 11 are important. Notice the different sections. Each section prints on a different part of the paper. The report header prints only on the first page, never again. Just the opposite is the report footer, which only prints at the end of the report. The Page Header section prints at the top of every page except for the first page where the Report Header prints above it. The detail section is where all the data from you database prints. The Detail Section prints once for every record. Lastly the Page Footer prints at the bottom of every single page, even the last one. A Report Footer would print on the last page (we did not have a Report Footer in this example) but the Page Footer acts just like a footer should and always prints at the bottom of the page.

Grouping in a Report

I need to print out my own phone book, I like having my phone book computerized but I really do not want to have to turn my computer on every time I call someone up. So I need a report that is similar to Figure 13 where each group of names is on a new page, this figure showing just one page in the middle.

This report I am going to base on my qryTable since I want all the records to print out. So using the wizard I make a report based on qryTable and I am going to include almost all of the fields (Figure 14 shows the fields I selected). Now the important part of using the wizard this time is when you are asked if you want to do any grouping?

I want to group all the records by last name, the first letter of the last name. This will put all the A names together, All the B names together etc. So I add grouping by fldLastName and then I choose Grouping Options to group by the first letter of last name. You would use grouping to group all the people by state or city, or zip code for example. Specifying the grouping here is important as it enables the wizard to do a lot of work for you. You can specify grouping at any time but trust me, be sure to do it in the wizard.

After this step I just make sure that I sort by last name and first name. The rest of the steps I just allow the wizard to use what ever option. In the last step I do change the tile to reflect the naming conventions for a report and I called this report rptTable. Let’s look at this report in Print Preview, Figure 15.

Notice how the group header prints followed by all the records in that group (Detail Section) and then the Group Header prints again for the next group. Now this report does not look like what I am after but there is no reason why I cannot easily rearrange everything. The wizard does a great job of getting all the objects on the report that I need.

In figure 16 you will notice that I have increased the size of the Detail Section by click and dragging down the top of the Page Footer. I have also highlighted all the text boxes in the Detail Section (Same way as with a form). I want to make all the text font size 12 and little bigger. I will then rearrange them all. In the way I want them to be on my report.

You will notice in Figure 17 the results of my moving things around. Notice how I took a label (children, birthday) from the page header and dragged them down to the detail section. Remember that you can align your objects either by eye or using the properties like we did in form design. It may take awhile to create your reports design but keep in mind that you only have to design it once and then can use it as many times as you like. Since I do not need the page header for this example I am going to delete all the labels and shrink the page header to no paper by dragging the Group Header up to the report header. I am going to keep the two lines that are in the Page header. The top line I am going to drag and put on the bottom of the group header. The bottom line I am going to put on the bottom of the detail section. I found it easier to make the Page Header section larger, move the lines and then shrink the Page header section down to nothing. When moving the lines be careful that you do not drag them to the right as this will cause the edge of your report to go beyond 6.5” and cause an extra page to print. No big deal if you do drag them to far over (I did) just drag them back and remember to drag the white paper back to 6.5” as well.

I also do not need the Report Header. What I am going to do is to change the label from rptTable to read Phone book (change the caption property) and put this in the group header. The group header will need to be larger. I am going to move the text box that is in the group header from the left side to the right side. I also made the font size larger (to match the other label) and I made it right aligned (note toolbar).

Let’s look at Figure 18 to see what I did. You can see the report design behind the properties window. I moved things around a bit and got rid of the header sections except for the group header. I wanted you to note the Control Source property for the text box in the group header. This is a Visual Basic (computer programming language) function with the syntax: Left$(expression, number of characters). So starting from the left side this function returns one letter from fldLastName, hence the first letter. Now we did not have to program this as the report wizard did this for us :) Let’s see how the report looks in print preview.

Not to bad. It would however be nice to have each group on a separate page. Easy enough we need a page break. So what Report Section do we wan the page break in? Let’s think about this for a minute. We have the Group header printing the big letter D, followed by the Detail Section with Terri’s information. Then we have another group header section printing. We could put a page break in the detail section but that would put a page break between everyone’s name! In this sample I only have one person in each group anyway but in real life I have more records. The section that would print after the detail section would be the Group Footer section but I don’t recall seeing one.

You need to add a group footer section by choosing View, Sorting and Grouping in design view. I am not sure why it has sorting on fldLastName 3 different times but why mess with it so I am not going to worry about it. You can see how the Group Footer property is No, so you just need to change that to Yes to get a Group Footer. This is the place that you would add Sorting and Grouping if you forgot to do so in the Report Wizard.

Now we only need a page break and we don’t need any white space. You need to click on the Group Footer and look at the properties and it should not take long to see one for Force New Page. Set that to After Section and presto your report is done.

Actually there was one more thing I wanted to do and that was to make the line in the detail section thinner and dashed. To do this you need to highlight the line and change its properties for border style and width. Remember when you need to make a change 99% of the time it is a property that you have to change.

Extra little tidbit: notice how none of the people in the sample report Figure 19 have any children. It would be nice if the label children did not show up at all. You can do this by changing the label to a text box (Format, Change To, TextBox) and then putting in a formula for the control source (labels don’t have a control source). The formula would be: =IIf([fldChildren]<>"","Children","") meaning that if there is something in the field then display children else display nothing. The if statement is the same in the database except it has two I’s (IIF). This prints just a blank line.

Summary Report / Group Totals

I really don’t need any other reports for a personal phone book database so let me make a few up to give you some more concepts about reports. For example suppose I wanted a report showing me the total number of kayakers and what there average age was. I would create a report based on a kayaker query and in design view I only need to use the Report Footer Section like shown in Figure 22 and Print Preview Figure 23.

I used formulas in the control source property. The first one I counted how many records there were by using the count function: =Count([fldLastName]) which counts all the non-empty fields. In other words if someone did not have last name then they would not be counted. The second function I used was the average function to get the average age of the kayakers: =Avg([cfldAge])

If you were to group the report say by State you could put the same formulas (copy the labels and textboxes) in the group footer section to get the totals and average for that group. There are many functions that you can use but that starts to get into some more advanced topics.

A report and a form are very similar in you use the wizard to put all the objects on them. The difference is that a form is meant to be viewed on a screen and a report is meant to be printed on paper. Remember that everything is an object with properties that you can set. Also keep in mind that I simplified databases in these 2 chapters. There is a HUGE amount of material about databases that you can learn, this was only a brief introduction. I hope you liked it.

What’s Wrong With this Form or Report

Looking at the form in Figure 24 the problem is more cosmetic. Nothing lines up smoothly. The zip code is cut off so that you cannot see all of it. It is in general just a mess. You can easily line your items up by selecting them and then setting the left property for all of them the same. To get objects align across you set the top property. Specify the width property to get the object the same width, and so on.

In Figure 25 the report is wrong but can you tell why? You can not see the actual information in this screen shot but what is wrong has to do with the layout.

The report is only about 2” wide on an eight inch piece of paper. If you move the fields so that they are in a row instead of a column you will use less paper. The reason the report came out this way is because I used the report wizard. Always remember to check out your report in print preview even after using the wizard to see if the layout makes sense. In some cases you may want the extra white space, in this case it is just wasted paper.

Now in Figure 26 the problem with the following report is just the opposite. Here I have the report in a row but my row is too long because the last field is pushed to the second page. Again, there is a huge waste of paper and the second page if it were to stand alone would not make sense. This is really easy to fix. What I would try first is to change the left and right margins. If that did not work, I would try to make some of the fields a little smaller. I could also change the orientation of the paper to landscape instead of portrait.