Database/Access Tips

From the Database Community Forum

July 2004

Freeze columns in Access's Datasheet view

Anyone that's used Excel extensively knows how helpful the ability to freeze panes can be when working with large worksheets. This feature lets you keep specific data continuously displayed on the screen as you scroll through the worksheet. Access provides similar functionality for working with datasheets. You can freeze columns in Datasheet view and continue to horizontally scroll through records. To freeze a column, click on the field's name in Datasheet view to select the column. Then, choose Format | Freeze Columns from the menu bar. Access moves the column all the way to the left of the view and freezes it. If you freeze additional columns, they are moved to the right of the last frozen column. To return the view to normal, select Format | Unfreeze All Columns from the menu bar. Here's where you'll find a catch. Although the columns are no longer frozen on the screen, Access doesn't return them to their original positions within the Datasheet view. If you don't want your columns to be kept in the new order, be sure to select No when prompted to save the layout changes upon closing the database object.

Quickly copy table structures through the user interface

Copying a table structure is easy--it's just about as easy as copying and pasting text in Word. First, select the table that has the structure you want to copy in the Database window. Then, choose Edit | Copy from the menu bar or use the familiar [Ctrl]C keyboard shortcut. Next, select Edit | Paste or press [Ctrl]V. Access now displays the Paste Table As dialog box. To create a new table with the same structure, enter a name in the Table Name text box, select the Structure Only option button and click OK. Note that if your database contains two tables with the same structure you can also use the Paste Table As dialog box to append data from one table to the other.

Resetting changes made with the MouseMove event

The MouseMove event can be used to tell when a user has moved his mouse pointer over a form section or control. One reason to use this event procedure is to conditionally change control attributes to get a user's attention. For instance, you can create effects similar to what you often see when selecting buttons on Web pages.

Let's say that you have a form with a command button called cmdExit. You can make the font used on the command button appear bold when you move your mouse pointer over it by adding the following event procedure to the form's class module:

Private Sub cmdExit_MouseMove _
	( _
	Button As Integer, _
    	Shift As Integer,  _
	X As Single,  _
	Y As Single _
	) 

	Me.cmdExit.FontBold = True 

End Sub

If you try the procedure, you'll notice one problem--Access doesn't reset the button's font when you move your mouse pointer away from it. To reset changes made with the MouseMove event, you need to detect when your mouse is no longer over the control. Access doesn't capture such an event, so you have to attach code to the MouseMove event for whatever object surrounds the current object.

In this case, we'll assume that your command button is in the Detail section of a form. You can use the code:

Private Sub Detail_MouseMove _
	(_
	Button As Integer, _
	Shift As Integer,_
	X As Single, _
	Y As Single _
	) 
	
	Me.cmdExit.FontBold = False

End Sub

to remove the bold formatting.

Change a control's associated label's default placement

Label controls are automatically attached to most form or report controls, however, their default placement in relation to the controls that they describe isn't always ideal. For instance, the default location for a label associated with a textbox control is one inch to the left of the textbox. But a particular form's layout may dictate that the label appear above the textbox control. Although you can manually move the label, the process of moving and aligning the label controls can quickly grow tiresome.

Fortunately, you can set the default placement settings Access should apply to a control's associated label. The default placement setting is saved with the current form or report, so the change won't affect new controls you add to other form or report objects.

To change the default label location for a given control type, open your form or report in Design view and select the appropriate control button on the Toolbox. Then, if the Properties dialog box isn't visible, press [Alt][Enter] or click the Properties button to display it. The properties you'll change are Label X and Label Y. The Label X property determines how far the attached label is horizontally offset from its parent control. Likewise, the Label Y property determines the vertical offset. A positive value places the label to the right or below of the control to which it belongs. A negative value places the label to the left or above the owning control.

For example, to specify that labels associated with new textbox controls should appear above the textboxes, select the Textbox button on the Toolbox. Next, set the Label X property value to: 0". This setting will align a label's left edge with the left edge of its parent textbox.

To place the label above its associated textbox, set the vertical offset by changing the Label Y property setting to: -.2". Now, simply add some textbox controls to your form or report object -- the labels appear above, instead of to the left of, their textbox controls.

Automatically open the VBE with the Build button

The ability to create an event procedure using the Build button associated with an event listed on an object's property sheet is convenient. However, when you click the Build button, you need to then select which tool you want to use from the Choose Builder dialog box before you can get to work -- you can choose Expression Builder, Macro Builder, or Code Builder.

