Database/Access Tips
From the Database Community Forum
April 2004
Quickly create Access subforms and subreports
When you need to create a subform or subreport, you probably use the Subform/Subreport tool from the Toolbox to draw where you want to add the control. You can also create subform and subreports using drag and drop. Simply open the main form or report in Design view, then drag the appropriate form or report from the Database window to where you want the control created. Note that you'll still need to set Link Child Fields and Link Master Fields properties on the new control.
Easily create controls formatted to your specs
Chances are that you spend a lot of time tweaking form and report controls to create a uniform look or to conform to your particular taste. For example, you may prefer that associated labels always appear above controls, instead of to the left of them. Or, you may need to use specific font attributes with certain control types. Fortunately, it's easy to make new controls conform to a standard set by an existing control. To set the default formatting to use for new controls, format a particular control to your liking. Then, while the control is selected, choose Format | Set Control Defaults from the menu bar.
Customize Access shortcut menus
The shortcut menu that's displayed when you right-click your mouse provides quick access to actions that are relevant to the object you click on. But, it's often likely that the shortcut menu is lacking a selection that you feel would make working in Access easier. Fortunately, Access makes it easy to customize shortcut menus to your liking. To do so, right-click on any visible toolbar and choose Customize. Then, click on the Toolbars tab and select the Shortcut Menus check box. Access then displays a toolbar that contains menus for major work environments within Access, such as Database, Query and Module.
Now, click on the Commands tab and scroll through the Categories and Commands list boxes to locate the command you want to add. Then, click and drag the command up to the appropriate menu and submenu on the Shortcut Menus toolbar.
For example, let's say that you want to customize the shortcut menu that appears when you right-click on a report control in Design view to include a choice for duplicating the control. To do so, select Form/Report design in the Categories list box. Then, drag Duplicate from the Commands list box to the Report menu on the Shortcut Toolbar, without releasing the mouse button. When your mouse pointer is over the menu it will automatically expand to reveal its submenus. Drag the mouse pointer over Design View Control and you'll see the other choices that are displayed when you right-click on a control. Simply drag the Duplicate command to the position on the menu that you want and release the mouse button. When you've finished customizing the shortcut menu, click Close to dismiss the Customize dialog box.
Easily change blocks of code to comments in Access modules
When you're testing procedures, you'll often want to temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Doing so manually by inserting an apostrophe before each line of code can be a real chore. Fortunately, Access 2000 simplifies this task by letting you convert a block of code to comments with a click of a button. To see how this works, open any module in the Visual Basic Editor (VBE), and then choose View | Toolbars | Edit from the menu bar to display the Edit toolbar. At this point, select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the right end of the toolbar). Each line of the selected code is now preceded with an apostrophe. To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
Splitting existing Access databases
When creating multi-user applications that will run across a network, you can drastically increase performance by storing data in a separate MDB file from the other objects, like forms and reports. In this scenario, known as a split database, the data is stored in a shared folder on a file server while each user has a copy of the file containing the other database objects on their local hard drives. If you're converting an existing database to a split database you can use the Database Splitter to simplify the task of separating database objects and linking tables.
To split a database, choose Tools | Database Utilities | Database Splitter from the menu bar (in Access 97, choose Tools | Add-ins | Database Splitter). Then, click the Split Database button. You can then specify the name of the back-end database, the file that will contain your data. By default, Access appends "_be" to the end of the existing database name. To avoid relinking tables later, you may also want to select the final destination folder that the back-end database will reside in. Once you've assigned a name, click Split. It may take some time, but Access will notify you when the job is complete.
Protect VBA modules from prying eyes
If you use user-level security with your Access applications, you need to be aware of an important difference between Access 2000 and prior versions. Securing your database no longer protects your VBA modules, so you'll need to manually password-protect any code you want to prevent others from seeing. Even if you don't use user- level security, you may want to protect modules to prevent others from borrowing or accidentally changing your code. To do so, press [Alt][F11] to open the VBE. Then, choose Tools | <databasename> Properties from the menu bar and click on the Protection tab. Next, select the Lock Project For Viewing check box. In the appropriate textboxes, enter the password you want to use, then click OK. When you later try to access the VBA code, Access will prompt you for the password.
Removing and adding subdatasheets to a view
In Access 2000/XP, when you view the master table of a relationship in Datasheet view, you'll typically be able to drill down to the related data through a subdatasheet. When a subdatasheet is in place, you can expand and collapse the view of any related data by clicking the appropriate button in the leftmost column of each row. You can even work with nested subdatasheets, such as when you view the Northwind sample database's Employees table, which lets you drill down to the related Orders and Order Details records. Although this feature is convenient, it can also cause confusion, especially for end users. If you'd rather not display a subdatasheet, you can easily remove it by selecting Format | Subdatasheet | Remove. Note that this doesn't affect the established relationship between the tables. To display related data with a subdatasheet, select Insert | Subdatasheet while the parent table is in Datasheet view. Then, choose the appropriate table or query you want to display, select the appropriate linking fields and click OK. If a relationship doesn't already exist you'll be prompted to create one; however, you don't have to create a relationship to be able to use a subdatasheet.
Use assertions to track down errors
If you've been coding in VBA for some time, you no doubt incorporate error handling into your procedures. A typical approach might resemble the following sample code:
ErrorHandler:
Select Case Err
Case 13
Resume Next
Case Else
MsgBox Err & ": " & Err.Description
End SelectThis simple example traps for a data type mismatch and displays a message box with the details of any other errors that may occur.
Although this basic error handling may be sufficient for dealing with errors related to your code, it can make your debugging efforts more cumbersome than it needs to be. For example, once the message box is dismissed, the procedure will end and you'll lose the ability to inspect the Locals Window or Call Stack. Likewise, you can run into situations where your code is written correctly but a procedure still doesn't work the way you intend due to inappropriate data values.
You can address these situations by using the Debug object's Assert method. The method is used in the format:
Debug.Assert (Boolean expression)When the Boolean expression evaluates to False, the procedure stops executing and the line containing the Assert method is highlighted in the VBE. For example, you could place the statement:
Debug.Assert Falseafter our previous MsgBox statement to automatically switch to the VBE after the message box is closed. Or, if your procedure depends on data being of a specific type, you could use a statement like:
Debug.Assert Debug.Assert IsDate(varInput)Speed up table creation with default field settings
When you add fields to a table, Access assumes you want to use a 50-character Text field by default. However, you may typically use a smaller field size or you may personally use Number fields more often than Text ones. You can avoid having to change the size and data types for new fields by setting defaults that are appropriate to your own design habits. To do so, choose Tools | Options from the menu bar and switch to the Tables/Queries sheet. Then, select the data type you use most from the Default Field Type dropdown list. Finally, set the Text and Number sizes you usually want to use in the Default Field Sizes panel and click OK.
Customize toolbars to suit your needs
If there are buttons you'd like to see on a particular toolbar -- or ones you never use -- you can customize the toolbar to be more relevant to your needs. While the relevant toolbar is displayed, choose Tools | Customize from the menu bar (View | Toolbars | Customize in Access 97). Then, click on the Commands tab of the Customize dialog box. The available commands are grouped into logical categories.
To add a button, select the appropriate item in the Categories list box to reveal the associated buttons in the Commands list box. Then, drag the button you want to add from the list box to a location on the toolbar.
To remove an item from a toolbar, simply drag the button away from the toolbar. When an X appears next to your mouse pointer, release the mouse button.
When you've finished making changes, click the Close button on the Customize dialog box.
Note that you can always restore a toolbar to its default configuration. To do so, redisplay the Customize dialog box. Then, click on the Toolbars tab. Select the toolbar you want to restore from the Toolbars list box and click Reset, followed by OK.
Add temporary comments without changing a report's design
There are plenty of times when you'll want to annotate a report with text that shouldn't become a part of the report's permanent design. For instance, say that you print a report to get a rough idea of a project's status even though data entry hasn't been completed. Doing so may quell an immediate business crisis, but it could just as easily stir up another one three weeks down the road when someone happens upon the report and demands to know why there are huge gaps in the data. However, if you clearly indicate the circumstances behind why a report was printed or who requested the report, you can eliminate confusion, and stress down the road.
Of course you can just hand-write an explanation on a report, but if you frequently annotate a particular report you can easily incorporate temporary text without continually changing the report in Design view. There are many ways to approach the task, but the easiest is to use a textbox control that prompts a user to enter any comments that should be included when the report is opened, in much the same way that a parameter query prompts a user for criteria.
To set up such a control, open the report in Design view and add a textbox control where you want the user-defined text to appear. Size the control so that it's big enough to describe a likely comment. Then, set the control's Control Source property to:
=[Enter any comments you want to make about this report:]
Save the report and close it. From now on, you're prompted to enter a comment whenever the report is previewed or printed. Enter the appropriate text and click OK. Your entry appears on the final report.
Note that you must click OK when you receive the report's prompt -- clicking Cancel actually aborts the entire print process. This doesn't mean you're forced to enter a comment. Just leave the prompt text box blank and click OK to continue printing without including a comment.
Alphabetize a recordset by either of two fields
There may be times when you'd like to alphabetize data based on an alternate field if the one you primarily sort by is blank. For example, you might want to sort a query by either company name or a contact's last name. Unlike a traditional sort, you don't want to just group all of the records that lack company names at the beginning of the datasheet. Instead, if the CompanyName field is blank, you want Access to use the contact's last name in its place. In other words, you want to treat a person's last name as if it were a company name when the company is missing, to create a datasheet that looks like:
CompanyName FirstName LastName --------------------- --------- -------- All-Weather Wear Inc. Claire Zemeckis Larry Anderson Beckett Motor Company Brian Brault
To accomplish such a sort, create an extra query field specifically to provide the custom sort, then use the NZ() function to replace the contents of one field for Null values in another. For instance, let's say you're working with a query using the previously described fields. Open the query in Design view and remove any previously existing sort specifications. Then, enter the following in a blank Field text box:
SortValue: NZ([CompanyName],[LastName])
Finally, select Ascending from the new column's Sort dropdown list. When you run the query, the NZ() function returns the contents in LastName instead if CompanyName is Null.
Alternate page number placement on reports
Although you may typically print out single-sided reports, situations may call for a report printed on both sides of the page. When your reports must support duplex printing, you may want to alternate the position of the page number indicators to create a more professional, book-like appearance. For instance, you may want the page number to appear on the left side of the footer on an odd-numbered page, but have it appear on the right side on the page when the page number is even. Fortunately, you don't have to go to a lot of work to set up such a page numbering scheme.
With your report open in Design view, choose Insert | Page Numbers from the menu bar. Then, choose whether the number should appear in the header or footer using the appropriate Position option. Finally, select either Inside or Outside from the Alignment dropdown list and click OK. The Inside setting prints odd page numbers on the left side of the page and even numbers on the right. Selecting the Outside setting produces the opposite result, with odd page numbers appearing on the right and even numbers on the left.
Clarify list items with column headings
Although listbox and combobox controls have an associated label by default, sometimes the data in the list would benefit from an additional descriptive heading. This is particularly true when the list displays multiple columns. Fortunately, it's easy to add column headings based on the control's row source data. Just set the control's Column Heads property to Yes. If the row source is based on a table or query, the displayed fields' Caption properties appear as column headings. If the row source is based on a value list, the initial items in the list are used as column headings; using as many items as are needed for the number of specified columns. Although you can set the property when the Row Source Type is set to Field List, the heading isn't really effective as the first field names are used as column headings, producing inappropriate results. Note that you can't change the way column headings appear -- if you want formatted headings you'll need to take a different approach, such as adding label controls above a listbox control.
Draw attention to data with conditional formatting
Sometimes you deal with so much data, it's easy for important information to get buried among everything in a form or report. You can highlight data that users should pay attention to using conditional formatting. For example, if you want to draw attention to inventory records containing a quantity greater than 1000 units, you can shade the control bound to the quantity field with a bright yellow background. In older versions of Access, you were forced to create custom solutions to apply such formatting, but it's easy to do in Access 2002. In fact, if you've ever used the Excel's comparable feature, you'll find the process is virtually the same.
To apply conditional formatting, open your report or form in Design view. Then, select the appropriate control and choose Format | Conditional Formatting from the menu bar. You're prompted to define the first condition, which can be based upon the field value or a custom expression. In the Condition1 panel, use the formatting controls to define the format that should be applied when the criteria expression evaluates to a True result. When the expression evaluates to a False result, the formatting shown in the Default Formatting panel is applied.
For example, to apply special formatting when the field value is greater than 1000, select Field Value Is from the first dropdown list, Greater Than from the second dropdown list, and enter 1000 in the associated text box. Then, select a shade of yellow from the Fill/Back Color palette. Finally, click OK. When you view the form or report, the control displaying the relevant data appears yellow when the displayed value is greater than 1000.
Note that you can require that up to three conditions be met before the conditional formatting is applied. Just click the Add button in the Conditional Formatting dialog box to set another condition. Keep in mind that when you specify multiple conditions, all of them must evaluate to True for Access to apply the desired formatting.
System Objects reveal more than just Access tables
Access maintains several hidden tables in each database that it uses to manage the file and its objects. To manually open these tables, you can make them visible by choosing Tools | Options from the menu bar, selecting the System Objects check box on the View sheet, and clicking OK. However, changing this setting also reveals previously hidden fields that will exist in replicable tables if your database is part of a replica set. Although you can't edit the data in these fields, they can have an unexpected impact on your database. For instance, if you have a form with a combobox that displays choices based on a table field, you may find that the control suddenly displays data from the wrong field because the number of columns in the table is different.
Identify a linked Access table's source
When a database contains linked tables, you may find that you need to verify the name of the source table or locate the source file. There are a number of ways to get this information, but the quickest approach isn't obvious. To quickly find a linked table's source information, open the table in Design view, clicking Yes when Access informs you that some properties will be unmodifiable. Then, right-click on the window's title bar and select Properties from the shortcut menu. You'll find the information you seek in the Description text box in the Table Properties dialog box.
Return all records with an Access parameter query
Parameter queries let you return records based on user input. However, you may want to provide an easy way for users to return all records from the query's underlying recordset. To do so, use the Like operator and append a wildcard to your criteria. For instance, if a parameter query prompts for a LastName value, change LastName's criteria to
Like [Enter Name] & "*"If a user responds to the Enter Name prompt without entering a value, all of the recordset's records are displayed.
Quickly resize a form to show the current record
One aspect of Access that we find annoying, is the way form sizes are maintained when you switch between Design and Form views. The size of the form is dictated by the size of the Design view window, not the size of the form sections. You often need to expand the window to be able to see the rulers and scroll bars in addition to all of the sections. This means you're left with wasted space when viewing the form in Form view, assuming that you forget to shrink the window back down.
A solution to this annoyance is to use the Size To Fit Form feature. Simply view the form in Form view and choose Window | Size To Fit Form from the menu bar. If your view of the form is maximized, the menu option will be unavailable and you'll need to click the Restore Window button on the form window to enable the choice. Once Access has resized the form, you can save its current dimensions by clicking the Save button on the Toolbar.
For Single forms, using this feature shrinks or expands the window accordingly, to display the entire record. For Continuous forms, if only part of the bottom record is showing, Access shrinks the form to encompass the previous record. If only part of one record is displayed, then the form lengthens to show as much of that record as possible. In all cases, the form widens or narrows to accommodate the record's width.
Programmatically hiding tables safely
Although you shouldn't use DAO to programmatically hide tables, there is a safe way to programmatically hide them from view in the Database window--if you have Access 2000 or above. To do so, use the SetHiddenAttribute method. This method applies to the Application object and uses the syntax:
SetHiddenAttribute ObjectType, ObjectName, fHiddenEssentially, is the programmatic equivalent to the Hidden check box available through the user interface. The ObjectType argument accepts one of the standard AcObjectType constants (like acForm or acTable) and is used to indicate the type of object with which you're working. The ObjectName argument is simply the name of the object and fHidden accepts True or False to determine whether the object is hidden.
To illustrate, let's say that you have a database containing a table named tblOrders. You can use the following two procedures to programmatically change whether the table is visible in the Database window.
Sub HideTable()
Application.SetHiddenAttribute acTable, "tblOrders", True
End Sub
Sub ShowTable()
Application.SetHiddenAttribute acTable, "tblOrders", False
End SubIf you want to check whether a database object is hidden, you can do so with the GetHiddenAttribute method. For example, use the following procedure to display the tblOrders table's visible status in the Immediate window:
Sub TableHiddenStatus()
Debug.Print Application.GetHiddenAttribute(acTable, "tblOrders")
End SubQuickly move to an Access datasheet column
When you're working with a table in Datasheet view that has many fields, navigating between columns can be a pain. Fortunately, there's an easy way to move to a particular column. Simply choose the name of the field you want to move to from the Go To Field dropdown list on the Formatting (Datasheet) toolbar. The focus will move to the appropriate field within the current record.
Start building Access projects using MSDE
Chances are you want to try your hand at using Access projects (ADP files) to work with SQL Server databases. However, if you don't happen to have a copy of SQL Server or an NT box handy, you may have thought that Access projects were beyond your means. Actually, Access 2000 comes with a desktop version of SQL server that can run in 9x and NT environments, but it isn't installed during the normal Office setup so you may not know it's available. Here are some basic instructions to get you up and running--we'll assume you don't already have access to a SQL Server database that you can connect to.
To install the Microsoft Data Engine (MSDE), run the SetupSQL.exe file in your Office CD's \SQL\X86\Setup folder. After installing, reboot your PC and you'll find an icon for the SQL Server Service Manager in your system tray. Right-click on the icon and open the service manager. Then, choose SQL Server from the Services dropdown list and click the Start/Continue button. You can now begin creating Access projects using the MSDE. Note that when you create a new SQL Server database you'll be prompted to enter your Login ID--you can use the username "sa" (for "system administrator").
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.