Database/Access Tips

From the Database Community Forum

May 2004

Using an ampersand in an Access control Caption

If you've ever tried to include an ampersand (&) in the Caption property of a control, you've probably found that the results are different from what you expect. For instance,

Caption: This & That
Displays: This _That

That's because Access interprets the ampersand as a means of setting up a shortcut key combination to let you select the control. The trick is to use two ampersands (&&) instead of one in the Caption.

Caption: This && That
Displays: This & That

Open an Access object from your Windows desktop

To open an Access object, such as a form or report, directly from the Windows Desktop, simply create a shortcut to the object. To do so, in the Database Window, right-click the object you want to create the shortcut to. Then, select Create Shortcut from the shortcut menu. Click OK in the Create Shortcut dialog box. When you do, Windows places the shortcut on the desktop. To test it, close Access and double-click the shortcut icon. Windows not only launches Access, but also opens the appropriate database and the object as well.

Use the ERL() function to find code errors

The ERL() function is supposed to help you diagnose code errors by providing the line number of the statement that failed. There's only one problem--unless you manually annotate your procedures with line numbers, the function always returns 0. For example, try running the following in a procedure:

Sub ErlTest()
    On Error GoTo ErrorHandler
    DoCmd.OpenForm "ABCDE"
    Exit Sub

ErrorHandler:
    Debug.Print "Error on line " & Erl & ": " _
    & Err.Description & " (" & Err & ")"
End Sub

The text returned in the Immediate/Debug window refers to line 0. But, if you change the code to:

10    On Error GoTo ErrorHandler
20    DoCmd.OpenForm "ABCDE"
30    Exit Sub

when you re-run the procedure the error handler returns: "Error on line 20: The form name 'ABCDE' is misspelled or refers to a form that doesn't exist. (2102)"

Of course many will claim that line numbers are an antiquated concept, and manually adding line numbers to every line of code would certainly be tedious. Fortunately, you don't need to number every line--just add line numbers in reasonable increments and the ERL function will return the last encountered number. It won't exactly pinpoint where an error occurred, but it can help narrow the scope of your bug hunt. By using line numbers sparsely, you can also quickly remove them from your procedures before setting up your application in a production environment.

Simplify creating hyperlinks in Access tables

When you populate a hyperlink field in a table, you probably cut and paste the URL from your browser into the Insert Hyperlink dialog box. However, there's an even easier way to do this using Internet Explorer. First, open the Access table you're updating and Internet Explorer. Then, select the hyperlink field you want to create the link in and choose Insert | Hyperlink from the menu bar. Then, press [Alt][Tab] or use your mouse to select the browser window. Browse to the page you want to link to and then switch back to Access. You'll find that the URL is automatically inserted in the dialog box.

Quickly change table names in a query

If you've ever developed a dozen or more complex queries, then had to change one of the table names, you know how frustrating it can be to all but rebuild the queries in the Query Design view grid by changing the table name in each cell. One quick alternative is to choose View | SQL View while the query is open and then cut and paste all the SQL code into Word. Next, do a Find and Replace, changing all the instances of the old table name to the new table name. Finally, copy and paste the SQL statements back into the SQL view of your Access queries. When you go back to the QBE, the new table will replace the old one.

Indent several lines of code at once

Indenting blocks of VBA code, such as statements within loops or If...Then statements, makes reading a procedure much easier. Currently, you probably indent a code statement using the [Tab] key, and outdent by using [Shift][Tab] or deleting the invisible tab character. However, you may not be aware that the [Tab] and [Shift][Tab] techniques also work when multiple code lines are selected. The VBE in Access 2000 also provides Indent and Outdent buttons on the Edit toolbar that allow you to easily reposition blocks of code.

Calculate the percentage of records in a group

Although it's easy to count the number of records in a group, determining that number's percentage of the report's overall record count may not seem easy to do. Fortunately, it's easy to calculate the percentage. First, add a textbox control to the report's Detail section and name it txtCountAll. Then, set its Visible property to No and set its Control Source property to:

=Count(*)

Then, add a textbox control the group footer. Name the new control txtCountGroup and set its Visible property to No. As before, set its Control Source property to:

=Count(*)

Next, add another textbox to the group footer and set its Control Source property to:

=txtCountGroup/txtCountAll

Finally, set the new textbox control's Format property to Percent.

Understanding how Access stores hyperlink data