If you've been coding for a while, you probably want to jump straight to the code window and forget about the Expression or Macro builders. You can streamline the process somewhat by selecting [Event Procedure] from the dropdown list associated with the event property's text box before you click the Build button. However, there's an even more efficient way to get to the module window.

You can set up Access to automatically open the Visual Basic Editor (VBE) when you click the Build button. To do so, Select Tools | Options from the menu bar. Then, click on the Forms/Reports tab, select the Always Use Event Procedures check box, and click OK. From this point on, clicking a Build button associated with an event property text box opens the code window and automatically creates an appropriate event procedure stub.

Get the details about a linked table's data source

When a database contains linked tables, you may at some point need to verify information about the source data, such as the location of the source file. There are a number of ways to find this information. The technique you probably use most often is to examine the contents in the Linked Table Manager. To access it, choose Tools | Database Utilities | Linked Table Manager from the menu bar (in Access 97, Tools | Add-ins | Linked Table Manager).

Unfortunately, the Linked Table Manager leaves a lot to be desired. First, you can't resize it, and it often isn't wide enough to display the complete path to the source data. The dialog box in Access 2002 is an improvement over previous versions, but you still may find that it isn't wide enough to be consistently effective.

In addition, if a table is linked from an MDB file, the Linked Table Manager doesn't display the original table's name. If you rename the linked table object, that's what appears in the Linked Table Manager.

Fortunately, there's an easy way to find out what a linked table points to, without resorting to a code-based solution or examining hidden system objects. To quickly find a linked table's source information, open the table in Design view, clicking Yes when Access informs you that some properties can't be modified. Then, right-click on the window's title bar and select Properties from the shortcut menu. When the Table Properties dialog box appears, you'll find the information you seek in the Description text box.

Monitor a procedure's changing values

As you debug a procedure, it's often important to understand what types of modifications are being made to a variable value or property. There are a number of ways to keep track of a changing value. One tool that frequently gets overlooked is the Watch Window. This feature lets you define an expression that you want to monitor, such as a calculation based on volatile data or a variable whose contents are frequently updated. When you examine the code in break mode, you can see how the value changes as you step through the procedure.

To demonstrate how to use a watch expression, add the following code to a new module:

Sub WatchTest()
  Dim str As String
  str = "hello"
  str = str & " world"
  str = StrConv(str, vbProperCase)
  str = str & "!"
End Sub

Then, select any of the occurrences of the str variable in your code and choose Debug | Add Watch from the menu bar. Notice that the Expression text box is automatically filled with whatever text was selected in the code window. You could also enter an object property, function, or custom expression, but just except the default for this simple example.

Note that you can specify which procedures and modules the watch should apply to using the controls in the Context panel. The Watch Type panel provides three choices of how the watch results should be displayed:

Watch Expression
Break When Value Is True
Break When Value Changes

The first choice continually monitors the expression regardless of any conditions, and the remaining choices should be self-explanatory. For our example, choose the Break When Value Changes option and click OK. If it wasn't already visible, the Watch Window appears and displays details about the watches that have been set up. Because the WatchTest procedure isn't running, the Value and Type information is unavailable.

At this point, run the WatchTest procedure. The procedure execution stops as soon as the procedure attempts to change str's initially stored value. Press [F8] to step through the code and you'll see that the str watch updates appropriately as each change is made. To remove a watch that's no longer needed, select it and press the [Delete] key.

Hiding duplicates in query results

It's easy to hide duplicate entries when you run a query, even though Access doesn't go out of its way to call attention to this ability. To do so, set up a query as usual using the design grid. Then, choose View | Properties from the menu bar to display the Query Properties dialog box. Change the Unique Values property to Yes. Access displays unique records based on each field returned by the query. It's worth mentioning a few points about the recordset that the query returns. First, the results are automatically sorted, based on the order of the fields in the query design grid. Also, you should be aware that the recordset isn't updatable.

Sort data using multiple fields

If you've used the toolbar buttons or Records | Sort menu commands to sort data, you've probably been frustrated to find that it appears as though you can only sort on one field at a time. Although this is true when you're working with data in Form view, you can sort on multiple fields when the data is in Datasheet view. To sort using multiple fields, switch to Datasheet view, then select the appropriate column headings. Then, use your usual method for issuing the Sort command. Note that the fields you sort on need to be adjacent to each other when in Datasheet view, so you may need to rearrange the field order to get the results you need. Also, the order of the fields you select determines the sorting priority. For example, if you want to sort by State and then City within state, you'll need to arrange the fields so that the State field comes first.

