Database/Access Tips Archive
From the Database Community Forum
March 2004
Display repetitive data once on an Access report
Sometimes, you may have data that needlessly clutters a report. For instance, suppose your report is listing the fields strCompany, strFirstName, and strLastName. If there are multiple people listed for each company, and the report is sorted by company name, repeating the company information is unnecessary. Your first instinct may be to create a group header based on the company name, but there's an easier way to hide the redundant data. First, open the report in Design view and select the control that displays repetitive information. Then, display the control's Property sheet and set the Hide Duplicates property equal to Yes. Finally, save and preview your report. If the data in the modified control is the same as the data from the previous record, the control is hidden.
Update data with Access's default field value
You probably know that you can set up a default value for Access to enter into a field when a new record is created. This can be done in the Design view for a table or form by setting the Default Value property. Unfortunately, you sometimes may set a default value after you've already entered records into the database. When you do so, the existing records aren't automatically updated to equal the new default. However, if you're editing a record and you want to update the field to the current default, you can do so with a keystroke shortcut. To do so, simply select the appropriate field and press [Ctrl][Alt][Spacebar].
Avoid confusion with data exported from Access
Exporting data to text files is a longstanding method of transferring data between different applications and platforms. If you export to a delimited text file from Access, you're given the choice of including field names in the first row of the file. However, if you export to a fixed-width text file you aren't given the option. If the application on the receiving end of your text file can read the first row of the text file for names, and if the field widths allow for it, you may want to consider manually adding the names to the resulting text file.
Copy data from the previous record in Access
As you enter records in an Access table, you may find that much of the data remains the same from record to record. In these cases, you can quickly copy information from the previous record to reduce data entry. To do so, simply press [Ctrl]['] and Access will duplicate the data from the appropriate field in the prior record.
And here's a bunch of other shortcuts, too:
CTRL+SEMICOLON (;) To insert the current date
CTRL+SHIFT+COLON (:) To insert the current time
CTRL+ALT+SPACEBAR To insert the default value for a field
CTRL+APOSTROPHE (') To insert the value from the same field in the previous record
CTRL+PLUS SIGN (+) To add a new record
CTRL+MINUS SIGN (-) To delete the current record
SHIFT+ENTER To save changes to the current record
SPACEBAR To switch between the values in a check box or option button
CTRL+ENTER To insert a new line
Easily select Access form and report controls
When you're working in Design view, selecting the right control on a form or report can be tricky, especially if many controls are contained in a small area. Fortunately, there's an easy way to ensure that you select the right object. Simply choose the name of the control you need to work with from the Object (Select Object in Access 95) dropdown list, which is located on the left side of the Formatting (Form/Report) toolbar.
Drag and drop columns in Access' query design grid
If you've ever needed to reorder columns in the query design grid, you may have been frustrated to find Access simply selects adjacent columns when you intend on moving them. Actually, the design grid supports drag-and-drop; you just need to wait a moment after selecting the fields before you attempt to move them. After the brief pause, click and drag the top of the selected columns to move the fields to their new locations.
Simplify creating ADO connections with Access
If you have a hard time manually putting together connection strings for ADO, you can take advantage of this shortcut. First, right-click on your Windows desktop and select New from the shortcut menu. If you see a choice for Microsoft Data Link on the submenu, select it. Otherwise, select Text Document and rename the new text file so that it has a UDL extension. Next, open the file to display a tabbed Data Link Properties dialog box that lets you easily set up your connection specifications. When you've finished, click OK and then rename the file so that it has a TXT extension. Finally, open the file with WordPad (don't use NotePad) to reveal an OLE DB connection string that you can copy and paste into your code.
Controlling Access's personalized menus
It seems like each edition of Office introduces a seemingly innocent feature that winds up polarizing the user community into "Love It" or "Hate It" categories. In Office 97, it was the introduction of Clippit and his posse of Office Assistants. With Office 2000, users appear divided on the usefulness of the personalized menu system. If you dislike the new menus, which initially show only the most recently used menu items, you can revert to the standard menu behavior you're used to. To do so, right-click on the menu bar and choose Customize from the shortcut menu. Then, click on the Options tab of the Customize dialog box, clear the Menus Show Recently Used Commands First check box, and click Close. If you like the personalized menus, but find that too many infrequently accessed menu commands are being displayed, you can restore the menus to the state they were in when Access was originally installed. To do so, display the Customize dialog box and click the Reset My Usage Data button on the Options sheet. Finally, confirm that you want to reset the data and click Close.
Quickly jump to a referenced code in Access
When you're editing code in Access, chances are that it calls other procedures or functions. If you want to review the code in a referenced function or procedure, just place your insertion point on the reference to the code and press [Shift][F2]. Access then displays the code, regardless of whether or not it's stored in the same module.
Convert Access macros to VBA
If you have legacy macros in a database that you'd like to convert to code, doing so is easy.
In Access 97: Right-click on the macro in the Database window and then choose Save As/Export from the shortcut menu. Then, select the Save As Visual Basic Module option button and click OK. You're then given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.
In Access 2000/XP: Right-click on the macro in the Database window and then choose Save As from the shortcut menu. Enter the name of the module you want to create in the text box and choose Module from the As dropdown list. Next, click OK. You'll be given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.
Add descriptions to Access database objects
Regardless of how detailed the names you assign to database objects are, chances are you find yourself repeatedly opening and closing objects just to find out more information about a form or table that you haven't worked with for some time. You can save yourself some work if you invest a little time after creating a database object to fill in its Description property. Once it's set, you can view the description details within the Database window, next to the object's name. To set the property, select the object in the Database window. Then, choose View | Properties from the menu bar or right-click on the object and choose Properties. Type the description and click OK. To view the text in the Database window, choose View | Details from the menu bar.
Simplify text entry with the Zoom dialog box in Access
Entering text can be difficult when the size of the text box you're working with is smaller than the text you need to enter. Fortunately, you can zoom in on form controls, table fields, cells in the QBE grid, and other text entry cells. To do so, press [Shift][F2] to display the Zoom dialog box. This feature is available throughout Access, and can also be used with property sheets and table and macro design grid cells.
[Note: one of the other forum participants added "Or if you prefer mouse clicks, you can right click and choose zoom from the pop-up menu"]
Update Access recordsets based on multiple queries
Warning! "professional driver on a closed course - do not try this at home" - well, be careful using this tip - perhaps you'll want to experiment first with a copy of your database to make sure you know how and when to use it. As one of the other forum participants noted, "For Access beginners with form design, I always warn them that if their form can't be edited, 99% of the time it's because they messed up when they built their query - and they need to look closely at those key fields."
When you join multiple queries together to create a recordset, the result is a read-only recordset by default. However, by changing the form's RecordsetType property, you can edit the data. To change the RecordsetType property, open the query in Design view and, if necessary, display the Properties dialog box. Then, choose Dynaset (Inconsistent Updates) from the Recordset Type dropdown list, save your query, and view it. You'll find that you can edit it as easily as a traditional datasheet.
Place Access controls exactly where you want
The Snap To Grid feature is an invaluable tool for aligning controls when you're designing forms and reports. However, when you fine-tune the placement of some controls, you'll probably want to move some of them to positions that aren't exactly aligned with the design grid. You can temporarily disable the Snap To Grid feature by holding down the [Ctrl] key. Then, you can use your mouse or the cursor arrows to place the controls exactly where you want them.
[Extra note from Karla: holding down the [Shift] key with the arrow keys allows for more precise resizing, too :-) ]
Quickly add Access objects to the query grid
If you need to add a table or query to a query you're building in Design view, you most likely click the Show Table toolbar button, drag the appropriate objects from the resulting dialog box, and then close the dialog box. However, there's a much easier way to do this. Simply drag the table or query object's icon from the Database window directly to the gray background of the query design grid. This same technique also works with Access's Relationships window.
Quickly change an Access control's type
You'll likely come across situations where you want to change a form or report control's control type. For instance a field that's set up as a combo box lookup at the table design level will appear as a combo box when it's added to a form. This is often convenient, but if you're displaying the data in the field as read-only, it makes more sense to display the information in a regular text box than a dropdown control. Fortunately, it's easy to change the control types for many form and report controls. Just right-click on the control you want to change, select Change To from the shortcut menu and then choose the new control type you want to use. The new control type has to be functionally similar to the original control type, so only the valid control type selections will be enabled in the shortcut menu.
Take the guesswork out of sizing controls
You'll find this tip helpful if you've ever had to repeatedly open a form or report in Design view to tweak the size of a control because it wasn't big enough to display all of your data. When you click on a control's sizing handles in Design view, Access displays the number of characters that the control will display in the program window's status bar. Access adjusts this number based on the control's font attributes, so it's easy to tell what impact such property changes will have on the amount of data that can be shown in the control.
[Note: one of the other forum participants added that double-clicking on the resizing handles in design view automatically adjusts the field size for the text entered.]
Load a specific form when an Access database opens
When you finish creating an application, chances are that a particular form will receive the majority of the attention from users. If so, you can set up Access to automatically launch the form when the database is opened. To do so, choose Tools | Startup from the menu bar while the database is open. Then, choose the form from the Display Form/Page dropdown list (just Display Form in Access 97). Finally, click OK. This technique can be used to launch a form that acts as a splash screen or to open a switchboard form that acts as a user interface to your database objects.
Highlight the current Access control
Many users have trouble knowing which text box on a form they're currently working with. One way to make it clear for users is to highlight the current one, for example, with a yellow background. Access 2000/XP allows you to do this with conditional formatting, but you can also get a similar result using code. To do so, create a new module and add the following code:
Function Highlight(Stat As String) As Integer
Dim ctrl As Control
On Error Resume Next
Set ctrl = Screen.ActiveControl
If Stat = "GotFocus" Then
ctrl.BackColor = 65535
ElseIf Stat = "LostFocus" Then
ctrl.BackColor = 16777215
End If
End Function
Save and close the module, then open the form you want to apply the highlighting to in Design view. Click the Code button and insert
Highlight("GotFocus")
in each textbox control's GotFocus event procedure. Likewise, add
Highlight("LostFocus")
to each textbox's LostFocus event procedure. When you've finished, save the changes, close the VBE, and switch to Form view. When you tab to a field, it's shaded yellow. When you tab away from the field, its background is restored to white.
Use the Tag property to flag Access controls
One of the properties we find most useful is the Tag property. This property allows you to associate up to 2,084 characters of text with any form, report, section, or control. This is especially helpful when you want to single out a specific subset of controls. For instance, say that you want to hide certain controls on a form when a user clicks a button. You can flag which controls will be hidden by entering the word "Hide" (or any other consistent word) in each control's Tag property. Then, attach the following code to the command button's Click event procedure:
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Hide" Then
ctl.Visible = False
End If
Next
[Note: one of the other forum participants pointed out that it is customary
to put the counter variable, ctl, after the Next.
While that is optional, it's a good idea :-)]
This space reserved for the answer to the mystery question! "Which line of code is missing?"
Organize Access database objects into custom groups
As a database grows, it's easy to get overwhelmed by the number of objects in it. If you repeatedly work with the same objects, trying to pick them out from the multiple sheets of a crowded Database window can be a pain. Fortunately, Access 2000 and above let you group shortcuts to database objects into custom categories that are listed on the Database window's Groups bar. Best of all, you can group related objects of different types, such as all the forms, queries and reports that deal with employee data.
By default, all databases have a Favorites group you can add objects to. To create and name your own, first click the Groups bar heading to display the current groups if none are visible. Then, right-click on the Groups bar and choose New Group from the shortcut menu. Type an appropriate name for the group and click OK. Finally, simply drag the objects you want to create shortcuts to from the Database window to the Groups folder.
Automatically set up links to data outside of Access
If you're creating a new database that will link to data that isn't in an Access format, you may be able to speed up the setup process. Rather than creating a new database and then using the File | Get External Data | Link Tables method, simply choose File | Open from the menu bar. Then, select the appropriate data format from the Files Of Type dropdown list and open the file as you would any Access database. Access will automatically create an MDB file with the same name as the data source you selected and will set up links to the data.
Simplify grouping on dates in Access reports
When you sort and group report data based on a Date/Time field you may find that the report is more meaningful if data is summarized in terms other than individual dates. For instance, you may want to group data by month or quarter. Fortunately, you don't need to use expressions to do so. While in Design view, select View | Sorting And Grouping from the menu bar. Then, select the appropriate field in the Field/Expression column of the Sorting And Grouping dialog box. Next, click in the Group On text box and choose one of the following from the associated dropdown list:
Year
Qtr
Month
Week
Day
Hour
Minute
Finally, close the Sorting And Grouping dialog box.
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.