Access2000
Home Up

 

HIRING A NEW ASSISTANT

Office 2000 installs only one assistant by default, and that's Clippit. If you want to fire Clippit and hire a new assistant, you may need to install it first. First, try changing the assistant by right-clicking Clippit, selecting Choose Assistant, and then clicking the Gallery tab (if necessary). Click the Next and Back buttons to find a new assistant, and then click OK. You may want to have your Office 2000 CD ready, just in case.

If you didn't install the other assistants, you'll have to do so before you can dump Clippit and hire a new one

 

INSTALLING ASSISTANTS

In our previous tip, we mentioned that you may need to install a new assistant before you can get rid of Clippit and switch to another assistant. That's because Office 2000 installs only Clippit. To install another, or all the available assistants, select Start, Settings, Control Panel. Next, double-click the Add/Remove Programs option, select Microsoft Office 2000, and then click the Add/Remove button. Next, click the Add Or Remove Features button in the Office setup window. You'll see a series of options, with plus and minus signs to the left of each. Click the plus sign to the left of the Office Tools option. Then, click the plus sign next to the Office Assistant option, which will open all the available assistants. Select the assistant(s) you want to install, and then choose the installation process you want. Finally, click Update Now and click OK. If you chose the Installed On First Use option, be sure to have your Office 2000 CD handy when you change the assistant.

 

SHRINKING YOUR ASSISTANT

Let's suppose you like the Office Assistant and you want to keep it. However, it's a little larger than you'd like it. If this is the case, you can reduce the size of your assistant. Display the assistant and then position the mouse pointer over any of the assistant's borders and click. Doing so will reduce the assistant. To enlarge it again, repeat the process.

 

SMART BACKUPS

Using the default My Documents file folder is a great idea if you have to back up your work on a regular basis. With all your files automatically filed in one folder, you need only back up that one folder. To do so, you'll simply pull that folder to your floppy drive or zip drive (or whatever medium you're using to store backup files).

 

PUT A BORDER AROUND IT

A border adds a nice professional touch to almost any report. However, no built-in feature prints a border around your report, so you'll need VBA. Fortunately, the code is short and easy to use. Simply attach the following code to the report's Page event:

Private Sub Report_Page()

Me.Line (0, 0) - (Me.ScaleWidth, Me.ScaleHeight), , B

End Sub

 

BOUND BEHAVIOR

When you update a bound control, Access opens the data source internally. You won't see it happen, of course, but the table opens just the same. If you don't close that table, you could create an error. You see, the next time you try to modify the table, Access will return an error because the table is already opened and locked.

So, how could you create this problem in the first place? You could force Access to update a field. For instance, you might run a quick VBA statement similar to

Me!fieldname = "newentry"

to change a particular field. Unfortunately, that statement alone would open and lock your data source.

If you must update a field in this way, remember to unlock the table afterward. The easiest way to do so is simply to move to the next record using a statement in the form

DoCmd.GoToRecord acDataForm, Me.fieldname, acNext

Thanks!

 

BREAKING UP IS EASY TO DO

When you need to display a message, you probably use the MsgBox() function. Unfortunately, you can't control how Access displays it. For instance, if your message is long, Access, not you, determines where to wrap it. However, you can take back control using the Chr() function.

To experiment, simply open the Debug window (the Immediate window in version 2.0), enter the statement

MsgBox("This message is too long so" & Chr(13) & "we added a Chr(13) function to wrap the text.")

and then press Enter. Access returns a message box displaying two lines of text. Furthermore, the Chr(13) function inserts a line break between the words "so" and "we." Without this function, Access, not you, decides whether and where to wrap the text.

 

ACCESS 2000 BUG

Microsoft has identified a problem you may experience with Access 2000. The problem occurs when Access 2000 improperly evaluates an If Then Else expression comparing a variable that is null. Microsoft's solution is to use IsNull() check on variables in the If Then Else expression. Rewrite the expression to include IsNull(), as in the following example from Microsoft.

The expression below will not evaluate properly if strName or txtName is Null.

If strName = txtName then

strName = '1'

Else

strName = '2'

End if

Change the expression to:

If strName = txtName and Not IsNull(strName) and Not IsNull(txtName) then

strName = '1'

Else

strName = '2'

End if

 

MICROSOFT ACCESS 2000 BUG

Microsoft has identified a bug in Access 2000 that results in the error message "Error Message 2046 Calling OpenForm or OpenReport with Access Automation." When you automate Microsoft Access and call either the OpenForm or OpenReport method, you receive one of the following errors: "Run-time error '2046': The command or action 'OpenForm' isn't available now" or "Run-time error '2046': The command or action 'OpenReport' isn't available now."

This problem occurs when the Database window is hidden at the time you call the OpenForm or OpenReport method from automation.

You can work around this problem with one of the following techniques:

  • Make sure the Database window is shown before calling the OpenForm or OpenReport method.

  • Create a module in the Access database that contains the code to open the form or report, then call that procedure from automation using the Run method.

 

DISABLING THE CLOSE BUTTON

Access forms include the Windows Close button (X) in the top-right corner. Clicking this button will close your form. You can force your users to close the form a certain way by disabling this button and including some other method for closing the form, such as a command button.

You can use a Windows API to disable this button, but Access provides a much simpler solution. Set the form's Close Button property to No. (The default setting is Yes, which displays the Close button.) Your form will still display the button, but it will be disabled (grayed out). As a result, your users will need an alternate way to close the form, so be sure to supply one.

 

CHANGE YOUR DEFAULT BUTTON

Most dialog boxes and forms use command buttons to close them. Sometimes you'll see a simple set of buttons with just OK and Cancel buttons. A set of command buttons usually has a default button. For instance, the Save dialog box displays Yes, No, and Cancel buttons--Yes is the default button. This means that once the Yes button has the focus, you can press Enter instead of clicking the button to execute the button's click event. You can create the same effect with your own command buttons by setting the appropriate command button's default value to Yes.

All command buttons have a Default property--the settings are Yes and No. However, only one button in each set can have a Yes setting. If you try to specify a Yes setting for a second button, Access will accept the Yes setting for the second button but reset the first button's Default property to No.

 

DISPLAYING LARGE BUTTONS

Would you like your toolbar buttons to be a bit larger? You can enlarge buttons by choosing Tools, Customize and clicking the Options tab. Then, select the Large Icons option in the Other section. If you change your mind, simply deselect this option, and Access will return to the default buttons. This option won't affect the size of your screen tips.

 

JUGGLING CODE THE EASY WAY

Many controls act as a toggle switch--clicking the control simply reverses its current state. For instance, if the control is off (equals False), a click reverses the control's state to on (True). Likewise, if the control is on, a click turns it off. To use a toggle control, you simply add code that responds to the control's current state.

You might be surprised to learn that most of the time you won't need a lot of code for your toggle controls--that is, if you can reduce your control's purpose to a simple Boolean value (True or False). When this is the case, use the Not operator to switch between the two values.

For instance, let's suppose that you want to toggle a control's Enabled property between True and False (on and off). To do so, use the Not operator in the form

control.Enabled = Not control.Enabled

where control represents the name or reference of your toggle control. If the control's current state is true and the control's Enabled property is on (True), then clicking the control reverses that situation by turning the Enabled property off (False). You're not limited to using this technique with properties, but the control's state must reduce to a Boolean value.

 

ADDING LINE NUMBERS TO CODE

You probably know you can comment your code using the apostrophe character or the REM statement. Did you know that you could number your lines of code? You can, and it's easy. Just add the number to the beginning of each line of code. For instance, the procedure

Private Function GetCount()

Dim db As Database, strSQL As String, rst As Recordset

Set db = CurrentDb

strSQL = "SELECT Count(*) FROM table3"

Set rst = db.OpenRecordset(strSQL)

Debug.Print rst(0)

End Function

becomes

Private Function GetCount()

10 Dim db As Database, strSQL As String, rst As Recordset

20 Set db = CurrentDb

30 strSQL = "SELECT Count(*) FROM table3"

40 Set rst = db.OpenRecordset(strSQL)

50 Debug.Print rst(0)

End Function

There are only a few rules to remember when numbering your code:

  • Numbers must appear at the very beginning of your line of code.

  • Each number must be unique within the module (which is a nuisance).

  • Don't number the beginning or ending statement.

 

HIDE YOUR COLUMNS

Don't use the Column Count property to hide a column in a combo or list box. Instead, set that column's width property to 0". For instance, if you want to hide the first and fourth column in a four-column box, you'd use the Column Width property 0";1";1";0". This property hides the first and last column and displays the second and third column in one-inch columns.

 

PICK FROM THE COMBO LIST

You use a combo box to display a list of items from which a user can select one or more. A combo box looks just like a text box, except that it has a down arrow at its right border. To see the combo list, click that arrow to open the control's drop-down list. When you select an item, Access displays that item in the control's text box component.

However, you don't have to choose an item from the list--you can type it yourself in the text box component. To force users to choose from the list instead of entering data themselves, set the combo box's Limit To List property to Yes. The default is No--this setting accepts an entry that's not on the list.

If you choose the No setting, the control accepts entries that aren't in the list; a setting of Yes limits users to the items in the control's list. If you attempt to enter an item that isn't in the list when you've set the property to Yes, Access displays an error message warning that you can't enter a non-list item. When you receive this message, click OK to clear the message, and then press Esc to delete the offending entry.

The error message can be a nuisance, and, fortunately, you can suppress it. To do so, attach the following code to the control's NotInList property:

Private Sub comboboxname_NotInList(NewData As String, Response As Integer)

Response = acDataErrContinue

End Sub

The next time you enter an item that's not on the list, Access still won't accept the value, but it won't display the error message, either. That's because the acDataErrContinue (intrinsic) constant tells Access to ignore the error and continue.

 

COMPILING DURING DEVELOPMENT

While you're still in the development stage of an application, don't use the Compile All Modules command. The first time you make changes to any of your code, you will undo the compile, so a Compile All Modules command at this stage of the game is a waste of time. When you need to compile code during the development stage, use the Compile Loaded Modules option. This command compiles only the modules that are called by the open module.

 

CAN'T USE CONSTANTS IN ACCESS OBJECTS

By design, you can't use intrinsic constants in queries, forms, and reports. You can use only VBA constants with VBA code. However, there's a simple workaround--use the constant's numeric value instead. Most of the time, this quick alternative will work just as well. The downside: The numeric values aren't as readable and they may not properly convert to a new version.

If you don't know the numeric value of an intrinsic constant, you can search Help. Alternatively, you can open the Debug window (press Ctrl-G) and run a statement in the form

?intrinsicconstant

(where intrinsicconstant represents the constant) to return its numeric value.

 

UNFORGETTABLE CONTROL

It's easy to forget about the Control menu--the Access key icon at the left end of the application title bar. Clicking this icon opens a menu that provides a list of commands for sizing and positioning the application window. Specifically, you can use these easy-to-access commands to minimize, maximize, restore, and even close the application window quickly.

 

CHANGING A CONTROL'S DEFAULT PROPERTIES

Controls have default properties just like tables, forms, and reports do, and you'll probably find yourself altering those defaults on occasion. If you're changing the same defaults too often, you can temporarily change those defaults. To do so:

  • In Design View, open the Toolbox and click a control button.

  • Before you insert the selected control, click the Properties button to open that control's default property sheet.

  • Modify any of the available properties.

  • Insert your modified control.

Access will retain these new default settings while you're working with the current form--even if you close the form and reopen it later.

 

CHANGING A CONTROL'S NAME

It's a good idea to name a control as soon as you create it, if you plan to change the default name. If you decide to give the control a more descriptive name later, you'll have to update all the code that references that control. Most important, you'll need to update the control's event procedures. VBA won't update these for you, which explains why your control ceases to work after you change its name. To update the control's code, locate the original procedure in the module and change the name. For instance, if you rename a command button named Command0 to cmdOpen, you should find the original procedure name

Command0_Click()

and rename it accordingly

cmdOpen_Click()

 

ADD MULTIPLE CONTROLS THE EASY WAY

Generally, you add controls to a form or report by clicking the appropriate control button on the Toolbox in Design view and then clicking the form or report. If you want to add a second (or several) controls of the same type, you probably return to the Toolbox and click the same button before inserting the additional control. Fortunately, this repetitive task isn't necessary.

If you want to add several controls of the same type to a form or report, simply double-click the control button instead of using a single click. Double-clicking a control button temporarily selects that control, so you can insert as many controls as you like without clicking that button again on the Toolbox. To reset the current tool selection, click another control button or the Selection arrow on the Toolbox.

 

MULTIPLE LINES IN CONTROLS

Do you need to enter more than one line of data in a control? Most of the time, users choose to use memo fields if they have large amounts of text to enter. As you type, the memo field control will wrap your text just like a text editor.

If you need to enter more than one line of text into a text field, a text box won't wrap the text for you. However, you can still enter more than one line of text into the bound text field. Here's how:

Begin by typing the first line of text in the text box control. Press Ctrl-Enter; the text box will position the cursor at the beginning of a new line. Then, type the second line of text.

You can enter as many lines as you like. Simply press Ctrl-Enter when you're ready for a new line. Just remember that you're still limited by the bound field's Field Size property.

 

WHEN YOU CAN'T CONVERT

Although most files easily convert to Access 2000, occasionally you will see the error

Microsoft Access can't open or convert this previous version database.

