Database/Access Tips
From the Database Community Forum
June 2004
Avoid entry mistakes by disabling AutoExpand
When you type an entry in a combobox control Access will typically attempt to complete the entry based on the control's lookup list. This is controlled by the AutoExpand property, which is set to Yes (-1) by default. Although such behavior is helpful, it can cause problems if your value list contains several items that are close in spelling, since it's easy for users to accidentally let Access choose the wrong item. You can avoid errors by setting the control's AutoExpand property to No in Design view or using VBA to set the property equal to 0. Once you've made the change users are forced to type the entire entry or select an item using the combobox control's dropdown list.
Customize keyboard navigation to suit your work style
Chances are that you take for granted how your direction arrow and [Enter] keys behave in Access. Typically, when you press [Enter], the focus moves to the next field and the contents of the field are selected. Likewise, when you press the direction arrow keys, the focus moves to the next field in the appropriate direction. You aren't forced to accept these behaviors. If you'd like, you can set up the arrow keys to move from one character to the next in the current field, rather than moving focus to the next field. You can also configure the [Enter] key to move to the next record when it's pressed or configure it to do nothing at all. When you do move focus to another field, you have the option to place the insertion point at the beginning or end of the field, rather than selecting the field's entire contents. To modify these settings, choose Tools | Options from the menu bar and click on the Keyboard tab. Then, simply make the selections you want and click OK.
Use graphics to easily identify a tab control's pages
Tab controls provide a great way to conserve form real estate. This control lets you design forms that resemble Access's Options dialog box. The control displays multiple pages that are dedicated to specific tasks and you select a page by clicking on the appropriate tab. Unfortunately, you don't have a lot of freedom in terms of tab customization--the font and color properties are dependent upon Windows system settings. However, you can make a tab stand out by adding an icon next to the text that describes the tab, in much the same way Access displays icons on the Database window's tabs or Objects bar.
To do so, select the appropriate tab and display its Properties sheet. Click in the Picture text box and then click the Build button to the right of that text box. The Picture Builder then provides you with a list of icons typically associated with Access. If none of the supplied images are appropriate, click the Browse button. You can then locate and open an appropriate Bitmap (BMP) or Icon (ICO) file located on your hard drive or network. Finally, click OK to close the Picture Builder and display the picture within the tab.
Use one form for several data management tasks
Using default settings, an Access form allows you to edit existing data, add new records, and delete records. You can modify how data is handled by changing the AllowEdits, AllowAdditions, AllowDeletions, and DataEntry properties. The first three properties are self-explanatory. When the DataEntry property is set to Yes, only new records may be added (note that AllowAdditions must also be set to Yes for DataEntry to be relevant). While these four properties let you customize a form to your needs, setting them as you design a form may not provide the best approach for determining how the form should behave.
For example, you may prefer that a form sometimes allows new entries, but that at other times it may only be used to edit existing data. Fortunately, it's easy to control how the form behaves if you open it with code. The VBA OpenForm method can be implemented using named arguments or the syntax:
DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgsThe argument we're interested in is DataMode, which accepts the following constants:
acFormAdd - Limits you to working with new records
acFormEdit - In addition to letting you add records, can change previously existing records
acFormReadOnly - Lets you only view data, no changes are allowed
acFormPropertySettings - A form's default behavior setting, causes Access to use the specified AllowEdits, AllowAdditions, AllowDeletions, and DataEntry property settings
For an example of how you can take advantage of the DataMode argument, let's say that you have a form named Customers. You then create another form with command buttons on it that control how Customers is opened. The first button, named cmdNewEntry opens the form solely for the purpose of adding new records. The second button, cmdEdits, lets you edit existing data or add new records. The last button, cmdReview, provides a read-only view of your data. The code behind these buttons would resemble:
Private Sub cmdEdits_Click()
DoCmd.OpenForm FormName:="Customers",DataMode:=acFormEdit
End Sub
Private Sub cmdNewEntry_Click()
DoCmd.OpenForm FormName:="Customers",DataMode:=acFormAdd
End Sub
Private Sub cmdReview_Click()
DoCmd.OpenForm FormName:="Customers",DataMode:=acFormReadOnly
End SubEasily simulate 3-D labels in Access forms and reports
Although most Access programmers are probably not as skilled at graphics as they are at database design, here's a simple technique you can use to add pizzazz to forms and reports with 3-D effects. Open the object you want to modify in Design view and select the label that you want to apply the effect to. Then, display the label's property sheet, and ensure its Back Style property is set to Transparent. Next, set the Font Size property to at least 12 and the Font Weight property to Bold or heavier. Finally, pick a "highlight" color, such as White (16777215), for the Fore Color property. Any color can be used as long as it contrasts with the form's or report's background color AND it contrasts with the usual label color of Black (0). Note that you may need to resize your label at this point to see all of the text.
Now, right-click on the label and choose Copy from the shortcut menu, right-click elsewhere on the form and choose Paste. Change the new label's Fore Color to Black (or any dark color). Then, simply move the new label over the old one with your mouse and adjust the position using the [Ctrl] key in conjunction with the keyboard arrows. To achieve a raised effect, adjust the labels so that the lighter text is one grid unit to the left and up from the darker text. Also try the opposite--place the lighter text below and to the right for a different illusion. If you want, you can take 3-D effect further by adding a third "shadow" color.
Verifying that update query criteria are correct
the View button provides you with a chance to see what records will be affected by an action query without actually running the query. This technique lets you double-check that your query will only impact the intended records before making irreversible changes to your data. However, if you're creating a query that depends upon criteria fields that aren't changed by the query, you may not get a clear picture of what data will be affected.
For instance, let's say that you're updating a SalesRep field based on values in an AreaCode field. You want to update all of the records for a rep that has the territory within the New York City area codes 212, 718, and 917. The update query contains two fields in the design grid: AreaCode and SalesRep. In the SalesRep column, the Update To field is set to the rep name:
"Brian Desso"The Criteria field in the AreaCode column is set to:
"213" Or "718" Or "917"Of course, you've entered an incorrect AreaCode value (213). However, if you click the View button or choose View | Datasheet View from the menu bar, you have no way of knowing that a mistake was made. The only fields shown in the datasheet are those that are about to be updated. In this case, the criteria field data that the query depends upon aren't changed, so all you see are the blank SalesRep fields.
Fortunately, you don't have to convert your query to a regular select query to provide context for the datasheet view of the records. Simply switch back to Design view and set the Update To value associated with the criteria field equal to the field name. For example, set the Update To value in the AreaCode column to:
[AreaCode]When you switch to Datasheet view, you can now see the selected area codes listed. Essentially, the query is now set up to update AreaCode with whatever value already exists in that field. Once you've verified that the right records will be processed, remember to go back to Design view and remove the extraneous updates before you actually execute the query.
Speed up data entry using the AutoCorrect feature
The AutoCorrect feature is designed to fix common typos. For instance, if you enter "accesories" in a field, Access automatically replaces the word with "accessories" as soon as you press [spacebar] to add another word. You can take advantage of AutoCorrect to quickly insert any words or phrases that you must repetitively enter into a table.
To do so, choose Tools | AutoCorrect (or AutoCorrect Options) from the menu bar. Then, enter a unique string in the Replace text box and the text that you want it to represent in the With text box. For instance, enter "zzz" in the Replace text box and your name in the With text box. Finally, click OK. From this point on, if you enter zzz in a table field, Access automatically replaces the string with your name.
Shortcuts for quickly deleting text data
Chances are you already incorporate a number of keyboard shortcuts that save you from having to use the mouse to select characters when you want to delete text from a control. For example, pressing [Ctrl] and the left or right arrow keys will move your insertion point one word to the left or right. Pressing [Ctrl][Shift] with the arrow keys actually selects the words. These shortcuts let you quickly navigate and select text, but there are other shortcuts designed to aid in deleting text that are less well known:
[Ctrl][Delete] - Deletes all text to the right of the insertion point
[Ctrl][Backspace] - Deletes the word to the left of the insertion point
Restore the last saved version of a form or report
Starting with Access 2002, you can finally undo multiple actions when working with a form or report in Design view. Access 2002 lets you undo the last 20 changes, whereas prior versions could only undo the most recent change. Even if you're using an older version of Access, there is a way to undo all changes that have been made to a form or report since the object was last saved. To do so, choose File | Revert from the menu bar. Note that this also restores any associated VBA code to its original state.
While the Revert command may be convenient when you want to undo unwanted changes in one fell swoop, use it judiciously. Once you execute the Revert command, all of your changes are lost -- you can't selectively recover changes. Essentially, the result is the same as closing the object without saving and then reopening it.
Using intrinsic constants in parameters
When you create VBA procedures that accept parameters, you may wish that you could take advantage of the constants that are automatically available when working with common properties and methods. For example, when you enter
DoCmd.Close
in a procedure and press [Spacebar], the IntelliSense feature automatically displays a dropdown list of the relevant object type constants. Now, let's say that you create a procedure like the following:
Sub DoStuff(strObjectName As String, intObjectType As Integer)
'...
'Do stuff
'...
End Sub
You need to pass an object name and type to the procedure. Although the object type is an integer value, you can use an intrinsic constant to pass the appropriate values, such as with the statement
DoStuff "EmployeeForm", acForm
However, you must already know the appropriate constant, because the VBE won't provide the list of valid possibilities as the procedure is currently written. Fortunately, there's a way to take advantage of existing constant collections. To do so, declare the parameter using the appropriate class name. For example, change the procedure to the following:
Sub DoStuff(strObjectName As String, intObjectType As AcObjectType)
'...
'Do stuff
'...
End Sub
When you subsequently call the procedure, the dropdown list of constants appears as soon as you add a comma after the name parameter.
Prevent erroneous data by applying validation rules
The more you control the quality of the data being entered into tables, the better results you'll get when you need to retrieve, analyze, or manipulate the data. To ensure that data conforms to your business needs, you can use data validation rules that prevent unacceptable entries. For example, say you have a table storing purchase information and you want to require that the quantity field always has a number greater than zero, or that a date field only contains values after a specific date. You can easily create validation rules that require correct entries and provide messages to users when information violates the rules.
To incorporate validation rules, set the Validation Rule and Validation Text properties at either the control or table field level. For instance, to require that a value is greater than zero, you can use the following property settings:
Validation Rule: >0
Validation Text: Quantity must be greater than 0.
Likewise, date field validation might look like:
Validation Rule: >=#1/1/2001#
Validation Text: Only dates from 2001 and later are accepted.
When a user enters an inappropriate value, the message stored in the Validation Text property is displayed.
Create new tables from external queries
When you create a new Access database, you'll sometimes want to work with the results of an external query but you won't have any other need for the query's underlying tables. Fortunately, you don't have to import the unnecessary data. There are a slew of ways to accomplish the task, but the easiest to do may be the easiest to overlook--you can import the query as a new table. To do so, select File | Get External Data | Import from the menu bar. Select the appropriate database and click Import, then select the queries you want to import on the Import Objects dialog box's Queries property sheet. Next, click the Options button and select the As Tables option button in the Import Queries panel. Finally, click OK. Access processes the queries and saves the results as a table with the same name as the original query.
Counting items in a report section
When a report contains several grouping levels, you'll often want to know how many records fall within each group level. For instance, say that you have a sales report that groups order details by sales rep name, country, and order date month. You'll likely want to know how many sales an individual rep made, and break down the count further by location and timeframe. You also probably want to know the grand total count of all sales made.
Fortunately, it's easy to get a count of the items in a given report section. To do so, add a textbox control to the appropriate section. Then, set the control's Control Source property to
COUNT(*)To create an overall count for the report, place such a textbox in the Report Footer section, which can be displayed by choosing View | Report Header/Footer from the menu bar.
Assign a control as a form's default action button
When you create forms, you may want them to perform a default action when a user presses the [Enter] key. For example, you may want pressing [Enter] to close the form, save a record, or abort a process by default. Assigning a default command button action that should be performed is especially helpful when a form contains several buttons. It's easy to designate the command button that should be associated with the [Enter] key. Simply set the button's Default property to Yes. Note that doing so does not set focus to the control when you view the form. Likewise, if another command button has focus, pressing [Enter] executes that button's code. However, assuming no other command button has focus, pressing [Enter] will perform the action linked to whatever button is flagged as being the form's default.
Printing headers, footers, and details on separate pages
There may be times when you'd like the detail information on a report separated from header or footer sections. For instance, a group footer may contain summary results that you want to print as a separate sheet. Fortunately, it's easy to control whether headers, footers, and detail sections begin on separate pages. Simply set the appropriate section's Force New Page property. The default setting (None) prints the current section on the current page. However, the other settings allow you to force page breaks before and after the current section. Although you probably rarely print forms, it's worth noting that form sections also support a Force New Page property.
Pick constants from a dropdown list as you code
If you create Visual Basic for Applications (VBA) modules in Access, you've no doubt used the IntelliSense feature that's built into the Visual Basic Environment (VBE). As you enter code, a dropdown list appears to show you the Visual Basic constants associated with a statement. For instance, if you enter:
MsgBox "An error has occurred.",
a dropdown list appears as soon as you press the comma key, providing a list of the constants used to define THE MsgBox function's buttons argument.
What you may not know is that you can easily redisplay this list if you need to make a change to your code. For instance, say you initially picked the vbExclamation constant, to create the statement:
MsgBox "An error has occurred.",vbExclamation
However, let's say you later decide that you'd like to change the message box to have a more dramatic appearance. To select a different constant, place your insertion point on the vbExclamation constant. Then, press [Ctrl][Shift][J] to redisplay the list of relevant constants. You can now select another choice, such as vbCritical, to replace the existing constant used in the MsgBox statement.
Return only the top values from a recordset
It's easy to create a query that returns results based on a specified number of values found in a field or a percentage of records based on a field value. To do so, you use a query's Top Values property. For instance, this property can be used to list only records where a field contains the top 10 values found in the underlying recordset. Or, you can return records where a field value is in the lowest 25 percent of all the records' values.
Let's say you have a query that returns a list of products that has a descending sort order applied to the quantity sold field. When you run the query, the records with the highest number sold are at the top. However, you want just the top 10 percent of the sorted results to show in the query result. To do this, open the query in Design view. In the upper pane of the query design window, right-click on a blank area and choose Properties, or click in the upper pane and choose View | Properties from the menu bar.
With the Query Properties dialog box open, click in the Top Values text box. If you use the dropdown arrow in the text box, Access provides a list of choices, such as 5, 25, 25%, and All. You can also manually specify a number or percentage. Once you've set the property, close the Query Properties dialog box and run the query. Your query should return just the number or percentage of records you need.
An even quicker way to apply this property is to use the Top Values dropdown list on the Query Design toolbar. Just click on the dropdown arrow and select a value, or enter your own custom value in the Top Values text box.
Remember that the sort order applied to the query determines which records are the top values. If you have a descending sort applied, the top values are the largest values. With an ascending sort applied, your top values are the lowest values.
Explicitly declare variables to avoid unexpected results
It's good practice to always use the Option Explicit statement in the beginning of your code modules to ensure that all variables are explicitly declared in your procedures. With this statement in place, you'll receive a "Variable not defined" error if you try to execute code containing undeclared variables. Without this statement, it's possible to mistype variable names, which would be interpreted as new Variant type variables. This could severely impact the results of your code, and you might not ever know it. If you do find a problem, tracking down the error can be a chore.
In Access 97, new modules contain the Option Explicit statement by default. However, this isn't the case in Access 2000 and Access 2002. Although you can manually type the statement into your modules, changing a setting in Access can ensure that the statement is always added to new modules. In Access 2000, open a module, and then choose Tools | Options from the menu bar. Then, on the Editor sheet of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel. Finally, click OK. Although the setting should already be set in Access 97, you can access it by choosing Tools | Options from the menu bar, regardless of whether you're working in a module, and switching to the Module sheet. You'll then find the option in the Coding Options panel.
Turning control text on its side
Although you can't incorporate different page orientations into a single report, you can simulate the effect by rotating text in label and textbox controls. To do so, simply set a control's Vertical property to Yes. Note that changing the property doesn't physically rotate the control, it just changes how the text is displayed -- you'll usually need to resize the control to properly show the rotated text.
Easily apply successive filters to a recordset
The Filter By Selection feature is one of the easiest ways to limit which records are displayed in a datasheet or form. If you're interested in seeing only the records containing a certain field value, you simply place your insertion point in the field containing that value and click the Filter By Selection button (or choose Records | Filter | Filter By Selection from the menu bar).
What you may not realize is that you can apply successive filters to the records using this technique. For instance, let's say that you have a query that returns information about sales orders. Among the fields returned are Country, SalesRep, and a calculated expression named OrderYear.
Initially, the query datasheet displays all records. However, you can use the Filter By Selection feature to show only the orders placed in Canada. Then, you can further filter the results to show only the sales made to Canadian customers in a particular year by selecting the appropriate OrderYear field value and once again clicking the Filter By Selection button. If more than one sales rep was assigned to the geographic region during that year, you can isolate one rep's results by selecting his name and once more clicking the Filter By Selection button. ough that rep may have made sales to customers in other countries, only sales made in Canada during the specified year are listed.
Programmatically manipulate a control's attached label
You probably most often use the Controls collection in procedures to loop through the control objects on a form or report. What you may not realize is that other objects can also have a Controls collection. For example, form and report sections, tab controls, and option groups all support a Controls collection.
These objects are obvious candidates, but what may not be so obvious is that the collection can also apply to objects like textboxes, comboboxes, or any other object that has an attached label. For a simple demonstration, add a button named cmdListCaptions to an existing form and add the following procedure to the form's module:
Private Sub cmdListCaptions_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
Debug.Print ctl.Controls(0).Caption
End If
Next ctl
End SubAlthough this example doesn't serve a practical purpose, it shows how easy it is to access related labels. When you click the button in Form view, the procedure lists all of the caption text related to a form's textbox controls in the Immediate/Debug window of the VBE.
Quickly move between referenced VBA code
When you work in a VBA procedure that calls other procedures or functions you'll often want to see the details of the code that's being called. As we've discussed previously, you can easily move to the relevant code by placing your insertion point on the name of the called procedure and pressing [Shift][F2]. This is very convenient, but two readers point out that it's also handy to have an easy way to return to your original procedure. David Finnigan, of Sydney, Australia, reminds us that you can return to your starting place by pressing [Ctrl][Shift][F2].
Daniel Stefanson takes a different approach that uses the VBE's shortcut menu. To jump to a referenced procedure, right-click on the procedure name and choose Definition from the shortcut menu. To get back to where you were, right-click anywhere in the code window and select Last Position from the shortcut menu.
Alrak's Course Resources ©2002-2007 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.