Help Getting Started With Access Tools

This page contains step-by-step instructions for using these Microsoft Access guru tools:

View screen prints for these tools.

What To Do First

Put a copy of AdminTools.mdb into the folder where your target database resides. (Keep an untouched copy of AdminTools.mdb in a safe location.) When opening AdminTools.mdb, if you're using a later version of Access, it will ask you if you want to convert the database. Go ahead and convert it. After opening AdminTools.mdb, import the objects from your target database (File - Get External Data - Import). Now you're ready to go to work. As you change or add objects, export them back into your target database using the export tool provided, because it keeps track of when objects were last exported.

As you export back to your target database, you may also need to export the modules vbFunctions and vbMainMenu if your code makes use of them.

Working With the Main Menu

By default the MainMenu form is the startup form. You can change that (Tools - Startup). Most forms you can close by pressing Ctrl-F4, but code behind MainMenu prevents closing this form in this manner. That's by design so that users cannot close this form in order poke around behind the scenes. But as a developer, you can use Ctrl-D to put this form into design mode, and then Ctrl-F4 will close it. Where does Ctrl-D come from? That comes from the autokeys macro (discussed below).

As shown on the MainMenu form itself in red, type the three letters (dev) to display the development controls. With the development controls showing, you can easily add and edit menu items. By default, the menu contains menu items for navigating to your development forms. Once you finish adding menu items for your client, you can delete the entire branch that goes to your development forms, leaving only what you want your client to see. These menu items are stored in tblMainMenu, and so you'll want to export that table to your target database. Also export the vbMainMenu module to your target database.

Dress up the MainMenu form for your client. For the image, use their logo (change the Picture property of the current image). Get rid of the red labels at the top that are for development only (either delete them or make them invisible).

Using Shortcuts in the Autokeys Macro

Open the autokeys macro in design mode to see the various shortcuts. Most of them use the Ctrl key, and most of them take effect on the active form. For example, use Ctrl-D to switch the active form to design mode, and use Ctrl-R to run the form again in form view. I know that Access has icons to do the same thing, but personally, I like to use keyboard shortcuts instead of a mouse.

Some of these keyboard shortcuts really save a lot of time. For example, if you want to switch labels from on top of the control to the side of the control, or vice versa, that takes a lot of mouse manipulation to drag, resize, and reposition. But simple shortcuts make it easy. And you can select multiple controls to do it all in one step.

Add your own favorite shortcuts to the autokeys macro. Don't export this macro to the target database. This is just for development.

Plan Forms

Access has a built-in form wizard. But this tool (devMakeForm) takes giant leaps beyond that. First, when designing a brand new form, you may use an existing table or not. You have the option. If the table does not yet exist, do nothing except type in the fields you want. The tool will create your table and form at the same time. But if you want to use an existing table, the two buttons Import Table and Import Fields come into play. If your existing table resides in a different database, then use the Import Table button to get it into this database. Once your table is in this database, then the Import Fields button grabs the fields in that table and puts them into this tool. Once the fields show up in this tool, it's just as if you had typed them in from scratch, as you would if the table did not yet exist.

This tool is divided into two parts. The upper half applies to your new form as a whole. The lower half applies to the fields on your new form. In the upper half, the Caption field sets the caption property for your new form. The Special Instructions field is just for your own benefit. Put anything you want there that may help you design the form, or you can leave it blank.

Notice that you can check what command buttons you want on the form. These buttons come complete with functional code. Where does this code come from? It comes from the Template_Detail form. The code from there gets copied over into your new form.

The template forms, especially Template_Detail, are key to creating your new form. By changing the look and feel of the template forms, you also change the look and feel of your new forms. By changing the position of the first control on the template form, you also change the position of the first control on your new form.

The lower half of this tool applies to the fields. Field Caption is what the label next to the control will show. (Behind the scenes the tool strips spaces from the Field Caption to make the actual field name.) Since clients usually speak in terms of field captions instead of raw field names, we use this approach. The Description next to it applies to the Description property of the field, and you see this in table design view. It is valuable for documentation purposes.

For Field Type, select text, number, or whatever. If you select text, and if you enter a Field Size greater than 255, then it will create a memo field.

Your choice of How Entered affects whether the control will be a textbox, combobox, checkbox, or just automatically calculated. Instead of a wizard that makes all controls the same, this tool offers a choice. If you select "Choose from a list" then it will create a combobox. This, in turn, offers more choices for the RowSource of the combobox. Where it asks "What List?" you have three choices. For a value list, enter the values, separated by semicolons. For a SQL statement (query), enter the SQL statement (Select Fieldname From Tablename Order By Fieldname). If your SQL statement contains multiple fields, then the combobox will be created with multiple columns. Finally, for a simple table or query with no "Select" statement, just enter the name of the table or query. Your combobox will be created along with it's RowSource property.