Use constants to simplify constructing strings

When you build strings in VBA you may sometimes want to include special characters to enhance the string's presentation. For instance, you may want the text to break at specific points when creating a message box prompt or you may want to use tab characters to help align items. To create such strings, you can use the Chr() function, which returns a character based on the character code that's passed to the function.

For example, Chr(10) produces a linefeed character. If you enter the statement:

? "This is an example" & Chr(10) & "of a linefeed character."

in the Immediate/Debug window, the result is displayed as:

This is an example of a linefeed character.

While Chr() produces the desired results, VBA includes several constants that you can use instead. Here's a list of the most commonly used constants, as well as their comparable Chr() values:

Quickly toggle between object views

As you design an Access object, you'll often want to switch between views to see the impact of your changes. For instance, it's often hard to judge the effectiveness of a report or form in Design view, so you'll switch the view to examine the object with live data. You'll often need to switch back and forth between views until you get everything exactly to your liking. Depending on what you're working on, it may be faster to switch between views using keyboard shortcuts, as opposed to the typically used View toolbar button. In Access 2002, you can cycle through the views of an open object using the [Ctrl][>] and [Ctrl][<] shortcut keys. In addition to forms and reports, these shortcuts can be used with tables, queries and data access pages.

Enhancing custom record navigation buttons

Although the Control Wizard makes it easy to add your own navigation buttons to a form, the buttons don't disable themselves when you reach the beginning or end of a form's records. Instead, if you click the Previous button when you're in the first record, Access generates an error. Similarly, if you're in the last record or a new record and you click the Next button, Access also generates an error.

To avoid this behavior, you can make these custom navigation buttons self-disabling; that is, they'll disable and enable themselves as necessary. To set up the code to do this, open the VBE and select Form from the Object dropdown list. Then, select Current from the Procedure dropdown list. At the insertion point, type the following code:

btnPrevious.Enabled = CBool(Me.CurrentRecord - 1)

The CBool function converts all numbers except 0 to -1, or True. So, btnPrevious remains enabled until the CurrentRecord value equals 1. To create the self-disabling Next button, press [Enter] to add a new line to the above procedure, and then type

btnNext.Enabled = Not Me.NewRecord

As you probably know, the NewRecord property returns True when the current record is a new record, so VBA enables btnNext whenever the current record isn't a new record. If you don't allow additions in your form, however, you won't have new records, so instead you'll want to test whether the current record value is less than the total number of form records. You can set the button's Enabled property equal to this test, like so:

btnNext.Enabled = (Me.CurrentRecord < Me.RecordsetClone.RecordCount)

Under this expression, whenever the CurrentRecord value is less than the total record count, the evaluation returns True, enabling the button. Otherwise, the evaluation returns False, automatically disabling btnNext.

Easily substitute strings in place of Null values

Although you may make a best effort to eliminate Null values from table fields, it's inevitable that some records eventually have fields that are Null. Null fields may not necessarily be a problem at the table level, but they can be a nuisance when you view a query and see unexplained blank cells in a datasheet. If you work with the data programmatically, Null fields can break your procedures. Even worse, fields aren't the only area of concern when you're creating VBA procedures -- Null Variables can be equally troublesome. You may deal with such problems by incorporating special error-handling or using conditional statements and the IsNull() function to control the procedure flow, you can often use an easier method to deal with such situations.

Instead of building conditional statements with If...Then statements or the IIf() function, you can use the Nz() function. This functions specific purpose is to return a string in place of Null. The full syntax is:

Nz(Value,ValueIfNull)

The first argument is the variable or field you want to check. The second argument is the string that should be returned when Value is Null. For example, say you have a query that retrieves data from a field named Revenue. If you enter the expression

Rev: Nz([Revenue],"Information Unavailable")

as one of the query's fields, the Rev column will display the message "Information Unavailable" if the Revenue field is Null, otherwise the value stored in Revenue is displayed in the query datasheet as usual.

Note that if you leave the ValueIfNull argument blank, a zero-length string is automatically returned when Value is Null.

Displaying dialog boxes in Access without API calls

Prior to Office XP, using pre-existing file management dialog boxes required you to use ActiveX controls or Windows API calls. Office XP addresses this shortcoming with the addition of the FileDialog object. Anyone that's used such techniques in the past will definitely appreciate the simplicity of code like:

Sub OpenDialog()
	Application.FileDialog(msoFileDialogOpen).Show
End Sub