This means that you don't have the appropriate security permission to open or convert a database created in an earlier version. If this happens to you, try reviewing the Knowledge Base article Q232589 ACC2000: Error Message "The Current user account doesn't have permission to convert or enable this database."

In a nutshell, there are three solutions you can try:

  • Ask the owner of the file to convert it for you.

  • Change the permissions on the system tables to include Administer.

  • Import the objects into a new database.

 

THE FASTEST COUNT

We've shown you how to count records in a recordset by using the RecordCount property. However, to do so, you must populate the entire recordset by executing the MoveLast method. If your recordset is large, this is inefficient and unnecessary because the SQL Count function is faster. For instance, the procedure

Function GetCount()

Dim db As Database, strSQL As String, rst As Recordset

Set db = CurrentDb

strSQL = "SELECT Count(*) FROM table3"

Set rst = db.OpenRecordset(strSQL)

Debug.Print rst(0)

End Function

is faster than

Function GetCount()

Dim db As Database, rst As Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("table", dbOpenDynaset)

rst.MoveLast

Debug.Print rst.RecordCount

End Function

Granted, in a small database, you may not notice the difference. However, if you have thousands of records, you should definitely notice an improvement.

 

THE TWO COUNTS

In our previous tip, we showed you how to use the SQL Count(*) statement to return the number of records in a recordset quickly. If you're tempted to replace the * character with an actual field name because you think referencing a specific field will be faster, don't. You see, the Jet has special optimization rules for the * character, and it actually runs faster than specifying a field name. Only specify a field name in a SQL Count function when you need a count of that particular field.

 

DAO/ADO CONFLICT

If you're running DAO code in Access 2000, you probably know you need to add the DAO library (Microsoft DAO 3.0, 3.51, or 3.6 Object Library) to your references by choosing Tools, References (in the Visual Basic Editor). If your DAO code still returns an error, remove the Microsoft ActiveX Data Objects 2.0 or 2.1 Library from your references, and your DAO code should run just fine (provided there are no programming errors).

 

OPENING FOREIGN DATA

Access 2000 makes working with foreign data easier than ever. If you open a foreign file, Access 2000 will automatically create a new database and link to the data, which can save you a lot of setup time. You can work with dBASE files, spreadsheets, text files, and Paradox tables. Of course, the data will need to be arranged in tabular format with rows and columns before you open that file in Access. In addition, the foreign file should contain the same type of data in each column and the same number of fields in each row.

Once you've launched Access, click the Open tool and locate the foreign file. Once you select the file and click OK, Access will create the database and set the links. If Access displays a link wizard, simply follow the instructions to complete the link. This capability isn't available with Access projects.

 

OPENING A DATABASE

You probably know that the File menu shows the most recently used databases. That means, instead of using the File, Open command, you can simply click one of these databases at the bottom of the File menu and Access will open it. The one drawback that we've found is that these files aren't available using the RunCommand macro action--which means you can't include the most recently opened files in a macro.

 

QUICK DATABASE PROPERTIES

You can choose File, Database Properties, and Access will display properties for the current database. However, the menu bar isn't always available. If you find yourself in this situation, try right-clicking the Database window title bar. Then, choose Database Properties from the context menu. Access will respond by opening the same tabbed dialog box.

 

DATABASE SHORTCUTS

You can perform several tasks from the Database window. To see a complete list, right-click the Database window's title bar. Here are just a few of the available tasks:

  • Open another database.

  • Import, link, and export objects.

  • Modify the current database's startup options.

  • Modify the current database's properties.

Other methods require a few more mouse clicks.

 

A CLICK TO THE DATABASE WINDOW

When working in a module, you may need to access the Database window to review another object. To do so, you can press F11. However, if you prefer a mouse shortcut, simply click the Database Window tool on the Visual Basic toolbar.

 

DATABASE WINDOW DEFAULT

By default, Access will open an object if you double-click it in the Database window. A single click simply highlights the object. After selecting an object with a single click, you can click the Open button on the Database window toolbar to open that object or double-click it.

You can change this behavior by choosing Tools, Options, then clicking the View tab. The Click option in Database window has two options: Single-click Open or Double-click Open.

If you prefer to open objects with just a single click, choose the Single-click Open option. Once you do, resting the mouse pointer on an object will highlight (select) that object and a single click will open it.

 

WHEN TO ELSE OR ELSEIF

The block If allows any number of conditional checks. First, the If statement itself specifies a condition. If that condition isn't met, control will pass to an ElseIf or Else clause. (You can also omit both and simply end the statement with an End If clause.) How do you know which to use? If you want to specify additional conditions, use ElseIf in the form

If condition1 Then

...

ElseIf condition2 Then

...

ElseIf condition3 Then

...

End If

You'll use the Else clause when you want to catch what falls through the cracks. That's because the Else clause doesn't accept a condition. For instance, we could add an Else clause to the above example:

If condition1 Then

...

ElseIf condition2 Then

...

ElseIf condition3 Then

...

Else

...

End If

Any value or expression not caught by condition1, condition2, or condition3 will be handled by the Else statement.

The thing to remember is that you can't add an ElseIf clause after the Else clause. Every If statement can have numerous ElseIf clauses, but only one Else.

 

CLICK EVENT ORDER

When you click on most controls, you produce a Click event. Right? Well, let's see. When you click a text box, the control fires its Click event--that much is true. However, if you click inside the text box component of a combo box, you don't trigger that control's Click event. So what's the difference? The combo and list box controls don't fire their Click event until the user selects an item from the control's list. This behavior is considerably different from other controls because they trigger their Click event as soon as the control receives the focus. So consider the outcome carefully when you attach code to a combo or list box's Click event.

 

EVENT OR PROPERTY

When you open an object's properties sheet, you'll see that object's events listed in the form

On event

As a result, you may think On is also part of the event's name, but it isn't. Anytime you see an event prefaced with On and a space between each word in the name, you're looking at the event's property name. In contrast, the actual event doesn't contain the word On and there's no space between the words. For example, On Click is the Click event's property. In addition, On Mouse Move is the MouseMove event's property.

 

MISSED EVENTS

Changing a control's value programmatically is a fairly common and useful task. But there's something you should know if you choose this route. Changing a control's value triggers both the BeforeUpdate and AfterUpdate events. However, when you change a control's value programmatically, the control doesn't trigger either of these events. So you'll want to be careful about placing code in these events when the control might be updated by VBA.

The form's BeforeUpdate and AfterUpdate events are still triggered if you alter a control programmatically.

 

HEADERS AND FOOTERS COME IN PAIRS

When you add a header to a form, you'll also get a footer, because they come as a pair. It doesn't matter whether you need both; you still get both. To eliminate an unnecessary header or footer, you need only close it. To do so, select that section and open its property sheet by clicking the Properties button. Then, set the Visible property to No.

 

HIDING ENTRIES AS YOU TYPE

Do you have to enter sensitive data--information that you wouldn't want anyone else to see? If you're using a data entry form, then use the control's Input Mask property to protect that data from being seen by prying eyes. Specifically, set the control's Input Mask property to Password. Then, as you type an entry, Access will display an asterisk character--instead of the actual character--for each character you type.

 

ID PLEASE

It's easy to add an identifying prefix to an AutoNumber field using the Format property. Why would you want to? To make the entity that value represents easier to identify.

For instance, let's suppose you use an AutoNumber field to assign a unique number to each order record. In this case, you might want to prefix each number with the string OrderID or OID. To do so, open the table in Design view and select the AutoNumber field. Then, use the expression

>OrderID

as the Format setting. You can use this technique with any field--we just find it particularly useful with AutoNumber fields.

 

CHOOSING MACRO ACTIONS

Macros automate a database by repeating actions and tasks for you. To create a macro, you open the macro window and enter a macro action in the Action column. There are three ways to enter a macro action:

  • Type it directly into the Action cell.

  • Choose the action from the Action cell's drop-down list.

  • Drag an object from the Database window to the macro window.

Now, you're probably familiar with the first two options, but the last option might be new to you. Once you've opened the macro window, click the Database Window button on the Macro Design toolbar. Then, drag an object from the Database Window into the macro window. Access will automatically default to the appropriate open action and enter the dragged object's name as that object's name property.

 

CONDITIONAL FORMATTING

Access 2000's conditional formatting feature is a big improvement over Access 97's format offerings. However, you can still use VBA instead. For instance, let's suppose you want to display certain report data in bold, depending on the contents of a second control. You could use the conditional formatting feature or you could attach code, similar to the following, to the Detail section's Print event:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If Me!chkName Then

Me!txtName.FontWeight = 700

Else

Me!txtName.FontWeight = 400

End If

End Sub

The above procedure will print the contents of a text box in bold print when a second control evaluates to True.

When chkName (which represents a checkbox) equals True, Access bolds the contents of txtName (which represents a text box). If the checkbox is False, Access doesn't bold the contents.

You can easily adjust this procedure to fit most any condition. Simply update the control names, properties, and settings, and specify an appropriate condition.

 

A CONDITIONAL MACRO

VBA doesn't have any trouble making decisions. You can use If or Select Case to check for conditions and direct your code accordingly. You might not realize that macros can make decisions, too. For example, let's suppose you want to open a form. In addition, you want the option to cancel the entire macro instead of opening the form. In this case, you might use the InputBox() function to determine whether the macro continues (and opens the form) or quits. First, open the Conditions column by clicking the Conditions Columns button on the Macro Design toolbar. Next, enter in the Conditions column the expression

InputBox("Do you want to open the form?") = "Yes"

When you run the macro, this expression will display an input box. If you respond to that box by entering the string "Yes", the macro will continue. If you enter anything else, the macro will quit.

 

UNDO FORM CHANGES

Access 2000 allows you to undo all changes made since the last save to the current form. With the form open in Design view, simply choose File, Revert, and Access will undo any changes you've made to the form since the last time you saved it.

If Revert is disabled, no changes have been made since the last save.

 

OPENING A DATA ENTRY FORM WITH A MACRO

In our previous tip, we showed you how to use VBA to open a form in data entry mode. If you'd rather use a macro, you can. First, open a blank macro window. Specify the OpenForm Action, and identify the form by name in the Form Name property. Next, specify the Form View setting, then choose Add from the Data Mode property field.

 

TEMPORARILY DISABLING A MACRO

An AutoExec macro runs when you first launch a database. If you want to disable the AutoExec macro once, you can hold down the Shift key when you launch the database. Occasionally, you may find yourself wanting to run a series of tests without activating the AutoExec macro each time you open the database. In this case, remembering to hold down the Shift key can become a nuisance. Perhaps the easiest way to temporarily deactivate a macro for a prolonged period of time is simply to change the macro's name. You see, an AutoExec macro must be named AutoExec. Renaming an AutoExec macro will keep Access from running it again until you change the name back to "AutoExec."

 

CUSTOM DATE FORMATS

Formatting a date is easy using the Format() function. But did you know that you can create a custom date format? You simply put together the appropriate code argument. For instance, the "d", "mmm", and "yy" codes display the day, the abbreviated month, and a two-digit year value, respectively. By putting them together as one code argument--"dmmmyy"--you can display March 3, 1999, as 3Mar99.

You can also display a literal character--such as a comma and space character--to create the string March 3, 1999. Just use this format function:

Format([DateField],"mmmm d"", ""yyyy")

We added a comma and space character by simply including those characters in the code. Just be sure to enclose any literal character in double quotes, as shown.

 

TAKE THE PAIN OUT OF REMEMBERING YOUR LAST DATE

A quick way to determine the first or last date in a date field is to run a Totals query. After specifying the query's group, select the First or Last aggregate function for the query's date field and then run the query. Doing so returns the first or last date, accordingly, for your group of records.

To specify a Totals query, open the query in Design view and choose Totals from the View menu. Doing so will display the Totals field in the query design grid. Open this field's drop-down list to see a list of aggregate functions, including First and Last

 

DISPLAYING FOUR-DIGIT YEAR DATES

Access 2000 takes the guesswork out of displaying four-digit years in your date fields. Now, you can force all your controls and fields to display four-digit years with just a few clicks. By displaying all four digits, you can ensure that a date's century is the right one with just a quick glance. To enforce this behavior, you'll need to access the Options command from the Tools menu and then click the General tab. In the Use Four-Digit Year Formatting section, you'll find two options:

  • This Database: Sets the four-digit formatting option for the current database.

  • All Databases: Sets the four-digit formatting option at the application level and affects all databases and Access projects.

Both options will override any Format property settings you've defined for individual fields and controls.

 

ENABLED VERSUS LOCKED

It's easy to assume the Enabled and Locked properties are the same. Even Help lists the two together. Although they're similar in some respects, they aren't the same and they aren't interchangeable. You'll use the Enabled property to determine whether a control can have the focus in Form View. Specifically, a Yes setting means you can set the focus to that control; a No setting means you can't. For the most part, that means you can't select the control in Form View--even if the control's Tab Stop property is set to Yes.

The Locked property will keep users from changing the contents of a control. You can still set the focus to the control, meaning you can select it. However, you can't change the control's contents.

 

IMPORTING MORE THAN OBJECTS

To import a table or form from another database, you probably use the Import command. Specifically, you choose File, Get External Data, Import, and then identify the database that contains the object you want to import. Next, you select the object and click OK.

You might not realize you can import a lot more than objects. For instance, you can import toolbars and relationships, and you can even import and export specifications. You'll find these import tasks by clicking the Options button in the Import Object dialog box. Thanks to a reader for this helpful tip.

 