Although it isn't obvious when you enter your data, an Access hyperlink field actually stores four pieces of information: the text you see in the field, the address that the hyperlink points to, a subaddress (which isn't relevant in this case) and text that appears as a ToolTip when you hover your mouse pointer over the link. The information is parsed by pound symbols (#) and is stored in the format:

displaytext#address#subaddress#screentip

(Note that Access 97 hyperlinks only contain three parts, as it doesn't support screen tip entries.)

Of all the hyperlink elements, only the address section is required. However, unless you specify the individual parts separately, Access treats the data you enter into the hyperlink field as the displaytext portion. To create the address section, Access automatically copies the displaytext and checks to see whether you've specified what Internet protocol to use. Web pages use Hypertext Transfer Protocol (http) and Access automatically prefixes the address data with http:// if you haven't specified a protocol.

You can see this firsthand by selecting a populated hyperlink field and pressing [F2] to switch to Edit mode. Because the displaytext and address sections are separate, you can store a long, complex URL in the address section, but display user-friendly text in the hyperlink. For example, a hyperlink containing the following data:

Inside Microsoft Access#http://www.elementkjournals.com/products/showProduct.asp?prodID=19&catId=1#

will simply display Inside Microsoft Access. However, clicking the link will take you to the related URL.

Setting default print margins for datasheets

One behavior regarding datasheets that people often find annoying is the inability to save print settings with a table or query datasheet. Although reports are the object intended for output, it's common to print a query or table datasheet when you need to generate a quick and dirty hardcopy. Although you can't set a default page orientation, you can at least set the margins so that you don't have to scale them back from the usual overly generous defaults every time you print. To do so, choose Tools | Options from the menu bar. Then, click on the General tab and set the desired values in the Print Margins section. Note that this change will impact any new objects you create, not just datasheets. For instance, new reports will use the default margin settings you specified. However, the report margins can still be overridden at the individual object level and existing report objects are unaffected.

Changing the back-end data source in a split application

You'll often design an application as two MDB files: a front end that contains objects like forms, reports, and modules, and a back end containing the data tables. Although you may use the application without ever having to change which database the front end looks to for its data, there may be cases in which you need to change the back end specifications. For instance, If the back end database file's location changes, you'll have to update the front end to look to the new location. Sometimes, you'll want to change a functioning front end to use different back end data to meet a particular business need, such as providing an interface to archived data that uses the same structure as your current application's current back end database.

To change the data location information in a split application, open the front end and choose Tools | Database Utilities | Linked Table Manager from the menu bar. Select the check boxes next to the objects you want to update, or click the Select All button to modify all of the linked table information. Then, select the Always Prompt For A New Location check box and click OK. Locate and select the appropriate back end database file and then click Open. Depending on how many data sources your front end is linked to, you may have to repeat this process multiple times. When you're returned to the Linked Table Manager, clear the Always Prompt For New Location check box and click Close.

When Seek won't work on a table

As you know, the DAO Seek method provides a fast way to search on a table's index. You use this method like so,

Set rst = db.OpenRecordset("MyTable")
With rst
.Index = "Primary Key"
.Seek "=", "A1B2"
End With

Here, the code snippet opens a table-type recordset based on MyTable. Next, it sets the recordset's Index equal to the Primary Key, then hunts for the A1B2 string. However, this method won't work on attached tables, because Access treats them as dynasets, not table-type recordsets. And as you know, the Seek method doesn't work on dynasets. On attached tables, use the Find methods instead.

Using CurrentDB

Although you may want to stick with a "pure" ADO solution, you should be aware that the CurrentDB method lets you quickly access the DAO Database object without requiring you to set a reference to the DAO library. Assuming you're working with an MDB file, the CurrentDB method sets up a hidden reference to the Microsoft DAO 3.x Object Library, allowing you to work with Database properties and methods.

Eliminate extra trips to the Toolbox

When you add controls to forms and reports you'll often need to add several of the same type, such as when creating a group of option buttons or a series of unbound text boxes. In such cases, repeatedly moving between the Toolbox and the object you're designing can quickly become tedious. Fortunately, you can make the process easier. When you select the control you want to add from the Toolbox, double-click the control button. Doing so lets you add as many controls of that type as you need. When you finish, click the button again to disable the control tool.

Creating a list of the reports in a database

As part of your efforts to make things simpler for end users, you'll often want to provide them with an easy way to select reports. To help simplify the process, you can display the report names associated with your database in a listbox or combobox control. Then, you can create code to act upon whichever item the user selects. Fortunately, you don't have to manually populate the control's list with the report names, or worry about updating the list when reports are renamed, added, or deleted. Instead, you can use VBA to loop through a collection of reports and build a value list using the report names.

For example, let's say that you have a form that contains a listbox named lstReports. If you're using Access 2000 or greater, the following code will populate the list when the form is opened.

Private Sub Form_Load()
  Dim strList As String
  Dim rpt As AccessObject
  Dim rpts As Object
  Set rpts = CurrentProject.AllReports
  For Each rpt in rpts
    strList = rpt.Name & ";" & strList
  Next
  Me.lstReports.RowSourceType = "Value List" 
  Me.lstReports.RowSource = strList 
End Sub

If you're using Access 97, you can use the following procedure:

Private Sub Form_Load()
  Dim strList As String
  Dim x As Integer
  For x = CurrentDb.Containers!Reports.Documents.Count - 1 To 0 Step -1 
    strList = CurrentDb.Containers!Reports.Documents(x).Name & ";" & strList 
  Next 
  Me.lstReports.RowSourceType = "Value List"     
  Me.lstReports.RowSource = strList 
End Sub

Pass values to a report object with OpenArgs

Access 2002 is the first version to support the OpenArgs property for the Report object. Previously, this property was available only with forms. As with the form property, the report OpenArgs property is accessible only through Visual Basic for Applications (VBA) code or a macro. The property's purpose is to pass a value to the report as the report is opened by the DoCmd object's OpenReport method in the form

DoCmd.OpenReport(reportname, view, filtername, wherecondition, windowmode, OpenArgs)

where reportname is the name of a Report object and the optional OpenArgs argument accepts a Variant. Once the report is opened, the value passed to the argument becomes the report's OpenArgs property value. You can, in turn, use this value for such things as conditionally executing code statements or customizing elements of the report.

Easily let users specify output file formats

If users need to be able to export Access data from an application, you may use the OutputTo method to allow this. If so, you can provide users with the flexibility of exporting to a variety of formats without coding solutions for each contingency. To do so, just leave the outputformat argument blank. When the statement is executed, a dialog box will be displayed that lists the method's supported export formats. Note that the same technique can also be used with the SendObject method.

Center an Access report's title text

When you want to center a report title, you can do so by dragging its label with your mouse. However, this approach can take a little more trial and error than you may have patience for. As an alternative, you can try taking advantage of the Center alignment button on the Formatting toolbar. But this will center the title only within its control and not between the left and right margins of your report. The trick is to make the control the size of your report. Simply position the top-left corner of your title control at the left edge of the report. Then, increase the width of the control to the right edge of the report. Next, center the title within the control, and it will be perfectly centered between the left and right margins of your report.

Import queries as tables

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 -- 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 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.

Streamline emailing Access data

If you need to electronically send information stored in Access to another user, you may export the data to a file and then attach that file to an email message. If so, chances are that you're going through more work than is necessary to complete the task. Access has built-in functionality that allows you to easily email an Access object in a variety of formats. For instance, you could email a table as an HTML file, send a form's underlying data as an Excel file, or send a copy of a report saved as a read-only snapshot file.

You can email an open object or one that is selected in the Database window. While the appropriate object is selected, choose File | Send To | Mail Recipient (As Attachment). Access then displays a dialog box that lists the available file formats that can be used. Choose the format you want and click OK. You may then be prompted to configure settings that are specific to the format you selected. Once all of the required settings have been specified, your default email application creates a new blank email. The appropriate data is automatically attached or embedded within the email, depending on the file format and email application you're using.

Hiding database objects from view

Although you may typically want to let users access any object in a database, there may be certain times when you would that users are unable to see certain tables, queries, or other objects. If so, you can flag an object as being hidden. By default, such objects are invisible. To flag an object, right-click on it in the Database window, and choose Properties from the shortcut menu. When the Properties dialog box appears, select the Hidden check box and click OK. Note that users can still get to the object if they really want to -- and you can temporarily redisplay the object if you later want to clear the Hidden property setting. To display objects that are normally hidden, choose Tools | Options from the menu bar. Then, click on the View tab, select the Hidden Objects check box in the Show panel and click OK.

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.