which is all that's necessary to display the File Open dialog box. The other dialog boxes directly supported within Access are a File Picker (msoFileDialogFilePicker) and Folder Picker (msoFileDialogFolderPicker).

Set properties for multiple controls at once

If you have to assign the same property setting to multiple controls on a form or report, you don't have to waste time configuring each control individually. With the Properties sheet displayed, select all of the relevant controls. If the controls aren't adjacent, hold down the [Shift] key as you click on each control. Then, simply assign the appropriate property value in the Multiple Selection property sheet.

Require that specific fields contain data

Although it's usually okay for some fields in your tables to be blank, almost every application has fields that absolutely must be filled in. If missing data is causing headaches, you can take steps to prevent incomplete records from being created. Setting a field's Required property at the table level prevents users from being able to save a record if a required field is Null.

Simply open the table in Design view, select the appropriate field, and set its Required property to Yes. If your table already contains data, Access prompts you to check whether existing records violate the new setting when you save the table. If you click No, the existing Null values are accepted as is. However, new records must adhere to the required entry rule.

Bypass personalized Access menus without waiting

You don't have to wait the default amount of time before you can access infrequently used features. Instead, you can display a full menu by double-clicking the menu name when you open it.

Create a default Access form template

If you always want to use the same standardized custom form, you can change Access's default form. To set up a custom template, create your template form by specifying all the properties you want to maintain from form to form. Then, save the form using any name. Next, select Tools | Options from the menu bar and select the Forms/Reports tab. Enter your template's name in the Form Template box to replace the Access default (Normal) and click OK. The next time you create a form, Access will base it on your form template rather than the typical Normal template.

Quickly move to specific records

Although the record numbers displayed in the text box associated with a datasheet or form's record navigation buttons aren't permanently linked to specific records, they're often helpful for finding records during the current work session. All you have to do is enter the number in the Record text box at the bottom of the form and press [Enter]. Access maintains focus on whatever control was active before using the Record text box, providing an easy way to examine data from records that may be far apart in the recordset. As convenient as this is, you might find that using your mouse to move your insertion point to the Record text box disrupts your workflow. Fortunately, you don't have to use your mouse at all--just press [F5] to activate the Record text box.

Simplify navigating subdatasheets using shortcut keystrokes

For the most part, working in a subdatasheet is just like working with a datasheet. The shortcut keys you use with datasheets apply to subdatasheets as well, so you're probably comfortable using the keyboard to navigate through data. However, there are several additional shortcuts that specifically help with subdatasheet navigation and some familiar navigation keys introduce new behavior when a subdatasheet is involved. Here's a list of some of the subdatasheet shortcuts you're likely to find most useful:

[Ctrl][Shift][down arrow]	
Expand the selected record's subdatasheet

[Ctrl][Shift][up arrow]	
Collapse the active subdatasheet

[down arrow] or [up arrow]	
Skip over an open subdatasheet to the next record in the same datasheet/subdatasheet as the current record

[Ctrl][Tab]	
Exit the subdatasheet and select the first field in the next record

[Ctrl][Shift][Tab]	
Exit the subdatasheet and select the last field in the previous record

Drill down to detail in PivotTable view

One of Access 2002's most exciting new features is the PivotTable view. Similar to crosstab queries, this view lets you look at summaries of information grouped by row and column categories. If you're unfamiliar with crosstab queries, the results look similar to something you might create in Excel. For instance, you can show sums of revenue by sales rep (listed as row headings) by month (displayed as column headings). The cell at the intersection point of a row and column contains a summary value.

Unlike crosstab queries (or even Excel's similar PivotTable feature), PivotTable view can also display the detail information behind the summary calculations. Each row and column heading in the PivotTable includes plus and minus controls that are used to toggle the display of the detail information. However, you can also drill down to the detail for a particular summary without displaying a lot of extraneous data. To do so, simply double-click on the summary cell you're interested in. The column expands to show the appropriate detail data. To rehide the data, simply double-click on the summary value again.

Print a hardcopy of Access database relationships

When you're documenting your database applications, you may want to include the same visual diagram of your table relationships that's available through the Relationships window. In Access 2000 and 20002, this is easy. Simply display the Relationships window as usual and then choose File | Print Relationships from the menu bar. Doing so displays a report preview that you can then print or save. This functionality isn't built into Access 97, but users of that version can download an add-in that performs similarly. To get the add-in, download the file PRELS80.EXE from the following Web page: http://support.microsoft.com/support/kb/articles/Q175/2/02.ASP.

Back to ZDNet/Element K Tip Archive

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.