DETERMINING A LEAP YEAR

With all the noise about Y2K bugs and leap years, we thought you might need to know how to tell if a particular year is a leap year. You can use an expression in the form

(year Mod 4 = 0) - (year Mod 100 = 0) + (year Mod 400 = 0)

to determine whether year is a leap year. If the year is a leap year, the expression returns -1; if it isn't, the expression returns 0. We've seen a lot of expressions, some shorter, but this is the only one we've come across that always works.

 

MORE ON DETERMINING A LEAP YEAR

In our previous tip, we showed you how to determine whether a year is a leap year by using an expression in the form

(year Mod 4 = 0) - (year Mod 100 = 0) + (year Mod 500 = 0)

However, this expression assumes you're working with a year value. If you're working with an entire date, this expression won't work. In that case, you can use the expression

(Year(date) Mod 4 = 0) - (Year(date) Mod 100 = 0) + (Year(date) Mod 500 = 0)

where date represents your date value or a field that contains date values.

 

SETTING DECIMAL PLACES

When you work with decimals, you must use the Single or Double data type. After specifying the data type, you should specify the number of decimal places using the Decimal Places property. When you're setting this property in Design view, the property offers a list of value settings from 0 to 15 and Auto. The Auto setting is the default and relies on the Format property to determine how many decimal values to display.

If you're using VBA to set this property, you can specify the values 0 through 15. If you want to specify the Auto setting, use the value 255.

 

DELETING DATABASE SHORTCUTS

Access 2000's Database window has a new look. The Object Bar offers shortcuts and the Database Window toolbar organizes object commands. You can open new objects in Design view or launch one of the object wizards by taking advantage of these shortcuts. For instance, if you click Tables on the Object Bar, Access will display the following shortcuts:

Create table in Design view

Create table by using wizard

Although many users find these shortcuts convenient, a few users find them annoying. Fortunately, you can inhibit these shortcuts. To do so, choose Options from the Tools menu. Then, click the View tab and deselect the New Object Shortcuts option in the Show section.

 

EMAIL ATTACHMENTS

You probably know that you can send Access data by email from inside Access. What you might not realize is that you can send an object as an email attachment instead of including the data in the actual message. To send an object as an attachment, select that object in the Database window. Then, choose File, Send To and select Mail Recipient (As Attachment) instead of the Mail Recipient option.

 

ECHO'S STATUS BAR MESSAGE

The Echo method freezes the screen so you can hide distracting tasks from the user. Did you know that you could also display a message in the status bar using the Echo statement? To do so, use the syntax

Application.Echo echoon[, statusbartext]

When echoon equals True, Access repaints the screen; when this argument is False, Access doesn't repaint the screen. The statusbartext argument is a string expression that Access displays in the status bar when the screen display is turned on or off. The statement

Application.Echo False, "Processing information, please wait."

would display the message

Processing information, please wait.

in the status bar.

 

CUSTOMIZING YOUR DICTIONARY

Do you have a strange name or a technical term that you use a lot but that the Spelling feature doesn't recognize? If this happens too often, the process can become tedious and annoying. All Office 2000 applications share a dictionary, and you can customize that file by adding jargon, names, or even technical and business terms. But how do you get your unique words into the dictionary? The next time you run the Spelling feature and it stops on an occurrence of one of these unidentified words, simply click Add in the Spelling dialog box. After you add the word, the Spelling feature won't identify the word as misspelled again.

 

PICK AND CHOOSE EXCEL DATA

It's easy to import data from Excel. However, most of the time you don't need to import an entire spreadsheet. When this is the case, assign a name to the cells that contain the Excel data you want to import. When you import the data, Access allows you to specify whether you're importing the entire spreadsheet or a named range. By applying this limit, you can avoid importing data you don't need.

 

EXPRESSION LIMITS

In our last few tips, we've discussed using an expression to return a random value using the Rnd() function. Did you know there's a limit on the number of characters you can use in any expression? There is--an expression can't contain more than 2,045 characters. While 2,045 characters should be adequate most of the time, it is possible to write an expression that Access simply can't evaluate because it's too long. If this should happen to you, there's no easy workaround. Perhaps the best solution is to write your own procedure function and break down the expression into components rather than trying to write the entire task as one statement.

 

FIELD LIST TO THE RESCUE

Creating calculated controls can be awkward when you need to refer to other controls in an expression or property but you can't remember their names. If this happens to you, don't play hide-and-seek games with your controls. Instead, open the Field List by clicking the Field List button on the Form Design toolbar. All of the form's controls are listed by their exact names in the Field List. Once you open the Field List, you can refer to it for the control names.

 

ANOTHER WAY TO CHANGE A FIELD NAME

Last month we talked a bit about changing a field's name. If you change a field's name in Datasheet view, you're making a permanent property change. If you change a field's name using a query expression, you don't modify the underlying table, but any subsequent object that's built on that query will use the new name you specify in the query expression.

If you'd like to change a field's heading without changing any of the underlying references, use the field's Caption property in the query design grid. Once you've specified the field in the query design grid, right-click that field (in the grid) and select Properties from the context menu. Next, enter the heading you'd like to see in the query results as the Caption property in the Field Property sheet.

Access will display the Caption property as the field's heading in Datasheet view (after you run the query). However, Access won't change the field's name in the query or the underlying table.

 

CHANGING FIELD NAMES

While we're on the subject of table design, you should be especially careful when you change the design of an existing table. You probably know that deleting a field will also delete the data in that field. However, what you might not realize is that if you add a field or change the name of a field, you must manually update your queries and forms if you want to include that new or modified field in your dependent object. Access won't automatically update your objects for you. In addition, if you reduce the size of a field, you might inadvertently truncate data, so be careful. For instance, if your field handles 50 characters and you change that to 6, any existing data that is 7 characters or longer will be trimmed after the sixth character.

 

QUICKLY CHANGING A FIELD NAME

While we don't recommend it, you can change a field's name. To do so, you could open the table in Design View, select the appropriate field cell, and then enter the new name. Fortunately, you can eliminate most of those steps by changing the name in Datasheet View. In your open table, double-click the field name cell (the gray cell at the top of the field). Access will respond by highlighting the field name--the cell will now be in Edit mode. At this point, you simply type the new name and press Enter.

When you rename a field name, be sure to update all your dependent references using the Name AutoCorrect feature. Then, use the Replace/Find Edit command to update any field references in your modules, since the Name AutoCorrect feature works only on objects--it won't update modules.

 

CHANGE THE DEFAULT FIELD SIZE

By default, Access assigns a Field Size setting of 50 to a Text field. If you find yourself resetting this property, you may want to change the default setting instead. To do so, choose Tools, Options and then click the Tables/Queries tab. Then, in the Text control in the Default Field Sizes section, simply enter the value you'd like all Text fields to default to.

 

CYCLING THROUGH FIELDS

Using VBA, you can easily cycle through a table's fields. The For...Each statement in the following procedure loops through all the fields in a table named tblMyTable. We're simply printing the field names to the Debug window, but you can use this loop for any kind of field task.

Function PrintFields()

Dim db As Database

Dim fld As Field

Dim tdf As TableDef

Set db = CurrentDb

Set tdf = db.TableDefs!tblMyTable

For Each fld in tdf.Fields

Debug.Print fld.name

Next fld

End Function

Since we're using DAO code and not ADO, you'll want to add the DAO library and remove the ActiveX library from your references. To do so, in the Visual Basic Editor, choose Tools, References and make the appropriate changes.

 

QUICK FIELDS

The Table Wizard can quickly create tables of predefined fields. However, you may not want a complete table. When this is the case, you can still take advantage of the wizard's available fields. With your table in Design View, click the Build button on the Table Design toolbar to launch the Field Builder. Specify a table and then double-click any of the corresponding fields to add that field to the current table. This is a quick and easy method for adding fields to an existing table, because all the field's properties are already set. You may have to alter a few properties, but doing so should be quicker than building the field from scratch.

 

COUNTING YES/NO FIELDS

Access offers an unusual data type named Yes/No. It's really a Boolean data type and stores one of two values: -1 or 0. The field displays a small check box. When checked, it equals Yes, or -1. When not checked, it equals No, or 0.

In earlier versions of Access, a simple Sum function was used in a query expression to count the number of Yes responses in the form

Yes: Sum(field)

The function returned a negative value, as all the fields contain only -1 or 0. If the negative value is a problem, you can wrap the expression in an Abs() function in the form

Yes: Abs(sum(field))

Access 2000 can handle this too, but you might have to alter the query's SQL expression. If you receive an error message about trying to group on fields with the asterisk character, open the query's SQL window by clicking SQL from the View button and delete the * character and the comma right before the asterisk in your SQL clause.

If you want to count the No fields, use a query expression in the form

No: Count(field) + sum(field)

This simple expression will count the number of fields and then add the absolute value of just the yes fields. For instance, if you have three records and two contain Yes values and one contains a No value, this expression would evaluate as 3 + -2, which equals 1.

 

LOOKUP FIELD RELATIONSHIPS

Using the Lookup Wizard to create a lookup field in a table creates a relationship between the current table and the table that contains the values you want to display in your lookup field. That means you can't later delete either field because you can't delete fields that are part of a relationship.

If you decide to delete one of these fields, you may delete the lookup reference in the lookup field to delete the relationship, but that won't work. To delete the relationship, you must open the Relationships window, display the related tables, and then delete the relationship between the two tables. The easiest way to do that is simply to select the relationship line between the two windows and press the Delete key.

 

WHEN ALL IS TOO MUCH

A query will return all the records that meet the conditions you specify in your query. If you want to limit the number of records your query returns, you can use the Top Values property. You can enter a number or a percent value. If you just want a one-time peek at a certain percentage of the query results, don't go to all the trouble of changing the Top Values property. For a temporary view, choose a value from the Top Values control on the Query Design toolbar. (This control displays the word All by default.) Access will retain this setting until you change it or you close your query without saving that change.

 

NUMLOCK PROLEM IN ACCESS2000

Having trouble keeping your NumLock key on while using Access 2000? Microsoft has acknowledged a problem with Access 2000 where the NumLock key keeps turning off. A well-known bug in all versions of Access causes SendKeys to switch off the NumLock key. Unfortunately, no solution is available at this time. Keep an eye on the Microsoft Support Web site for a future fix.

 

RERUNNING A PARAMETER QUERY

A parameter query is a query that prompts you for specific data to search for (and limit the query to). For instance, if you want to see sales by each region, you could create a query that prompts you for a particular region. You'd respond to the prompt by entering a region, perhaps Northeast, and clicking OK to run the query.

Now, the reason we create parameter queries is because we run the same query on a variety of different entries. Following our region example, you'd probably want to run the query for each region, right? You might think you have to close the query and reopen it. Or you might consider changing the view to Design View and rerunning the query from there.

Fortunately, there's a simpler way. Simply press Shift-F9 to rerun a query. Doing so will redisplay the parameter box so you can enter a search string and begin again.

 

RESTRICTING A PARAMETER QUERY

Parameter queries are useful, easy to create, and somewhat improved in Access 2000. In earlier versions, a parameter might accept invalid data. For instance, if you were limiting your records by the contents of a numeric field and you entered text, Access accepted the incorrect parameter and simply returned no matching records. Version 2000 restricts a parameter to the same data type as the field you're querying. If you attempt to enter inappropriate data, Access will display a generic error message. This improvement means that specifying restrictions for parameters is unnecessary in most cases.

 

RENAMING FIELDS IN A QUERY

Sometimes we take shortcuts when we name our fields, trying to make things easy. Little do we realize that we may complicate things down the road. For instance, TelNo may not be a good label for the telephone number control on a data entry form.

You're probably very familiar with Access's label behavior in this respect--Access will display the field's name in a text box's label component. If you base the form on a table, you'll have to reenter the label's Caption property. However, if you base the form on a query, you can create a new reference to that field. For instance, if your field's name were TelNo, instead of just dragging that field to a Field cell in the query design grid, you'd enter the expression

Telephone Number: [TelNo]

That way, Access would display the TelNo field but label it Telephone Number.

Of course, if you're working with only one form, this behavior won't make much difference. You can reenter the field name in the label control, or you can reenter it in the query design grid, but either way, you're still reentering the field name. However, if you base several objects on this query, you can avoid reentering the field name.

 

USING WILDCARDS IN A QUERY--PART 1 OF 2

Access allows wildcards in a parameter query. Just remember to use the Like operator in your parameter expression. For instance, if you want to return all your employees whose last names begin with the same letter, you'd use the parameter expression

Like [Enter the first letter of the last name]&*

When you run the query, Access will prompt you to enter the first letter of the last name. After you do so and click OK, the parameter expression will evaluate to

Like "x"*

where x is the letter you entered. For instance, if you entered the letter S, the expression will reduce to

Like "S"*

As a result, Access will return only those records whose entry in the last name field begins with the letter S.

USING WILDCARDS IN A QUERY--PART 2 OF 2

In our previous tip, we talked about using the * wildcard with the Like operator to return all the records whose last name field began with a specific letter. The Like operator is very versatile and can be used in several situations. For instance, let's suppose that each piece of inventory in your warehouse is identified by a part number that consists of two digits, a hyphen, two more digits, a second hyphen, and then two alpha characters. Furthermore, let's suppose that you want to see all the records for part numbers between 11-XX-XX and 12-XX-XX. You might waste a lot of time trying to come up with a Between statement, but the simplest solution is the Like operator. Use a criteria expression in the form