A subform is automatically created when you set Repeating to Yes. It's related child table name is determined by Heading next to it. You may have multiple subforms, each with with it's own related table. Repeating fields will be created on a subform instead of on the parent form.

Decide on the order of fields using the list at the bottom. This doesn't have to be perfect, because you will move controls around anyway after the initial creation. But setting the order here determines the initial tab order.

The two buttons, Delete Form and Delete Field, delete different amounts of information. The Delete Field button deletes the currently selected field for the currently selected form. Of course, neither the field nor the form have been created yet, and so this means that it's deleted from the design. The Delete Form button deletes not only the form, but also the fields that belong to that form. Use this button after you have successfully your created form if you want to get rid of the extra information.

After you enter all the information for the fields on your new form, click the Create Forms button. That opens another tool (discussed below) where you can create one or more forms at once.

The Create Forms button goes to the same place as if you had selected devCreateForm in the Go combobox below. That Go combobox lists all forms that start with "dev," including any of your own that you may add to the database.

Create Forms

The previous tool planned your form. This Create Form tool (devCreateForm) takes that plan and actually creates the form, along with the table if it doesn't already exist.

Your new form will be based on a template form. It defaults to Template_Detail. If you want to create your own template forms, and if you want them to appear on the combobox list here, then start their name with Template, and they also will appear on the list. Template forms have sample controls on them, and the control names must be exactly what the program expects in order for the creation of new controls to work. Examine the code behind this form to see what control names it expects. Comments in the code behind this form provide further documentation about how the template form is translated into your new form. By modifying either the template form, or the code behind this form, or both, you can control your own form creation.

Notice that you have a choice of label position (above or beside). If you create the form one way and don't like it, you can create it again the other way. Remember, the autokeys macro has a keyboard shortcut for changing the label position as well.

Notice the checkbox to optionally include a Find combobox (cboFind). What's that? It's a combobox at the top of the form to use for selecting which record to go to. If the client often goes to old records, to view or edit them, this is an easy way to do it. This tool can automatically create such a combobox for you, along with functional code to make it work.

From the list of Forms to Create select one or more. This list depends on the forms and fields that you have planned in the previous tool. If you delete a form from the previous tool, then that form will also be deleted from this list.

After you select one or more forms the Make Forms button becomes enabled. Click that button and watch the program go to work. Instantly you have your new form, new table, optionally subforms and related tables. Now open your newly created form in design mode, and move the controls around to suit you.

Edit Forms

When your forms get complicated, with lots of controls and lots of code, you may find it easier to navigate through the code by using the Edit Forms tool (devForms). From the combobox in the upper left corner, select a form to edit.

As you know, VBA function code can be connected to either the form or to the controls on the form. That accounts for the two listboxes on the far left and the far right. The one on the left lists functions connected to the form. The one on the right lists controls on the form. As you make a selection from either listbox, the corresponding VBA code shows in the large middle textbox. Find code fast this way.

The large middle textbox is enabled so that you can copy snippets of code, but editing has no effect here. To edit the code, click the Edit Function button. It takes you to the module where you can edit. It takes you to the first function displayed, if more than one is displayed.

The three buttons, Design Form, Preview Form, and Save Form are self explanatory. The Close Forms button closes the forms you have been editing. As you select forms to view their functions and code, they become opened in design mode. So the Close Forms button is a convenient way to close them all at once. If any of the forms have been modified, you will be prompted if you want to save them or not.

Analyze Queries

Queries can become like spaghetti, a big tangled mess. Use the analyze queries tool (devQueries) to help de-tangle them. Select a query from the combobox at the top. Now you see: 1) a list of fields in that query, 2) the source table from which each field comes, 3) other queries that this query uses, 4) other queries that use this query, and 5) its SQL statement.

From here you can navigate by clicking. Click a field to open the table and go to that field. Likewise, click the source table to open that table. To go to another query, either higher or lower on the hierarchy, click on that query in the Uses listbox or the Used By listbox.

The Comment field at the upper right is for your notes as a developer.

The SQL statement you can edit right here. This is especially helpful in cases where a table name has changed or where a field name has changed, and going into the Access query design no longer shows what you need it to show. Any editing you do here has no effect until you click the Save Sql button.

To make editing the SQL easier, you may use the Find/Replace feature. Suppose you want to change a table name in the SQL statement. Double-click on that table name in the SQL in order to highlight it. Then press Ctrl-C to copy it. Then tab to Find in SQL and paste (Ctrl-V) in the table name. Tab to Replace in SQL and enter the new table name. Tab to the Find/Replace button and press Enter. Again, these changes are not final until you click the Save Sql button.

