Access 2007 Access 2010

Prompt Users for a Name in Access 2007 and 2010

When you are creating a Report in Access, you can include as much, or as little detail as you like. For instance, you can prompt the person creating the report to add his or her name to the footer of the Report.

Follow the steps below to learn how:

  1. On the Home Tab click on the drop-down arrow under View, and select Design view.
  2. You can, of course, also right-click the Report in the navigation pane and select Design view.

If your footer is not visible:

  1. In version 2010, right-click near the bottom under the Details section and select Form Header/Footer.
  2. Go to the Arrange tab in version 2007 and and in the Show/Hide Group, select Report Header/Footer.

Add a text box to your footer:

  • Go to the Design tab in Report Design tools and click the Text Box controls button.
  • Click and drag in the footer to draw your text box.
  1. In the Label Control, enter the label that is intuitive for this field, such as Prepared By:.
  2. In your text box, key in =[Please enter your full name].
  3. Save your report.

Now, when a user runs the report, a dialog box will appear that says Please enter your full name, along with a field for the user to enter it.

That name will now appear in the footer of your Report.

Access 2007

Create a Chart in Access 2007

Access tables store lots of raw data such as contact names, sales figures, prices, salaries, gender, etc. Numbers are great for calculation but they are not ideal when you need to describe the data to another person. Charts and graphs which summarize data visually, enable us to communicate data value and trends not only for ourselves, but also for inclusion in a business report.

Follow the steps below to build a new chart in Access:

  1. Open a blank form in Design view.
  2. If necessary, click the Design tab.
  3. Click the Insert Chart button.
  4. Drag the mouse pointer to define the chart’s area.
  5. Select the option button to display your database’s tables, queries or both.
  6. Click the table or query to provide chart data.
  7. Click Next.
  8. Click the first field to add to the chart.
  9. Click the Add button.
  10. Click the chart type you would like to insert.
  11. Click Next.
  12. Verify that the chart appears the way you want and then click next.
  13. Key in a name for your chart.
  14. Select the option button that reflects whether you want to display a legend or not.
  15. Click Finish.

You an see what your chart will look like by using the Preview Chart button at the top left of the Wizard page.

Access 2007

Hiding the Ribbon in Access 2007

As you all know, the latest version of MS Office no longer has toolbars. Instead, there is the fancy Ribbon, which you either love or hate according to the mail I receive from my readers. Some folks love it because they think it is easier to use and more intuitive than earlier versions. Other folks hate it because it is so gosh-darned permanent! Simply put, you cannot customize the Ribbon like you could the toolbars of yore.

Unfortunately for developers, the Ribbon object is not as easy to control as was the command bar object of days gone by. MS Access developers can show and hide the Ribbon programatically, using the commands below:

DoCmd.ShowToolbar “Ribbon”, acToolbarNo


DoCmd.Showtoolbar “Ribbon”, acToolbarYes

Access 2007

Easy Tricks to Make Access 2007 Forms Run Faster

Giving your form less data to digest can make it run more efficiently for you.

The less data MS Access has to load into memory when you open your form, the better it will perform for you. If your form is to be used solely for entering data rather than for data searches, it makes sense to change the data entry property in your form so a blank record opens directly. Otherwise, Access will read all the records in the file before it displays the blank record at the end of the record set.

Follow the steps below to learn how:

  1. Open the form in Design View and click the Selector button.
  2. In the form’s property sheet, click the Data tab.
  3. Click int he DataEntry property text box and select Yes.

You can improve performance also by using only default formatting and properties for most or all of your form controls. Your form will load faster because Access does not have to load the non-default form and control properties. If you must change the defaults for most of the controls in your form, create one control with the properties and make that the default control.

Follow the steps below to add a control to your form and change its defaults for those compatible with the requirements of your form.

  • With the control selected, go to Format | Set Control Defaults.

Now, when you add the control to your form, it will have the same properties as the first one. MS Access saves only the properties of the default control; it does not need to store each control’s individual properties.

Access 2007

How to Switch From Design To Runtime Mode in Access 2007

Once you have your Access object finished to perfection and you are ready to let the masses use your creation, you will need to ensure that when it is opened, it will not run in Design mode. You want your application to open in Runtime mode so that users will be prevented from modifying or accidentally deleting objects that you have made available to them in the Startup form.

After specifying the Startup, you will need to change the MS Access 2007 database filename extension so that it will open in runtime mode.

To ensure that your mdatabase opens in runtime mode, follow the steps below:

  1. Open the database.
  2. Click the Office button.
  3. Click the Access Options button.
  4. Click Current Database on the left side of the window.
  5. Under Application Options, click the drop-down arrow of the Display Form text box.
  6. Select the name of the form you want to be your Startup form for your application.
  7. Click OK.
  8. Click the Office button.
  9. Click Close Database.
  10. Use Windows Explorer to navigate to the database icon.
  11. Right-click the icon and select Rename.
  12. Type the name of your database and press Enter.

The steps above will create a locked-down version of your MS Access 2007 database.

To open the database in runtime mode:

  • Double-click its icon in Windows Explorer
  • Open it using a shortcut on the desktop
  • Use the /runtime command-line switch.

To open the file again in Access in design mode, you need to rename it with an .accdb filename extension.

Access 2007

Add a Total Row to your Access 2007 Table

Prior to Access version 2007, you could calculate totals of your data in query, report or form view. Now, Access 2007 lets you add a Total row field to your tables which seems to make a lot more sense doesn’t it?

Let’s suppose you want to find out the average number of hours your employees worked per week in August.

Follow the steps below to add a total row to the hours field column in your Employees database:

Open your Employees database.

  1. In the Navagation pane, double-click the tale that records the hours worked for the month of August.
  2. Click the Hours field name in the open table.
  3. Click the Home tab.
  4. Click the Totals button.
  5. Scroll to the end of the Hours column and click the Total row field’s list arrow.
  6. Select Average.
  7. To find the minimum or maximum hours worked by one employee, select eith Maximum or Minimum.

To obtain the total of all hours worked for the month, select Sum in step 7.

Access 2007

Put Finishing Touches On Your Access 2007 Forms

Access 2007’s new Layout view allows you quickly add finishing touches to the design of your form.

In the Layout view, you can add a form title, date and time field to the form’s header. You can even add another field control and automatically readjust the tab order.
Follow the steps below to make adjustments to your Access 2007 form:

  1. In the Navigation pane, double-click the form you want to change.
  2. On the Home tab, click the View icon and select Layout View.
  3. In the Controls group, click the Title command to add a title to the form header.
  4. If you wish to change the default title, click and drag the text in the text box and type the new title.
  5. In the Controls group, click the Date & Time icon to add a date and time control to the form header.

You could easily add a missing State field to the form by following the steps below:

  1. Click and drag the State field from the existing field list to a location above the Zip Code field.
  2. Click anywhere in the form background.
  3. Click the Arrange tab.
  4. Click the Tab Control command in the Control Layout group.
  5. Click the Auto Order button.
  6. Click OK.