Like "11*" Or Like "12*"

The resulting query will return any part number that begins with the string 11 or 12. Don't forget the * character, or you'll end up with a completely different (and useless) set.

Unfortunately, this expression won't work if you need to see all the records between two nonconsecutive values, such as 11 and 13.

 

SELECTING A FIELD IN DATASHEET VIEW

Working in an open table that has many fields can be difficult because you can't see many (sometimes most) of your fields. To select a field that you can't see quickly, simply choose that field from the Go To Field control on the Formatting (Datasheet) toolbar.

If the Formatting (Datasheet) toolbar isn't open, right-click any toolbar and choose Formatting (Datasheet) from the resulting list.

 

CHANGING YOUR MIND IN DATASHEET VIEW

Many people work directly with data in Datasheet view. When you're working in Datasheet view, you can easily change your mind if you change an existing field entry in one of three ways:

  • Press the Undo button on the Table Datasheet toolbar.
  • Choose Edit, Undo Typing.
  • Press the Esc key.

 

QUICK FILE ACCESS

The Open dialog box offers several ways to open a database. The bar on the left is the Places bar. Each icon is a shortcut for opening files a little quicker than the traditional method of browsing through all the folders. The History folder displays recently opened databases. Unless you've changed your default settings, your Microsoft Office documents are stored in My Documents. You can quickly access files you've stored on your desktop by clicking the Desktop icon. The Favorites directory isn't a real directory, but rather a collection of shortcuts to your most frequently used files. Web Folders is similar to Favorites--it stores shortcuts to Web sites.

 

PUT YOUR NAME IN THE FOOTER

You can always put the name of your report right at your fingertips if you include the expression

=CurrentObjectName

in the footer of your report. Simply add a text box to the footer and enter that expression. The report then prints the report's name in the footer (or wherever you position the text box). If you want to print the entire path, use the expression

=CurrentDb.Name & " " & CurrentObjectName

 

OPENING A DATA ENTRY MODE FORM WITH VBA

You probably know that you can limit a form to data entry mode by using the Data Entry property. Specifically, you simply set the property to Yes. The No setting allows data entry but also shows bound records. By activating data entry mode, you can use a form for data entry purposes only. That way, the form is bound to a data source, but the user doesn't have access to existing records.

If you'd like to use the same form to switch back and forth between modes, you can use VBA to open the form in data entry mode using the OpenForm method in the form

DoCmd.OpenForm "formname", , , acAdd

The acAdd argument specifies the form's data mode.

 

VIEWING THE WEB FROM YOUR FORM

To visit the Web while you work in Access, you can create a hyperlink that launches a specific Web site, or you can use the Web toolbar. However, both methods launch your default browser, which may or may not be what you want.

If you want to view Web sites but remain in Access, you can do so by using the Microsoft Web Browser control. You add this control to a form and specify a URL, and the control displays the Web site from inside your Access form. Here's how to set it all up.

First, create a table of URLs. Base a form on that table, then add a text box and name it txtURL. Click the More Controls button on the Toolbox and select Microsoft Web Browser from the list. Add the new control to your form and name it msWebBrowser. Next, open your form's module and enter the following statement in the form's Current event:

Me.msWebBrowser.Navigate txtURL

When you run the form, the Web browser control will display the appropriate Web site for that record's corresponding URL (in txtURL).

 

ABOUT THOSE FORMATS

A format determines how Access displays (or prints) your data. You may think of Access as a smorgasbord when it comes to formatting options, but push yourself away from that table before you make a mess! Professionals agree: Limit special formatting for emphasis. In other words, you should apply formats sparingly, and only when they complement the data. Otherwise, your formats may lose their appeal. In fact, too many formats in the pot can create a negative distraction.

 

MORE ABOUT MS FORMS 2.0 LISTBOX CONTROL

In a previous tip, we showed you how to add items easily to a Microsoft Forms 2.0 ListBox control. When you're using the AddItem method, you can also specify the position of the new item within the existing list. For instance, we used the simple procedure below to add two items to a list:

Sub Form_Open()

lst.AddItem "red"

lst.AddItem "blue"

End Sub

If you wanted to add the item "white" between the already existing items, you'd use the statement

lst.AddItem "white", 1

 

MORE ON OPEN FORMS

A few tips ago, we shared a procedure that returns True if a form is open in Form View and False if that form is open in Design View. If the form isn't open at all, your procedure will return an error.

Function ObjectState(name As String) As Boolean

If SysCmdname) <> 0 Then

(acSysCmdGetObjectState, acForm, ObjectState = Forms(name).CurrentView <> 0

End If

End Function

This function will return True if the form is open and in Form View, and it will return False if the form is open and in Design View or not open at all.

 

MORE ON THE OPEN FORM METHOD

In our previous tip, we showed you how to open a form in read-only mode using the acReadOnly constant with the OpenForm method. There are two other mode constants: acAdd and acEdit. The acAdd constant allows you to add new records using the form, but you can't view or edit existing data. The acEdit constant allows you to edit existing records and add new ones--which is why this constant is the method's default value.

 

USING POP-UP FORMS

Pop-up forms stay on top of all the other forms and objects, regardless of which object is active. They're great for displaying additional information or for requesting data from the user. There are two kinds of pop-up forms: modeless and modal. The one major difference between the two--a modeless pop-up form allows you to access other objects, and a modal pop-up doesn't. You must respond to a modal pop-up form before Access will release the form and allow you to continue.

You create a pop-up form the same way you would create any other form, but set a modal form's Pop Up and Modal properties to Yes. When creating a modeless pop-up form, set the Pop Up property to Yes and the Modal property to No.

If you open a modal form using OpenForm, you must be sure to specify the acDialog constant in the form

DoCmd.OpenForm "formname", , , , ,acDialog

If you omit the acDialog constant, Access will ignore the Modal property setting.

 

QUICKER FORMS

Almost all forms are bound to a data source--a table or a query. If you're working with a query and you don't need to change data, you can speed things up a bit by changing the bound query to a Snapshot recordset type.

By default, queries are Dynaset recordset types, which means that you can modify the bound data. However, dynasets take longer to display. Consequently, your form will load faster if you change the bound query to a snapshot.

To change a query's data type, follow these few steps: First, open the query in Design View. Then, right-click the query's background (the gray background in the top pane). Choose Properties, and then change the Recordset Type property to Snapshot.

You probably won't notice much difference between the two query types unless you're working with lots of data.

 

READ-ONLY FORMS

Access data is easily available, which means it's also easily deleted or corrupted. If you use forms to display data, you may not want users also to be able to change that data. If this is the case, open the form in Design View and set its AllowEdits property to No. You can also set this property programmatically using the Open Form method in the form

DoCmd.OpenForm "frmReadOnly", acNormal, , , acReadOnly

The acReadOnly constant opens the form in read-only mode.

 

GROUP MEMBERS

By default, the Object Browser alphabetically sorts by properties, methods, and events. If you'd like to see the members grouped by type, you can. You must first be in a module to view the Object Browser, so open a module. Then, open the Object Browser by pressing F2 or clicking the Object Browser button. Next, right-click the background in the Classes control, then choose Group Members from the shortcut menu.

 

STORING GROUPS OF OBJECTS

Many Access 2000 users are curious about the new Groups option on the Places toolbar (in the Database window) and the Favorites folder. This new feature has several uses, but one of my favorites is hiding objects the user doesn't need to see. Most applications are full of tables and queries that the user never opens or interacts with, and they can create a lot of clutter. Moving these objects to the Favorites folder in the Groups section can clean things up a bit. To add an object to the Favorites group, simply drag the object to that group and drop it.

Once you've moved the object to the Favorites group (actually, you're just creating a shortcut), hide the object in the Database window by right-clicking it, choosing Properties, selecting the Hidden object, clicking Apply, and then clicking OK. Now you still have easy access to the object, but it won't clutter up the Database window.

 

QUICK HELP ON THE WEB

When you need help, you usually need it quickly. If you can't find that information in the Help files, try searching the Web. Fortunately, that's not as difficult as it may sound. Simply choose Help, Office On The Web to display a number of online sources, such as the Knowledge Base. Just check for the Technical Articles link.

Of course, you must have Internet access for this tip to be of benefit.

 

A HYPERLINK ALTERNATIVE

Sometimes we use command buttons to open other Access objects, but there's an easier alternative. Our alternative will also open objects faster than VBA code. Instead of inserting a command button and using code to open other objects, try using a hyperlink.

With the form in Design View, choose Insert, Hyperlink. In the Insert Hyperlink dialog box, skip the first text box and enter the name of the object you want to open in the Named Location In File (Optional) control. Or click the appropriate shortcut to the left and point to the object or create the object. Then, click OK.

Access will insert a label with a hyperlink into your form. When you want to open the object named in the hyperlink, simply click it.

 

HYPERLINK SHORTCUT

Hyperlinks are popping up all over, and it's no wonder. They perform a unique and useful task, and they're easy to create. You can choose Insert, Hyperlink, or you can insert a label, enter text, and press Ctrl-K. Pressing Ctrl-K will display the Insert Hyperlink dialog box, where you can identify the link. That method is especially convenient when you want the hyperlink to display text other than the link's address.

 

EDITING ICON

Have you ever noticed that little pencil that appears in the gray cell just to the left of the record when you enter data directly into a table in Datasheet view? Obviously, it means you're editing a record, but it means more than that. The pencil indicates that new data has been entered but not saved. So don't ignore the image, and be sure your data is properly saved.

Fortunately, you don't have to do much to save a new record or modified data. Anytime you move the focus from the current record, Access saves your changes. In other words, simply move the cursor to another record, and Access will automatically save any changes you've made.

 

IMPLICITLY TRUE

A few tips ago, we showed you a short procedure that applies print formats depending on a condition. In that procedure, we used the following statement to check a control for a True value:

If Me!chkName Then

This conditional statement states an implicit form of the True value, and it evaluates differently than the explicit form. Specifically, as long as chkName's entry is any value other than 0, this statement will evaluate to True. On the other hand, when expressed explicitly in the form

If var = True Then

var must evaluate to -1 in order for the condition to return True. Any value other than -1 will evaluate to False.

Keep this behavior in mind when depending on the implicit True value--the results might not be what you expected.

 

AN AUTOMATIC INDEX

Have you ever discovered an index you didn't set? Does the name of the mysteriously indexed field begin or end with one of the following string components: ID, key, code, or num? If so, Access set your index for you thanks to the AutoIndex on Import/Create feature.

The AutoIndex on Import/Create feature creates an index automatically, when the name of a field begins or ends with a specific string. The default strings are those we listed above, but you can change them. To do so, select Tools, Options, then click the Tables/Queries tab. If you don't want Access to set indexes for you, delete the current setting and leave the control blank. After altering the setting, remember to click Apply.

Having Access create an index for you can be handy, but it can also be a little dangerous if you don't use consistent naming conventions. Access doesn't know whether the index is needed; Access just goes by the field's name. As a result, you could end up with inefficient indexes.

 

CONFUSION ABOUT INDEXES

Are all those indexes really necessary? Here's a suggestion. Temporarily remove all your indexes from your tables--except from the primary keys (leave those intact). If you want, make a copy of your database and work from your copy instead of making changes to your original file. After removing the indexes, run the Performance Analyzer. The Analyzer will make index recommendations that are specific to your database. If the Analyzer doesn't suggest an index that you had before running the Analyzer, chances are you don't need that index. To run the Analyzer, choose Tools, Analyzer, Performance.

Just keep in mind that the Analyzer can't review and make recommendations for indexes created by VBA.

 

INSERTING ROWS AND COLUMNS

If you're still using the Insert menu to insert rows and columns, there's a quicker way. First, right-click the header cell for the column or row to the right or just below the column or row you want to insert, respectively. (The header cell is the gray cell at each column and to the left of each row.) Access will respond by displaying a shortcut menu. Select Insert to insert a column or row. You just saved yourself a couple of keystrokes.

 

JET LAG

If you've experienced a problem converting an Access 97 database to Access 2000, take a look at the Access 97 queries. If you have a series of complex relationships, that may be your problem. The Jet 3.5 that comes with Access 2000 creates an index for both tables on either side of a relationship--two for one, you might say. Since Access allows only 32 indexes per table, a complex table with many relationships can quickly exceed this limitation. As a result, the conversion to Access 2000 could fail.

If you find yourself in this situation, the only choice you have is to delete some of the relationships and try the conversion process again.

 

ADDING AN ITEM TO A LIST BOX

It's rather difficult to add an item to an Access list box on the fly. If you need this functionality but would rather skip the extra work, use the Microsoft Forms list box instead of the Access list box. Microsoft Forms controls--and there are several to choose from--are more flexible than the native Access controls.

To insert a Microsoft Forms 2.0 ListBox control into your Access form, in Design View, choose Insert, Microsoft Forms 2.0 ListBox, then click OK. Or click More Controls on the Toolbox and select the control from the resulting list. Once you've inserted the control, you can use that control's properties and methods just as you would a native control's properties.

One of the Microsoft Forms 2.0 ListBox methods is AddItem, which uses the syntax

object.AddItem "item", index

The procedure below will add two items to the list box when the form is opened:

Sub Form_Open()

lst.AddItem "red"

lst.AddItem "blue"

End Sub

To remove an item from a list, use the RemoveItem method in the form

object.RemoveItem index

where index is the position of the item you want to remove (the first item equals 0).

 