To try the SQL before making it final, you can select all (Ctrl-A or click and drag to highlight it all), copy it, open a new query, go to the SQL window, paste in the SQL, and then try it.

The Refresh button updates the table behind this form with the latest information about the queries in your database. Use this button only if you don't see what you expect to see.

The Delete button deletes only the query information from this form. It does not delete the query from the database.

The Back button takes you to the previous query that you viewed on this form. Repeated clicking takes you back and forth between two queries. This is an easy way to compare two queries.

The Run button runs the selected query. The Design button opens the selected query in design mode.

Make Reports

If you plan to make reports, this may be one of your most time-saving tools (devMakeReport). Here's the concept. Generally speaking, a form and a report draw their data from the same table or query. Therefore, it should be possible to point a field on the form and say, "I want that field for the report." This tool allows you to do that. It creates query and report, all in one step.

Unless you have a ready made query for your report, the first step is to tell the form to listen to you when you say, "I want that field for the report." To accomplish this, the form needs code in its KeyPress event, and this can be done instantly and automatically. Here's how. In the upper right combobox, select the form. Then press the Add Code button. The KeyPress code is now in place.

Now that the KeyPress code is in place, click the Go to Form button next to the Add Code button. Here is where you select the fields for the report. Focus on the first field and press Ctrl-E. As you do this the status bar shows the field name. That's the only feedback you see. Go to the next field and do the same. After selecting all the fields by pressing Ctrl-E, close the form.

You should be back at the Make Report form. Click the Refresh List button, and you will see your form on the combobox list (Forms With Chosen Fields for Report). It will no longer be on the Forms Without Chosen Fields list. Because you have chosen the fields for the report, the form has moved from one list to the other list.

From the combobox (Forms With Chosen Fields for Report) select the form, and now you see the fields for your report showing in the listbox in the center (Fields for Report). For each field you have options, most of which you decide by checking checkboxes. The options to the left of the Fields for Report listbox apply to individual fields. The other options to the right of the Fields for Report listbox apply to the report as a whole. For now, let's concentrate on the left side, the options for fields.

You may want a field to be in the report's query in order to use as criteria or as a grouping field, but you may not want to visibly show that field on the report. Therefore, the checkbox Show Field on Report allows you to show that field or not. Similarly, the checkbox Use Field in Criteria for Report tells the Report Criteria tool (discussed below) to use that field for criteria. A field can both show visibly and be used as criteria, or it can be one or the other.

Likewise the checkbox Use Field to Group By in Report will create a grouping, either with or without head and footer as you choose. Grouping is a good way to sort the report's data.

If you choose to have a footer section after the group, do you want to show a summary calculation? Create the formula with a click of a button (Sum, Avg, or Count). If you want a different formula entirely, then just enter your own formula in Formula for this Field in Footer.

Now that you have completed options for the fields, move on to options for the report as a whole. These options are on the right side.

Will you base your report on an existing query or on a new query? Make your choice. If it's a new query, this tool will create that for you automatically. All you need is its name. If it's an existing query, then you can skip the first process of pointing to fields on a form with Ctrl-E.

Finally, choose a template from which to pattern your report. At the Report Template combobox you may select any report in your database. It defaults to "Template," and this set of tools comes with one report called Template. But you may choose any other report for your template.

From your template, you may copy less or more, allowing your new report to be slightly different from the template report. Check whether you want to copy the Report Header, Report Footer, Page Header, and Page Footer.

Last step? Click Make Report. What time did you save? You saved time making a query from scratch. You saved time making a report from scratch. You saved time making group sections and summary controls from scratch. Finally, you saved communication problems with your client. Your client sees a field on a form and says, "I want that field on a report." This saves you from having to translate the field on a form to a field in a table to a field in a query. The tool thinks through those steps for you.

Edit Reports

To edit a report, normally you open it in design mode. But you have to click in a lot of places in order to see what's what. The Edit Report tool (devReports) puts more information in front of you.

The Sections listbox and the Groups listbox shows you at a glance which sections and groups are visible or invisible. And you can change the Visible property with a click of a button.

As you highlight each group, you can also view or edit the Group Control Source (which is one of the fields in the report's record source). The Edit Groups button takes you to the built-in Sorting and Grouping wizard that Access has. Use the Refresh Groups button if you have added a group, because this updates the listbox to show your newly added group.

Also as you highlight a section or a group, you see listed the labels and controls that belong to it. When you select a label or control, you can view or edit some of its properties (font, alignment, caption, control source) below. You may select more than one at a time and change them all at once.

The steps to change label or control properties are: 1) select one or more labels or controls, 2) choose the properties you want, 3) click Apply Changes, 4) click Save Report when you are ready to make these changes permanent.