ADDING BLANK LINES TO LISTS

In our previous tip, we showed you how to add a second (or more) column of items to a list or combo box list. You can use this same method to display blank lines in a control. Specifically, insert a zero-length string between each item in the list in the form

"item1";"";"item2";"";"item3";""

In addition, be sure to select the Value List setting from the Row Source Type property. The Column Count property should be set to 1.

 

ADDING ITEMS TO LISTS

The combo and list box controls allow you to enter a list of items. When you choose this route, you enter in the control's Row Source property a setting in the form of

"item1";"item2";"item3"

You must also specify the Value List setting for the Row Source Type property. The result is a list of items, one right after the other, in the same order as they appear in the Row Source property setting.

You might not realize that you can display more than one column of items in a Value List control. To do so, you simply add a second item to the list in the form

"item1a";"item1b";"item2a";"item2b";"item3a";"item3b"

Access will display items 1a and 1b in the first row, items 2a and 2b in the second row, and items 3a and 3b in the third row. Just be sure to update the Column Count property to reflect the appropriate number of columns.

 

AVOIDING A MACRO

Macros help us automate many redundant procedures, but on occasion you need to ignore them. When this is the case, you can in effect turn off the macro until you're ready to use it again. To do so, open the macro window and enter the value

False

in the Conditions column. Save and close the macro and return to your work.

When Access encounters the command to execute that macro, it appears to ignore the request. Actually, Access is running the macro, but it's interpreting the False value, which means the same as doing nothing.

When you're ready to use the macro again, open the macro window and delete the False value. Remember to save this last change.

 

MEMORY HOG

If you've converted any 97 or earlier .mdb files to Access 2000, you may have noticed that your converted files are much larger than the original. That's because Access 2000 supports Unicode, which increases storage space by almost 100 percent. Why? Unicode uses two bytes per character.

What's Unicode? Unicode is an international standard that allows Office to read foreign data. As long as the foreign application supports Unicode, Office can read the data.

A quick and simple fix is to compact your converted database.

 

TAKE BACK YOUR MEMORY

If you use the Table Wizard to create tables, you're probably saving a lot of time. However, you might be wasting some of your resources. If you have tons of memory--and many of us do--it doesn't really matter. Regardless of the amount of memory you have, inefficient is still inefficient. Besides, if you're working with thousands of records, resources may become an issue in the future. In either case, open any wizard-created table and check the field properties. You'll find that the wizard always uses the data type defaults. For instance, text fields use the Field Size default setting of 50. If the field will consume only six characters, you're wasting space. In many cases, you can reset some of these field properties and save on resources.

 

MORE ON CUSTOMIZED MESSAGES

In our previous tip, we showed you a simple report expression that will print custom messages. If you need a bit more emphasis, try applying a few special formats to the message control. For instance, if the message is urgent and you want to make sure it isn't missed, change the color of the control's font to red. If you want the message to stand out but without the urgency the red may imply, try using the Italics or Bold style.

 

CLOSING OPEN MODULES

In our previous tip, we suggested that you avoid using the Compile All Modules command during the development stage. There's something else you should be aware of: Using the Compile All Modules command loads all your modules into memory, and Access doesn't unload them. That means they continue to consume memory unnecessarily. We suggest that when you use the Compile All Modules command you also close your database afterward to close all those open modules and free up the memory they consume.

 

MORE ON CLOSING ALL MODULES

A few tips ago, we suggested that you close your database after running the Compile All Modules command. The truth is, you'll probably want to close your database a couple of times during every work session (if your sessions are long).

When you call a function, Access opens the module that contains your code. Unfortunately, Access doesn't offer a programmatic way to close that module once you've run the procedure. That means you eventually end up with lots of modules open--and consuming memory--long after you need them. To free up memory used by open modules, simply close and reopen your database. (Don't exit Access; just close the database.) Doing so will close all your modules and free up the memory they would otherwise consume.

 

SAVING YOUR SPOT IN A MODULE

When working with code in the VB Editor, you can return to your form or report without losing your spot in the code module--and you don't have to do a thing to mark your place. The next time you're working in a code module, pay close attention to the position of the cursor before you close the module. After closing the module, reopen it and you'll find the cursor in the same spot you left it in. That way, you can go right back to the code you were working on if necessary.

 

INSTALLING MSDE

You may have read about Microsoft Data Engine (MSDE)--a new technology that provides local data storage that's compatible with Microsoft SQL Server 7.0. However, you can't install it by running the Office 2000 Setup program.

If you want to add this new functionality to your system, insert the Office 2000 CD 1 and locate the \SQL\x86\Setup folder. Then, double-click Setupsql.exe.

 

FORMATTING TEXT IN A MSGBOX

In Access 97 you could add formatting codes to a MsgBox function. This feature is still available in Access 2000, but you'll have to use the MsgBox action because the MsgBox function doesn't support the formatting codes in Access 2000. For instance, the following steps will display a message box's first line of text in bold.

First, open a macro window by selecting Macro in the Object bar and then clicking New. Choose MsgBox from the first Action cell's drop-down list. Then, in the Message argument field, enter the following string (don't use quotes as you sometimes do with strings):

This line is bold@This line is plain@This line is plain

Click the Save button on the toolbar and name your macro. Then, click the Run button.

Access will display a message box with three lines of text. The first line, "This line is bold", will appear in bold typeface. The next two lines will both display the text "This line is plain" in normal, or plain, typeface.

 

NORMALIZING CHOICES

One of the normalization rules states that all fields in a table should relate to that table's primary key. However, sometimes that's a tough call--just how much normalization is enough? For instance, if you're working with a customer database, should you have a table just for customer names, another for addresses, another for phone numbers, and so on? Or should you use just one table to store all your customer information? For the most part, there's no clear advantage to separating all your customer data. There are two situations when you'll want to consider creating a second table:

  • When a field is optional, meaning many fields may remain blank.
  • When a field has more than one possible entry.

Let's consider the customer's phone number data. Some customers may not have a phone (not likely, but it is possible). Other customers may have more than one phone, or type of phone. For instance, many businesses have dedicated fax lines and cell phones. In this case, you have three design possibilities:

  • Store all the customer information, including the phone numbers, in one table.
  • Create a separate table for phones with a one-to-one relationship.
  • Create a separate table for phones with a one-to-many relationship.

Now, what are the pros and cons for each solution? Well, a single table will produce quicker data retrieval since you're accessing only one table. However, you may have blank fields and you may end up duplicating data if the customer has more than one phone. In fact, if that's the case, you really have no choice but to create a second table. If you care about storing only one telephone number--say, the customer's direct business line--then the second option will work. The third option is more flexible than the second, but if the additional numbers aren't needed, it's wasted effort. In the end, the choice is yours--being familiar with your data is the fastest ticket to good normalization.

 

PLANNING FOR NULL PARAMETERS

It's easy to use the contents of a control as the criteria in a parameter query. Instead of hard-coding the criteria in your expression, use an expression in the form

Forms!formname!controlname

When you run the query (the form must remain open), Access checks the control for its contents and use that value as the query's criteria. If the control is blank, the query returns a blank record set. If you'd rather return all the records when the control is blank, use the query expression

Forms!formname!controlname OR Forms!formname!controlname Is Null

You can enter this as one expression as shown, or you can break it up and enter the first part in the Criteria cell and the second part in the Or cell.

 

CATCHING RECORDSET NULLS

You may not anticipate Null values in a recordset, but sometimes they're there just the same. If you aren't prepared for the Null value, your application will come to a screeching halt with the dreaded

Invalid Use of Null

error. To avoid this, you should test field values for Null using the IsNull function in the form

If IsNull(rst!fieldname) Then

where rst represents the recordset and fieldname is the field you're accessing.

 

ANOTHER QUICK NULL CATCH

Our previous tip showed you an easy way to avoid Null errors by catching Nulls before they have a chance to stop your code. Another quick method for catching Null values is to use the Format property in the form

var = Null

str = Format(var)

where str is a String variable and var is a Variant. The following procedure runs a quick check for Nulls using this method:

Function CatchNull()

Dim var As Variant, str As String

var = Null

str = Format(var)

MsgBox str = vbNullString

End Function

If var is Null, the message box displays True (False when var isn't Null).

Using Format returns a zero-length string; the vbNullString constant, used in this context, will catch a zero-length string.

 

WHEN A NUMBER ISN'T A NUMBER

We store lots of numerical data we never use in calculations. Phone numbers, Social Security numbers, and addresses all contain numbers, but you'd never try to use this type of data in a mathematical expression. Since you don't need these numbers to act like values, you can save memory by storing this type of data as text instead of numbers. A text field can and often does consume less memory than a number field.

Just remember that your numeric entries will sort differently as text. For instance, the values 1, 2, and 11 won't sort in numeric order.

Instead, these three values will sort as text--1, 11, and 2.

 

OBJECT SHORTCUTS

You probably know how to create shortcuts to files, but did you realize that you could create a shortcut to an Access object? Access objects are stored together in the same .mdb file. That means you must launch Access and open the database to work with an Access object--say, view a report. If you routinely work with the same object, a shortcut may be just what you need. To create a shortcut to an Access object, right-click its icon in the Access Database window and choose Create Shortcut. Access will offer to save your shortcut on the Windows desktop. You can go with this option, or click the Browse button to find a new location.

 

COPYING OBJECTS

You can copy an object without using the Edit menu's Copy and Paste commands. Simply select the object you want to copy and hold down the Ctrl key while you drag that object to the end of the object list and then release it. Access will create a duplicate and name it "Copy of filename." For example, if you want to copy a report named MyReport, you'd select it, hold down the Ctrl key, and drag MyReport to the end of the report list. Access would then display a new report named "Copy of MyReport" in your report list.

 

NEW OBJECTS AT A CLICK

There are two ways to use the New Object button: You can click the drop-down arrow to display a list of items, then choose the appropriate object, or you can click the button itself. This button displays the type of object you last selected. To create another object of the same type, simply click the button instead of opening the drop-down list and selecting that object type.

 

IMPORTING QUERIES

In our previous tip, we told you how to import toolbars and more using the Import command. You can also import a query as a table. To do so, choose File, Get External Data, Import. After identifying the database that contains the query you're importing, select the query you want to import as a table. Next, click the Options button in the Import Object dialog box and select the As Tables option in the Import Queries section. Any query you import with this option selected will take the form of a table in the importing database.

 

DEFAULT VALUES IN PARAMETER QUERIES

A reader sent us a great tip for using default values in parameter queries. As is, you can't specify a default value for a parameter in query. For instance, let's suppose you use the same parameter query to check sales by region. When you run the query, the parameter expression prompts you for a region. You enter a region, and Access will then display the results for that region. Now, let's further suppose that you run this query for one particular region most of the time. In that case, it would be nice if your parameter query would automatically default to that region. Then, you can run it by just accepting the default--changing it only when you needed to see the sales totals for another region.

He explains that if you use the InputBox function in your parameter expression in the form

Eval("InputBox(""prompt:"",""title"",""defaultvalue"")")

Access will display an input box instead of the normal parameter prompt--with the default value. If you click OK, Access will run the query using defaultvalue as the query's criteria. Or you can enter a new value and click OK to run the query.

 

QUERY LIMIT

Access limits a query to 255 fields. We can say this with a smile on our face because, frankly, most queries will never reach this limit. However, it isn't impossible to have a query so large and complex. If one of your queries should reach the limit, you do have a few alternatives. First, try the most obvious solution and eliminate unnecessary fields. This should be easy if you used the asterisk character to add fields to the query design grid. Chances are there are a few fields you don't really need but are included only because you used the asterisk character (which includes all fields). Second, if your query is the data source for a form or report, consider adding a subform or subreport to the main object and splitting your query in two, accordingly.

 

BYPASS PARAMETER QUERY

We often base reports and forms on parameter queries. You can get a similar effect by adding a parameter to a report control. For instance, let's suppose you want to display a particular date in your report, but the date will change from report to report. An easy solution is to add a control, name it txtDate (or whatever you like), and then add the Control Source setting of

[Enter letter date]

or something similar. When you run the report, Access will prompt you for a date. You'll enter one and click OK, and as a result, txtDate will display the date you entered in your report.

This solution won't act as a filter for your report--it's simply an easy way to display changing information.

If you decide to test this technique, open an existing report; itwon't work in an unbound report.

 

USING THE SIMPLE QUERY WIZARD--PART 1 OF 2

Queries aren't your cup of tea? Don't worry. If you can't make heads or tails of the query design grid, make the Simple Query Wizard your new best friend. Although this wizard is a bit limited--it retrieves data but doesn't limit data--it can make quick work of many of your query needs.

There are several ways to launch the Simple Query Wizard:

  • Click the Queries tab in the Database window, select New, and then double-click Simple Query Wizard in the New Query dialog box. (Or select the wizard and click OK.)
  • Click the Tables tab in the Database window, select the table you want to base your query on, choose Query from the New Object button's drop-down list, and then double-click Simple Query Wizard.
  • In the Database window, select the table you're basing your query on; choose Insert, Query; and double-click Simple Query Wizard.

USING THE SIMPLE QUERY WIZARD--PART 2 OF 2

When using the Simple Query Wizard to create your query, you can include more than one table or query in your query. Simply specify the first table or query by selecting that table or query from the Tables/Queries control. If you want to include a second table or query, simply select an additional source from the Tables/Queries control. The wizard will add all the fields from any table or query you select in this manner.

 

RENAMING OBJECTS

In our previous tip, we told you how to copy a database object quickly using the Ctrl key. When you use this technique, Access creates a copy of your object and names it "Copy of filename."

Once you have a copy of an object, you'll probably want to rename it. There are a few ways to do so:

  • Right-click the object in the Database window.
  • Choose Rename from the resulting shortcut menu.
  • Enter the new name.

Or you can rename the object right in the Database window:

  • Slowly double-click the object in the Database window. When we say slowly, we mean to apply two single clicks, one right after the other, but not so quickly as to constitute a true double-click.
  • Access will enter the object name in Edit mode--you can tell by the thin border that Access displays around the object's name.
  • While in Edit mode, simply enter the new name and press Enter when you're done.

 

EDITING THE OFFICE 2000 DICTIONARY

In our previous tip, we told you how easy it is to add a word to the Office 2000 dictionary while running the Spelling feature. You can also edit the dictionary directly, and doing so is almost as easy as clicking the Add option in the Spelling dialog box.

First, launch Word and choose Tools, Options. Click the Spelling And Grammar tab, choose Dictionaries from the Custom dictionary list, and select Edit.

Enter each new word or term on its own line.

 

IMPROVING PERFORMANCE

Speed is an issue with any application, and we've made many suggestions to enhance speed over the past several months. An easy way to improve an application's performance is to split it into two databases. Specifically, you should separate the interface objects (forms, reports, queries, etc.) and your data (tables). Access comes with a feature that will move your tables to a new database, known as the back-end, and then link those tables to your interface objects, known as the front-end.

To split your database, choose Tools, Database Utilities, Database Splitter. In the resulting window, click the Split Database button. The feature will then display the Create Back-end Database window and supply a default name, which you can change if you like. Click Split to continue. Be aware that the process can take a few minutes.

Before you split your database, you should make a backup of your application, just to be safe.

 

PRINTING PARAMETERS

If you've based a report on a parameter query, you can print the parameters as part of your report. For instance, if your parameter is a date, you can print that date in the header of your report. Simply add a text box to your report's header and specify the parameter by name as the control's ControlSource using the syntax

=Reports![reportname]![parametername]

Let's suppose you want to add the feature to a report named rptDates and your query's parameter is [Enter Date]. In this case, you'd enter the expression

=Reports![rptDates]![Enter Date]

 

A QUICK PRINT

You can quickly print a form and its code from the VB Editor. Simply select the form you want to print and then choose File, Print on the VB Editor's Standard toolbar. The VB Editor will then display the Print dialog box, which will offer several options. You can print the form's image, its code, or both. You can even change the print quality (the default is High) or send the form to a print file.

 

ADO CURRENT PROJECT

We've had a lot of people ask how to use ADO to connect to the database. That's because ADO is typically used with foreign data (although we'll all be using it in the future as Microsoft plans eventually to replace DAO with ADO). As a result, most examples and documentation stick with connections to files other than the current file. If you'd like to use ADO in the current database, use the following code to open a connection to the database:

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset Set cnn = CurrentProject.Connection

Then, refer to your cnn variable when you open your recordset in the form

rst.Open source, cnn, cursortype, locktype

where source identifies your data source, cursortype is one of four constants that identify the position of your cursor, and locktype is also a constant that specifies your locking preference. As a rule, you'll use adOpenKeyset and adLockOptimistic, respectively. Now, let's suppose you want to open a recordset based on a table named tblMyWork in your current .mdb file. To do so, you'd use the code

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset Set cnn = CurrentProject.Connection

rst.Open "tblMyWork", cnn, adOpenKeyset, adLockOptimistic

You can replace the cnn variable with the connection reference in the form

rst.Open "tblMyWork", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

 

MODELESS PROPERTIES WINDOW

When working in Design View, you probably open the Properties sheet to modify object properties. Once you modify a property, do you close it and then reopen it again if you need it? If so, you don't need to. The Properties window (sheet) is modeless, which means you can access other objects while it's open. In fact, leaving the window open is very convenient, because the window displays the properties of the selected object. That means that you can jump from one object to another without having to update the Properties window.

 

RECONSTRUCTION

When you copy a SQL string from the SQL window to a module window, it's usually in block form instead of being one long statement, which VBA requires. Consequently, you have to assemble all the clauses on one line, and doing so can produce an Expected Case error, which you must clear before you can continue. You can easily avoid this error by reconstructing your statement from the bottom up. In other words, if the SQL statement comprises three separate lines, don't pull the second line around to the first and then pull the third line around to the first. Instead, pull up the third line to the second, and then pull up the combined second line to the first line.

 

THE ACTIVE RECORD

When scrolling through records in Datasheet view, you're not actually changing the active record. You're just changing the areas of the table you can see. To change the active record, you must press the Down Arrow or Up Arrow key or the Page Down or Page Up key. As you might expect, the Down Arrow key selects the next record, and the Up Arrow key selects the previous record. The Page Down and Page Up keys move the screen and the selection an entire screen. In other words, if the active record is row 2 and the current screen displays 20 records, pressing Page Down would select record 21. You can also use the navigation buttons at the bottom of the table.

 

COPYING RECORDS

Often, one record will contain much of the same data as the new record you're entering. When this is the case, you have two choices. If most of the fields are the same, consider selecting the entire record and copying it to the new record, then reentering the one or two fields that are different. To select an entire record, simply select the gray row cell to the left of the record. Then, press Ctrl-C to copy the selected record to the Clipboard. Select the new record row and then press Ctrl-V to paste the contents of the Clipboard to the new record.

If only some of the cell entries are repeated, you can select adjacent cells by holding down the Shift key as you click the cells. Then, copy the selected cells to the new record. Depending on the cells you select, you may have to use the Paste Append command from the Edit menu instead of just Paste.

 

OMITTING DUPLICATE RECORDS

The Find Duplicates Wizard will locate duplicate records. However, most of the time, what we really need is a query that will display only one record when duplicates exist. When this is the case, build your query as you normally would and then set the query's Unique Values property to Yes. Here's how. First, with the query in Design view, click the Properties button or right-click the query to open the property sheet. Then set the Unique Values setting to Yes.

Doing so will eliminate records with duplicate values for all the fields in the query.

 

COUNTING THE NUMBER OF RECORDS IN A GROUP

Totals queries are good for grouping records by specific field entries and returning calculations based on just those records. For instance, you can use a Totals query to return the total sales for each salesperson.

First, create your basic query based on your sales tables. Drag the appropriate name or identification and value fields to the query design grid. Choose View, Totals, and select Group By from the name or identification field's Total cell drop-down list. Choose Sum from the value field's Total cell drop-down list. Then, run the query.

The resulting query should display one record for each salesperson and a total of all that person's sales.

Now, let's suppose you want to count the number of sales per salesperson. In that case, you'd follow these steps. First, create your basic query based on your sales tables. Drag the appropriate name or identification fields to the query design grid twice. Next, choose View, Totals. Select Group By from the first name or identification column's Total cell drop-down list. Select Count from the first name or identification column's Total cell drop-down list. Then, run the query.

This time, the query will display one row for each salesperson and the total number of sales for each.

 

PASTING NEW RECORDS

We often copy data from an existing record to a new record to save a little data entry time. If you're using the Copy and Paste commands, we can save you one more keystroke. Instead of using the Paste command, use the Paste Append command. That way, you won't have to select a field in the new record before copying the data.

Let's look at a quick example. First, select any existing record in any table. Then, press Ctrl-C to copy that record to the Clipboard. Now, if you were using the copy/paste method, you'd have to select a field in the new record and then press Ctrl-V to paste that record into the table. Instead, simply select Edit, Paste Append.

 

OPENING TO A SPECIFIC RECORD

A reader wants to know how to open a form to a particular record. By default, a form displays the first record in the underlying recordset. Perhaps the easiest method is to use the GoToRecord method in the form's Open event. The GoToRecord method uses the syntax

DoCmd.GoToRecord acForm, "formname", acGoTo, recordnumber

where acGoTo is an intrinsic constant and recordnumber is the numeric value that equals the record you want to view--in other words, the record number.

 

RETURNING THE LAST N RECORDS

Several tips ago, we showed you how to use SQL to return the top n records in your query result. Basically, the SQL method is the same as using the query's Top property. However, there's no corresponding Bottom property to return the last n records in a query.

The trick is to use the Top property and sort the records in descending order. As a result, the records that are generally last are now sorted to the top of the query's results--what was last is now first and vice versa. Consequently, the Top query still returns the first records in the query result.

If you decide to use SQL to return the last records in a query, use a statement in the form

SELECT TOP n field FROM table ORDER BY table.field DESC;

 

SAVING RECORDS

Many of you use custom data entry forms to enter new data. You enter the data and then click a command button that accepts the data and shows a new blank form for the next entry. However, if you check on the record you just entered, it doesn't appear to be there.

If you're using VBA code to handle your data entry, you must remember to save the record! Simply add the following statement to your command button code:

DoCmd.RunCommand acCmdSaveRecord

Doing so will save your record immediately.

 

TRACK ALL THE RECORDS

If you need to inhibit the navigation buttons on an Access form, you can still track the total number of records in the underlying recordset. To do so, add a text box to the form and name it txtTotal. Then add the following code to your form's Current event:

Private Sub Form_Current()

Me.RecordsetClone.MoveLast

Me![txtTotal] = Me.RecordsetClone.RecordCount

End Sub

 

DEFAULT RELATIONS

There's not much you can do about your human relatives, but you have complete control over the relationships in your Access databases. By default, Access creates a relationship between two tables in a query, if the two tables contain a common field of the same name and data type, and at least one of the fields is a primary key field.

If you don't like this arrangement, turn off this feature by choosing Tools, Options, then clicking the Tables/Queries tab and deselecting the Enable AutoJoin option.

 

DISOWNING YOUR RELATIONS

Access automatically creates a relationship between two tables with AutoJoin if the following two conditions are true:

  • Both tables contain the same field data type and field size.
  • One of those fields is a primary key.

Most of the time you'll appreciate this feature. But you can turn it off if necessary. Choose Tools, Options; click the Tables/Queries tab; and deselect the Enable AutoJoin option. Once you do so, you'll have to create relationships in the Relationships window.

 

PRINTING 97 RELATIONSHIPS

Access 2000 will print a report of your table relationships, which is an easy way to document those relationships. Unfortunately, if you want a similar report for an Access 97 database, you must use an add-in. Or do you?

If you still support 97 databases, you can create a relationship report using Access 2000. Simply convert the 97 database to Access 2000 and print the relationship report by choosing File, Print Relationships.

Just be sure to rename the database when you convert it--you don't want to convert your actual database! You just want a temporary conversion so you can take advantage of the Print Relationships feature in Access 2000.

 

EXTRA REPORT PAGES

Ever print a report that prints blank pages between each page or every other page? Usually this is an indication that the report is wider than the selected page size. This can happen if a control extends past the margin. When positioning and resizing controls, don't forget about that right margin. For instance, if you're working with a typical 8-inch by 11-inch sheet of paper in a portrait report and both the left and right margins are one inch, you have only six inches--from left to right--to work within. That means all of your controls must be to the left of the 6-inch ruler mark. If a control extends past that mark, Access will print an extra blank page for each page of your report.

Fortunately, this problem is usually easy to resolve. Review the page size and margins and then check all the controls at the right edge of your report. Resize or reposition any controls that extend past the report's right border.

 

VIEWING MULTIPLE REPORT PAGES

When viewing reports in Print Preview, you generally get one page. If you'd like to see more, right-click the report, choose Multiple Pages from the context menu, and then select the number of pages you want to see. For instance, if you want to see two pages side by side, you'd select the second page in the top row--the 1 X 2 Pages setting. If you want to see three pages side by side, you'd select the 1 X 3 Pages setting, which is the third page in the top row. You can also view stacked pages. Simply select the first page in the second row, the 2 X 1 Pages setting, to see one page on top of another, and so on.

 

CREATING A ONE-LINE SUBTOTAL REPORT

In our previous tip, we showed you how to add subtotals to a group report. That report displayed all the detail records for each group. Sometimes, a list of subtotals is easier to read and all you really need. Fortunately, this type of subtotal report is easy to create. Referring to our previous tip, simply follow the steps for building a subtotal report. Then, delete all the fields from the Detail section--yes, you read that right. Remember, you don't want the detail records for each group; you just want the subtotals. Trust me, it will work.

Just in case you can't access our previous tip, we'll repeat those instructions today:

  • Create your report--it needs to be a grouped report.
  • Open the group footer section and enter the appropriate subtotal function. For instance, if you're wanting to display a total of each region's sales, your function might be similar to =Sum([Sales]).
  • To create today's one-line subtotal report, delete all the fields from the Detail section and close up that section.

 

REPORTING IN ACCESS 2000

There are several ways to report data in Access 2000. You can rely on the standard report feature. You can also export a report to static HTML format. New to Access 2000 is the ability to work with report snapshots and to view those reports in the Snapshot Viewer. (Access 97 needed an add-in to view report snapshots.) Also new to Access 2000 is the Pages object--a type of interactive Web page.

Here is a brief description to help you decide which reporting option is appropriate for you:

  • To print data for distribution: Try the Standard, Snapshot, HTML, or Pages reporting options.
  • To distribute data by email: Try the Snapshot, HTML, or Pages reporting options.
  • To distribute data by browser: Try the Snapshot, HTML, or Pages reporting options.
  • For interactive data in a browser: Try the Pages reporting option.

Access 2000's new Pages object requires Internet Explorer 5 (or later).

 

BUILDING REPORTS

You can build an Access report by using one of the many report wizards, or you can choose to build your report from scratch. If you have a form already based on the same table or query, you might want to consider a quicker approach. Simply copy the form's controls to a blank report.

First, open your form in Design view and choose Select All from the Edit menu (or individually select the controls you want to copy). Select Edit, Copy or press Ctrl-C to copy the selected controls to the Clipboard. Next, select Report from the New Object button's drop-down list. In the New Report dialog box, select Design View and click OK. Then, select Edit, Paste or press Ctrl-V to paste the controls into the report.

Of course, this shortcut won't always be appropriate, but when it is, it can be a real timesaver.

 

NUMBERED REPORTS

Sometimes you need to number the records in a report, and depending on a table's AutoNumber value isn't wise. Remember, the AutoNumber values simply track the order in which you enter records. A sorted or grouped report won't be in the same order as your underlying table (most likely).

Fortunately, you can number your records in a report. To do so:

  • Add an unbound text box to the Detail section. Most likely, you'll want to position this control to the left of your record controls.
  • Enter a ControlSource property of =1 for the new text box.
  • Change the textbox control's Running Sum property to Over All if your report isn't grouped. If the report is grouped, select the Over Group property.

When Access runs your report, the new textbox control will display a series of consecutive values, beginning with the value 1.

 

RELATIONSHIP NAMES

Often, related fields share the same field name, but you might not realize that it isn't necessary. You can relate fields even if they have different names. The only requirement is that both fields contain the same type of data. For instance, you could relate two fields named CustomerID and CustomerNumber as long as their data types match. If, however, Customer ID is a Numeric field and CustomerNumber is a Text field, the relationship won't take.

 

COMPLETE LIST OF RUNCOMMAND CONSTANTS

The RunCommand method was added to Access 97 to replace the DoMenuItem method. The RunCommand method runs a built-in menu or toolbar command and uses the syntax

object.RunCommand command

where object is the application or the DoCmd object, and command represents the menu or toolbar command. Each command has a constant that you can use, and they're listed in the Help section.

To locate a complete list of RunCommand constants, open Help's Index feature and enter

Run Command Method Constants

If that doesn't work, you can still get there by taking the scenic route. Enter

Run Command

and choose Run Command Action from the third control. At the bottom of this section, click the first RunCommand link. In the resulting window, click the RunCommand Method Constants link in the second paragraph under the Remarks heading. This last link should display a complete list of VB RunCommand constants.

 

RETURNING A PERSON'S AGE

Many simple expressions are floating around that will return a person's age. We've tested them all and found them all lacking in some area. Eventually, these expressions will return an incorrect age, and you may not know it.

The best route is to go with an accurate expression, even if it's long, like the one we're about to show you. When working with age, you can use an expression in the form

Year(Now())-Year(birthdatefield) + (DateSerial(Year(Now()),

Month(birthdatefield), Day(birthdatefield))>Now())

where birthdatefield is the field that contains your birthdates. The expression may be long, but it's the only one we've found that works in all situations.

 

AN EASY SEARCH

The Filter By Form data is one of the easiest ways to search for matching data in Form View. Simply click the Filter By Form button on the Form View toolbar and Access will display a blank version of your form. At this point, you enter the data you're searching for in the appropriate control and then click the Apply Filter button on the Form View toolbar. For instance, if you're viewing records in a customer database and you want to see only those customers in the state of New York, you'd enter

New York

in the State control and then click the Apply Filter button.

Access will search the underlying (or bound) recordset and display only those records where the state field contains the entry New York. Access will also display the number of matching records at the bottom of the form along with the string "(Filtered Set)", so you can easily tell when you're working with a full set of records or just the results of a temporary filter.

Once you've viewed the matching records and you're done with the filter, simply click the Remove Filter button, which is actually the Apply Filter button. This button acts like a toggle switch--you click it to apply a filter and then you click it again to remove the filter.

If the Form View toolbar isn't available, you can choose Records, Filter, Filter By Form.

 

BROWSING A FILTERED SET

In our previous tip, we talked about using the Filter By Form feature to view records that match a specific search string. Many features available in Form View are also available with the Filter By Form form. For instance, if you're working with a single form, you can click the View button and choose Datasheet to view all the records at one time. Fortunately, this feature also gives you a quick glance at all the records in your filtered set. After specifying the search string and displaying the filtered set, simply click the View button and choose Datasheet, as you would in Form View. Access will display just those records that match your search criteria in Datasheet View.

 

SHARING INFORMATION

When you select a control, Access can display text in the status bar that reflects the purpose of the current control. This behavior provides a good opportunity to define the control or give hints on how to enter the correct data. To display such a message, simply add the desired text to the control's Status Bar Text property. This message can contain up to 255 characters, but Access displays only what fits in the space allowed--so if your status bar is cluttered, you'll need a short message.

 

OUT OF SIGHT

If you'd rather not see an empty report, you can use the No Data report property. Access fires this property when the underlying table or query contains no records to display. To keep Access from displaying the empty report, attach the following code to the report's No Data event:

Private Sub Report_NoData (Cancel As Integer)

MsgBox "There are no records to display"

Cancel = True

End Sub

 

AFTER THE SORT

You can use the Sort Ascending and Sort Descending buttons to sort records in ascending or descending order. However, if you need a more complex sort, such as sorting by more than one column, you probably want to use the Advanced Filter/Sort feature. You may or may not want to save your records in sorted order. After sorting, you can return your records to their original order by choosing Records, Remove Sort/Filter.

 

SORT BEHAVIOR

A Totals query is a special kind of query that allows you to group records by a field or a group of fields. Most often, we use a Totals query to perform calculations on groups. For instance, you might want to subtotal all the sales by region. In this case, you would create a query that contains the region and sales total fields, group by the region, and sum the sales total field.

You can create a Totals query by choosing View, Totals in the query design grid. Then, select an aggregate function in each field's Total cell. There are lots of possibilities.

One thing you want to watch for when working with a Totals query--Access sorts the result, even if you don't specify a sort order. Since Access must group the underlying records, this behavior might seem obvious, but it's easy to miss if you don't realize it's happening.

 

A CHECK FOR THE SPELL CHECKER

Finding typos when you know you ran the Spell feature? It's possible because, by default, Access will ignore words that are in uppercase letters or that contain numbers. If this is causing you a problem, you can modify the spelling option. To do so, run the Spell feature on data you know will produce the error. When Access displays the Spelling dialog box, click the Options button. Clear the Words In UPPERCASE option in the Ignore section. Then, clear the Words With Numbers option in the Ignore section. Finally, click OK and then click Cancel.

Of course, you don't have to disable both options--you can select one or the other, or both.

 

SPELLING BUTTON

I've been using Access since version 1, and I just found the Spelling button on the Table Datasheet toolbar. It's not hidden--it's right on the toolbar. Nor does it have an obscure design. It has the text ABC and a checkmark on it. What could be plainer? Simply select a cell, a field, or the entire table, then click the button. The Spell Checker feature will respond accordingly, just as if you'd selected Tools, Spelling.

 

USING SQL TO QUERY FOR THE FIRST N RECORDS

You probably know that you can use a query's Top property to return only a specific number of records, instead of all the records. For instance, after sorting all your orders by the total value, you may want to select only the top ten sales. To do so, you'd specify 10 as the query's Top property.

You can do the same thing with SQL using the TOP keyword in the form

SELECT TOP n field(s) FROM table

More than likely, you'll need to also include an ORDER BY clause. For instance, to select the top ten best sales from a table named tblSales, you might use a statement similar to the following:

SELECT TOP 10 * FROM tblSales ORDER BY TotalOrder

where TotalOrder is the name of the field with the total sales value for each order. If you don't specify an argument for ORDER BY, SQL defaults to ascending order.

When using the TOP keyword, don't confuse it with a value, as in the greatest or highest values. The keyword simply returns the first records in the query's results.

 

SQL DELETE WARNING

The SQL DELETE clause sometimes refers to a single column, but don't assume that field's value is the only one being deleted. The SQL DELETE clause deletes an entire record--it makes no difference if the clause references only a single field. For instance, the SQL statements

DELETE FROM tblMyStuff WHERE Flag = True;

and

DELETE FROM tblMyStuff.OrderID WHERE Flag = True;

both do the same thing--they both delete all the records in tblMyStuff where the Flag field contains the True value. The second statement won't delete just the value in the OrderID--it will delete the entire record.

 

AN AUTOMATIC SUBFORM

If you use the AutoForm and it adds a subform, you're probably seeing the table's subdatasheet. A subdatasheet displays related records in another table in Datasheet View (it's new to Access 2000). If you don't want the subform, you have two alternatives:

  • Modify the table's Subdatasheet Name property to [None].
  • Use the Form Wizard to create the form (instead of the AutoForm).

If you choose to adjust the table temporarily, open it in Design View and click the Properties button. Choose [None] from the Subdatasheet Name property field's drop-down list. After creating your form using the AutoForm, restore the property setting.

 

CAN'T SUM

Have you ever added a Sum function to a report's footer expecting to return a grand total of one of your fields? If you have, you found it didn't work. Instead of a total, your function returned an error. That's because the Sum function won't work in a report's footer. To work around this limitation, add your Sum function to some other section of your report and set its Visible property to No. Then, in the report's footer, add a text box and set its ControlSource to the name of the text box that contains your working Sum function. For instance, if you named the control that contains the Sum function txtTotal, you'd then add another text box to the report's footer and use the expression

=txtTotal

as that control's ControlSource property.

 

SYSTEM INFORMATION

There's a treasure chest of information available with just a few quick clicks, and you probably don't know a thing about it. From the Help menu, click About Microsoft Access. In the lower-right corner, click the System Info button to open the Microsoft System Information dialog box. Here, you'll find information about your system: hardware, components, and even the other applications you have installed. Take a look around--you may be surprised at what you'll learn about your system.

 

CREATING A TABLE ALIAS--PART 1 OF 2

A self-join relationship allows you to relate records in the same table. To do so, you simply add the same table to a query twice. However, when you do so, Access renames the second query by adding a 1 (or the appropriate consecutive value) to the title. For instance, if you add the table tblMyTable to a query twice, Access will rename the second table tblMyTable_1. You can accept this default name or change it.

To change the default name, simply right-click the field list in question. Then, select Properties from the context menu and enter a temporary name in the Alias field.

An alias helps you differentiate between tables in the query. However, assigning an alias doesn't modify the name of the underlying table.

CREATING A TABLE ALIAS--PART 2 OF 2

In our previous tip, we discussed assigning an alias to a table in a query. You might want to do so when working with copies of the same table in a self-join.

The Alias property also comes into good use when your tables have long names. That's because Access won't display the entire title in the field list's title bar. Assigning a short alias in the query makes each table easier to find.

 

CREATING A TABLE IN DATASHEET VIEW

You probably know that you can create a new table in Datasheet view by double-clicking the Create Table By Entering Data shortcut in the Database window. This approach will open a blank datasheet with ten generic fields. As you enter data, Access will do its best to determine the appropriate data type for each field.

You can help Access choose the correct data type by entering descriptive characters. For instance, if you want a field to be a Currency data type, be sure to preface your entry with your system's Currency symbol, such as the $ character. When entering percentage values, be sure to include the % symbol, and Access will assign the Number data type and Percent format. The more information you enter with your data, the more complete your table will be.

 

LISTING TABLE FIELDS

You can use VBA to create a list of fields, but if all you need is a list, you might consider using the Documenter. It's certainly a lot quicker and easier. Granted, the Documenter will also print a lot of other information, but you can limit most extraneous information.

To use the Documenter, select Tools, Analyze, Documenter. In the resulting dialog box, click the Tables tab, and then select the appropriate table. To limit the information that this feature prints about the table, click the Options button and choose the Names, Data Types, and Sizes options in the Include For Fields section. Then, click OK twice. Access will examine the specified table and create a report that lists that table's fields, as well as each field's data type and size.

 

ORDER IN THE TABLE

For the most part, the order of your table fields is irrelevant, because you can display data in any order you want. However, it's a good idea to position your fields in the same order you plan to arrange your data entry forms. If you use a form wizard to create your form, that wizard will rely on the field order to add controls. If your field order matches your data entry order, you won't have to rearrange anything.

A form wizard doesn't have this problem. If you use a form wizard instead of an auto wizard, you determine the order of your controls. Simply add fields in the order you want them to appear in your form.

 

CLEANING UP YOUR TABLES

You won't often want to delete a table, but it's an easy process when you do. Simply select the table in the Database window and press the Delete key, then click Yes when Access wants you to confirm the action. If you want to omit this action, press Shift-Delete, but be very careful when using this shortcut. In fact, always think twice before deleting a table full of data. You might want to archive the data instead of deleting it. However, if you do delete a table and change your mind, you can undo the delete by clicking the Undo button or choosing Undo from the Edit menu. But you must do so immediately after deleting the file--before you take any further action.

 

HIDING A TABLE

It's easy to hide a table so the average user doesn't know it exists. Right-click the table in the Database window, then choose Properties from the submenu. In the resulting Properties dialog box, click the Hidden option at the bottom of the box. Next, click Apply and then click OK.

When you're ready to work with the table, choose Tools, Options and click the View tab. Then, select Hidden Objects from the Show options and click Apply. Finally, click OK to return to the Database window, which will now display your hidden table.

 

CAN'T MODIFY TABLE

Most of the time, it's easy to modify a table. You open it in Design view, then make your changes and save them. If Access won't let you modify a table, there are a few possibilities. First, make sure the table's not open by pulling down the Window menu and checking the active objects. (VBA code could also be the problem. We discussed this possibility in an earlier tip named BOUND BEHAVIOR.)

If your table's not open, and you're working with a networked copy, you may have to wait until no one else is working in the same database. To make sure of this, choose Tools, Options and then click the Advanced tab. If the Shared button in the Default Open Mode section is selected, you must be the only one working in the open database. In other words, everyone else must close the appropriate .mdb file before you can make changes to a table.

When the Shared button isn't selected, you'll need to contact your network administrator. Chances are you don't have permission to modify tables.

 

QUICK TABLES

If you're running out of time and you need a new table, you can bypass Design View, go straight to an empty table, and start entering data. To do so, in the Database window click the Tables tab and then click New. Then, click Datasheet in the New Table dialog box.

Access will open a blank table with default field names. You can start entering your data right away. Later you'll probably need to go back and name fields and tweak field properties. But in a pinch, it's a real time-saver.

 

AN UNRELATED TABLE

Most multiple-table queries are based on related tables. If not, Access will create what's known as a cross, or Cartesian, product. That means Access joins every record in every table. For instance, if you add a ten-record table and a 20-record table, your query will have 200 records. It's not often that you'll want this kind of datasheet, but you might.

Let's suppose you want to match each of your products with every storage medium in your warehouse to learn the least expensive way to store your inventory. To do so, you'd join your product and storage medium tables without creating a relationship. The product would be a record for every product in every medium. At this point, you could compare all the mediums for each product to determine which is the least expensive. Of course, there'd be more calculations going on, but this simple example gives the gist of the arrangement.

 

EXPORTING TABLES

When you export a table, Access exports the records in the order you entered those records. Sorting the table makes no difference. If you need to export records in a specific order other than the table order, you must create a query to do your sorting. Then, export the query, not the table.

 

CONVERT TEXT TO NUMBERS

If you save numeric-based data as text when you don't need the entries for calculations, you can still use the data in calculations if necessary. Simply use the Val() function to return the numerical value of the data. You can use a query expression or a calculated control in a form or report to display the results.

However, the Val() function considers values from left to right only and stops when it encounters an alpha character. That means the Val() function won't return a value for entries that begin with alpha characters. In other words, the two entries 123abc and abc123 respond differently to the Val() function. 123abc returns the value 123, and abc123 returns nothing.

 

TEXT VERSUS MEMO

Technically, a Memo field stores text and accepts more characters than a Text field, so you might wonder just why you'd need to bother with a Text field. There are a few distinctions between the two, and knowing them will help you choose the most appropriate data type.

A Text field accepts up to 255 characters, including letters, numbers, and any punctuation. A Memo field can hold up to 64,000 characters. You can limit the size of a Text field using the Field Size property; you can't limit the size of a Memo field. Text fields accept formatting, but Memo fields don't.

Therefore, you'll want to rely on Text fields when the entry is relatively small (fewer than 255 characters) or you want to limit or format the data. When the entry is larger than 255 characters, use a Memo field.

 

WORKING WITH ELAPSED TIME

One of the questions we see most often is how to track time when the time period begins in one day and extends into the next (past midnight). Fortunately, there's an easy way. But first, let's look at a simple expression that tracks time within the same 24-hour period (midnight to midnight). If you know that the time period will be inclusive of the same day, you can use an expression in the form

end - start

where end marks the ending of the time period and start marks the beginning of the time period.

Unfortunately, this expression doesn't work with elapsed time. In these cases, you'll need the expression

1 + end - start

 

PROTECTING TOOLBARS AND MENU BARS

You can easily change a toolbar by choosing View, Toolbars, Customize. When you do, Access opens all toolbars and menu bars in edit mode. While this makes it easy to modify a toolbar, it also makes accidents more likely--if you're not careful. With all the toolbars and menu bars in edit mode, you could easily make a change to the wrong toolbar without realizing it at the time. For this reason, we recommend you close all toolbars and menu bars, except the one you mean to change.

To close a toolbar or menu bar, select View, Toolbars, Customize. In the Customize dialog box, click the Toolbars tab and deselect the toolbar or menu bar in the Toolbars control.

 

RESETTING TOOLBARS

If you make changes to the built-in toolbars, you can always reset them to their default condition. To do so, right-click the toolbar and choose Customize from the resulting shortcut menu. Next, click the Reset button in the Customize dialog box. Just keep in mind that you're not deleting only the most recent change--you're returning the toolbar to its original settings when you installed Access (or Office).

 

SUBMENUS AS TOOLBARS

Did you know that some submenus could double as toolbars? They can, and this capability is especially convenient if you find yourself using one of these tools repeatedly because you'll omit a few keystrokes.

For instance, let's suppose you use the Font Color tool often. You click the arrow to the right of the Font Color button and choose a color--each time you use the tool, you must open it first. You might find working with this tool as a floating toolbar a bit easier.

To do so, open the control's drop-down list and position the cursor over the horizontal gray bar at the top of the list. In response, the bar will turn blue, which means you can drag this control to a floating position. (This behavior is true of any tool that can double as a floating toolbar.) Simply click the blue bar and drag the control to a position off the toolbar. To close the new toolbar, click the Windows Close button (the X button in the top-right corner).

 

DOCKING THE TOOLBOX

The Toolbox is actually a toolbar. As such, you can dock it or move it the same way you do any other toolbar. If you prefer a floating Toolbox, you can still move it by clicking its title bar and dragging the Toolbox to a new position.

If you decide to dock the Toolbox, simply double-click the title bar, and Access will dock it below the other docked toolbars. To undock the Toolbox, grab the handle at the toolbar's left border and then drag the Toolbox back to the Access window.

 

CAN'T USE TOP VALUES WITH DELETE

You might not realize it, but you can't use the Top Values property in a Delete query. For instance, let's suppose you wanted to delete 10 percent of your records. You might try setting a Delete query's Top Values property to 10%, but you'd find the Top Values property missing from the property sheet.

A reader has a solution to this limitation. Create a Select query using the Top Values property. Then, base the Delete query on the Top Values Select query. When you run the Delete query, it will delete the appropriate number of records from the Select query's underlying table or data source.

 

A TOTAL ERROR

Ever have a Totals query return the error

Cannot group on fields selected with '*"

If so, the query's Output All Fields property is probably set to Yes--and that just won't work. You see, a Totals query groups the fields by entries, so you need to be careful about the fields you add to a Totals query. Each additional field will increase the number of "groups." When the query's Output All Fields property is set to Yes, then your query is considering all the fields in all the underlying data sources. The effect would be a query that's not grouped at all, since each field is a "group."

Fortunately, the solution is simple. Open the query's properties sheet by clicking the Properties button and set the Output All Fields property to No.

 

CAN'T UNDO

You're probably familiar with the Undo button. In fact, we've written a few tips about this feature, which allows you to undo many tasks. Unlike some of the other Office programs, Access lets you undo some tasks after saving a table. For instance, try opening any table and changing the contents of any cell. Then, save the table. Notice that the Undo button is still enabled. If you click it, Access will undo your change, even though you've saved the table.

 

FASTER UPDATE

You can use a combo box to update other form controls. For instance, if you choose an employee by name from a combo box, you might then use a DLookup() function to return that employee's address and phone number. If so, there's a quicker way. If all the data you want to display is stored in the same record, try using the Column property in the form

ctrl.Column(x)

where ctrl is a reference to the combo box and x identifies the position of the corresponding field in the combo box. Just remember to add all the necessary fields to the combo box when you're creating it. In addition, x is 0 based, so the first field in the combo box equals 0, the second equals 1, and so on.

 

WORKING IN THE VISUAL BASIC EDITOR

Sometimes when you're changing properties using VBA code, the setting isn't obvious. Fortunately, the Visual Basic Editor displays the active object's properties in the lower-left corner. When you need to know a setting, return to the form or report in Design view and apply that setting. Then, return to the Visual Basic Editor and view the modified object's properties in the properties window. This method is particularly useful when you don't know the correct syntax for the property.

Let's consider a simple example. Suppose you want to display a control's text in bold print and you need to know the integer value that represents the bold setting. (We recommend using intrinsic constants when available.) First, you click the View Microsoft Access button on the editor's Standard toolbar. Then, you change the control's Font Weight property to Bold in the property sheet. When you return to the Visual Basic Editor, review the properties in the property window for the FontWeight property setting, which should be 700. Now you know the integer value, and you can complete your code statement. Just don't forget to return to the form and change the object's property back to its original setting.

 

TURNING OFF WARNINGS

When you run an action query, Access will display a message warning you that you are about to modify the existing data. If you don't want to display these warnings, you can temporarily turn them off using the SetWarnings method. The statement

DoCmd.SetWarnings False

turns off the display. Substitute the False value with True to turn on the display.

However, we recommend that you not add the SetWarnings statement until the procedure is complete, debugged, and running as expected. During the testing and debugging process, those messages can give clues as to whether the code is running as expected.

 

RESIZING THE WEB BROWSER CONTROL

In our previous tip, we told you about the Microsoft Web Browser control, which you may have trouble resizing. Thanks to a reader for sharing his innovative workaround for this apparent bug. If you can't resize your Web Browser control, select it and press Ctrl-X to cut the control from the form. Next, press Ctrl-V to copy it back to the form. Now, resize your control and save your form.

No one seems to understand why this simple cut-and-paste task does the trick, but it definitely does. We've been wanting to share the Microsoft Web Browser with our readers for months but didn't because we couldn't resize the darn thing. Thanks!

 

FORCING A WEEKDAY

Limiting the type of data you enter doesn't always require a complex event procedure. Sometimes, the Validation Rule property is one way to limit the type of data you enter. For instance, if you want a control to accept only weekday values, you can enter the expression

DatePart("w",[field]) Mod 7 >= 2

as the control's Validation Rule property.

Saturday and Sunday will return the integers 0 and 1, respectively. Consequently, checking for a value that's greater than or equal to 2 eliminates any entry that equals 0 or 1. Therefore, the control won't accept any date that falls on a Saturday or Sunday.

 

AN IMMEDIATE WINDOW SHORTCUT

When you want to run an expression, function, or variable in the Debug window, you don't have to retype it. You can drag it from its module to the Debug window and then run it. You may need to revamp it just a bit by adding a ? character to the beginning of the statement or adding a passed argument. But you'll save yourself a little time and cut down on typos.

 

ORDER IN THE WIZARD

When working with wizards, you generally select fields from an Available list box and move them to a Selected list box. The wizard then uses those fields to create a form, report, or query--depending on the type of wizard you're running.

You may have noticed that the wizard displays the fields in the same order as they occur in the underlying table or query. If this order doesn't match the order in which you want these fields to appear in the finished object, you're not stuck. You don't have to rearrange all your controls after the fact.

The order in which you select fields in the Available list determines the order in which the wizard displays them in the resulting form, report, or query. Simply select the fields in the Available Fields list box in the order you want for your finished form or report.

 

CREATING A SUBTOTAL REPORT

Typically, grouped reports beg for subtotals, but the report wizard won't just give them to you--you'll have to work for them. Here's how:

  • Create your report--it needs to be a grouped report.
  • Open the group footer section and enter the appropriate subtotal function. For instance, if you're wanting to display a total of each region's sales, your function might be similar to =Sum([Sales]).

The results will be an additional line below each group displaying the result of your function. That result will reflect only the records in its group.

 

SEE YOUR REPORT IN WORD

At some time or another, you'll probably want to export a report to Word so you can take advantage of Word's formatting capabilities. Fortunately, doing so is easy: Open your report or select it in the Database window. Then, locate the Office Links tool on the Database toolbar and open its drop-down list. Finally, choose Publish It With MS Word from the drop-down list.

Access will open Word along with an RTF file of your report. At this point, you can work with your report as you would any other Word document.

 

ZOOMING IN

Quite often, you can't see an entire expression or function in a property field. When this happens, press Shift-F2 to open the expression or function in the Zoom window--a large dialog box that will more than accommodate your expression or function. So the next time you're working with a long expression, remember the Zoom window!

 

ZOOMING IN

When entering expressions in the query design grid, you can press Shift-F2 to open the Zoom dialog box. Doing so allows you to see the entire expression as you enter it or while modifying an existing expression. If you prefer the mouse, you can still zoom in on your expressions without using the keyboard. Simply right-click the cell that contains the expression (or will contain the expression) and choose Zoom from the context menu.

 

LEADING ZEROS

There are several solutions for adding leading zeros to a value, and most of them are more convoluted than they need to be. One of the simplest methods for adding leading zeros that we've found isn't all that intuitive, but it's simple and it works. In a nutshell, you add the value to a 10-based number that's one place larger than the number of characters you need for each entry. For instance, if you want all values to have five characters, using leading zeros to fill in as needed, you'd use the number 100000--that's one place more than five. The function

Right(value + 100000, 5)

will return five characters from the result of adding value to 100000--including leading zeros. If value equals 30, the function will return 00030; a value of 4321 will return 04321, and so on.

Send mail to webmaster at markpettersen.com (insert an "at" symbol) with questions or comments about this web site, but not for technical support.
Copyright 2001-2020 Pettersen's Computers
Last modified: April 27, 2020