Notice the Control Source field under the Controls listbox. It's enabled when one (only one) control is highlighted. You can change the Control Source to a formula by clicking one of the formula buttons. Again, click Apply Changes when done changing that control, and click Save Report when you a ready to make these changes permanent.

The Preview Report button opens the report in preview mode, and the Design Report button opens the report in design mode.

Use the Close Reports button to close any reports you have edited (this tool has automatically opened them in design mode in order to make them editable). If you omit using the Close Reports button, you will be prompted about closing the reports when you click the Close button to close this form.

Report Criteria

The Report Criteria (RptCriteria) tool does not start with "dev," because it is meant to go into your target database. Yes, it's a development tool, but it also is usable for the client, similar to the MainMenu form which has development controls built in. As you export the RptCriteria form, also export the lkpReports and lkpReportsCriteriaFlds tables to the target database.

This form allows you to select any report, include an entirely different set of criteria for each report, and then print or view that report. Automatically developing the criteria for the reports is the most time-saving feature of this tool.

The Select Report listbox by default shows all the reports in your database. But you want your client to see only certain reports. You don't want to display reports still in development or template reports. How do you hide these? Go to the Admin tab below the listbox. Select the report you want to hide, and click Hide This Report. To unhide a report, do the opposite. First click Show Hidden Reports, then select the report, and click Unhide This Report (same button as Hide Report, but the caption has changed).

Also on the Admin tab, in addition to deciding which reports you want to show, you also decide which fields to use as criteria fields. If you previously used the Make Reports tool to make the report, then the fields may be already decided. But if not, you can select certain criteria fields here. There are two ways to do it. First, you can select a field from the Fields in Report combobox. Then check one of the checkboxes below it in order to use this field as criteria for the report. The two checkboxes represent two kinds of criteria. For ordinary criteria check Use This Field as Criteria. But for multiselect criteria (a series of values separated by "OR") check Use Multiselect Listbox Criteria.

The second way to make these field selections is to click the Edit Fields button. It brings up a query showing all the fields at once, and you can edit this directly. As you do this, you can also edit the label captions and sort order for the criteria controls.

Find more help in two places. First, the Help button provides an explanation of what we have just said here. Second, comments at the top of the module behind this form (RptCriteria) provide more detailed and technical help about creating criteria controls on this form.

After you have used the Admin tab to make your choices of reports and fields, set its visible property to false. Then the client won't see it.

The Report Info tab is for the client to see. Either you or the client can enter values in the two fields there. The Name field is an alias name for the report, not the actual name of the object in Access. This is the user-friendly name that you want to show in the listbox above. The Description field optionally provides more information about the report or its purpose. For example, you can enter "Run this report weekly," or "This report is for the managers."

Assuming you have selected certain fields as criteria fields, the steps for printing a report are as follows: 1) select a report in the listbox, and when you do, the controls associated with the previously chosen criteria fields become visible, 2) enter the criteria, and 3) click View or Print.

Export Objects

Use the Export Objects tool (devExportObjects) to export modified forms, reports, etc. to your target database. It's easiest to have your target database and this development database in the same folder, because the Export To combobox automatically lists all Access databases in the current folder. However, you may enter any path there for your target database.

The large listbox lists the objects in this database. To narrow down this list, to make it easier to see what you want to see, use the checkboxes at the right. You have a choice of showing development objects, or non-development objects, or both. What's the difference? Development objects start with "dev." So if you create your own development objects, in addition to the ones already in this database, and if they are not for the client's consumption, then start their names with "dev."

Other checkboxes allow you to show any combination of forms, tables, queries, reports, macros, and modules. Since we cannot export linked tables, the list shows only local tables.

Another way to filter the list is by time of export. The Show Objects Exported combobox contains selections of different time periods. For example, you may view all objects exported before today. Or you may show all objects never exported.

In addition to filtering the list in these various ways, notice also that you have sorting options. You may sort alphabetically, by most recent export date, or by object type.

All of these filtering and sorting options stick. The next time you open this form, it will remember your last selections. And, of course, the nice thing about this form, is that it also remembers the exact date and time each object was exported.

Select one or more objects from the list box, select your target database, and click Export. If the object already exists in the target database, it will be overwritten.

As you export back to your target database, you may also need to export the modules vbFunctions and vbMainMenu if your code makes use of them.

View screen prints for these tools.

eXTReMe Tracker