Computer Tips

 
Visit us often.  Computer tips updated daily.  Click here to--> "Tell a friend" so they can get updated computer tips, too.  Please visit our clients, as they support the computer tips page.

If you would like to submit a tip send us an email with your tip to info@businesswebsitelinks.com.
  ______________________________________________________________

Alternate page number placement on reports (2002/2003)

• Although you may typically print out single-sided reports, situations may call for a report printed on both sides of the page. When your reports must support duplex printing, you may want to alternate the position of the page number indicators to create a more professional, book-like appearance. For instance, you may want the page number to appear on the left side of the footer on an odd-numbered page, but have it appear on the right side of the page when the page number is even. Fortunately, you don't have to go to a lot of work to set up such a page numbering scheme.
• With your report open in Design view, choose Insert | Page Numbers from the menu bar. Then, choose whether the number should appear in the header or footer using the appropriate Position option. Finally, select either Inside or Outside from the Alignment dropdown list and click OK. The Inside setting prints odd page numbers on the left side of the page and even numbers on the right. Selecting the Outside setting produces the opposite result, with odd page numbers appearing on the right and even numbers on the left.

Easily select from a list of options with the Choose function (2000/2002/2003)

• If you need to select from a list of values, don’t bother creating an array to hold the values or writing a lengthy conditional statement. Instead, use the VBA Choose() function. This function selects and returns a value from a list of arguments based on an integer value. It works just like an array, without the array. The Choose() function conforms to the following syntax:
  • Choose(index, choice-1[, choice-2, ... [, choice-n]])
• The following code uses the Choose() function to randomly select a quote from a list:
  • ? choose(Int((3 * Rnd) + 1), _
    "Dig the well before you are thirsty.", _
    "Do good, reap good; do evil, reap evil.", _
    "Don't open a shop unless you like to smile.")
• The Choose() function returns Null if you pass in an integer value less than one or greater than the total number of arguments.

Add custom sounds to your application with the sndPlaySoundA() API (2002/2003)

• The DoCmd property supports a Beep method that you can use to signal an error or other status. However, if you desire a more unique sound, such as a ringing sound for an alarm, DoCmd won't help you. One way to play sounds in an Access application is to use the sndPlaySoundA() API function. This function is defined in the winmm.dll file included with Windows and conforms to the following syntax:
  • Public Declare Function PlaySound _
    Lib "WINMM.DLL" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, _
    ByVal uFlags As Long) As Long
• Following is an example of a call that plays a clapping sound on our system:
  • result = PlaySound("applause.wav", &H1 Or &H8)
• The next call stops the clapping sound:
  • result = PlaySound(0&, &H1)
• The values we pass in the uFlags parameter are constants that determine how Windows will play the sound. The valid flags are:
  • -> &H0: Pauses the application until the sound finishes playing.
  • -> &H1: Plays the sound asynchronously in the background while your application executes.
  • -> &H8: Plays a sound continuously in a loop. This flag must be used with the asynchronous flat. To stop the play, call the API again with 0& as the first argument.
  • -> &H10: Instructs Windows to wait until a currently playing sound ends before playing a new sound.
  • -> &H2: Stops the default sound from playing when you attempt to play a file that doesn’t exist.

Change the insides of copied databases to head off confusion (2002/2003)

• One of the things Access users like about database files is you can make a copy of the MDB just by using the Windows copy and paste functionality. However, besides giving the database a new name on the outside, you should take a moment to make the following internal changes as well:
  • 1. Database Properties— Select File | Database Properties and click the Summary tab. Then change the Title and any other pertinent information.
  • 2. Startup— Change the Application Title, Icon, and Startup information to values appropriate for the copy.
  • 3. Project— Open the code window and select View | Project Explorer to open the project pane. Then, click the project name and change it to match the file name.
  • 4. Cleanup— Remove any unnecessary objects to reduce clutter.
• If you’re creating a copy of your database for backup purposes, it’s probably okay to leave these items alone. However, if you’re making a copy to reuse for another project or pass on to someone else, these are quick changes that will help both you and the recipient.

Disable the form Shortcut menu to discourage deliberate or accidental changes (2002/2003/2007)

• Most Access developers know that they can change views and modify certain form properties from the shortcut menu Access provides when they right-click on a form. Of course, most Access users know this as well. While you can’t completely stop users from switching views or changing property values without using code, you can quietly let them know that you’d rather they not do so by simply taking away their ability to bring up the shortcut menu.
• To disable the shortcut menu for a form, simply open the Properties pane, locate the Shortcut Menu property, set it to No, and then close the Properties pane. Now, when you open the form in Form view and right-click on it, the shortcut menu will not display. Keep in mind that this also prevents the shortcut menu for text boxes from displaying.

Elevate your applications with a Windows-like drag-and-drop feature

• Although drag-and-drop functionality impresses users and saves them time, you could end up wasting a lot of your own time emulating this feature using standard Access controls. Luckily, with the Microsoft ListView Control, you can get drag-and-drop up and running in no time.
• To help you drag your application into the big leagues, we'll:
  • Explain each step of the ListView drag-and-drop operation.
  • Create a sample application that you can use as a drag-and-drop model.
  • Show you how to upgrade the sample application to include support for dragging icons.
  • Read More About This Tip Click Here > MS Access Tips

It’s OK to think outside the list box—use a ListView to clarify or liven up list values

• Lists can be so drab--there isn't much you can do to customize a list box control beyond changing the font or back color. Make your applications truly user-friendly with a dazzling list display, and a more feature-rich control: the ListView.
• To help you liven up boring list displays, we'll:
  • Reveal several important rules you need to follow when working with the ListView control.
  • Show you some of the great ways you can dress up data lists in a ListView.
  • Discuss the basic syntax for adding and removing items from a ListView.
  • Read More: MS Access Tips

No wizards, no code, no fuss: Copy Excel data and paste it into an Access database (2007)

• If you’re constantly importing data into Access from Excel, Microsoft Access 2007 has a real treat for you. You can now move data from one application to the other using only the Clipboard!
• To import Excel data into Access:
  • 1. Open your Excel file and select the data (with the headings) that you want to export.
  • 2. Copy the data to the Clipboard.
  • 3. Open an Access 2007 database and create a new table in the Datasheet view.
  • 4. Right-click on a cell in the upper right portion of the Datasheet and select Paste from the shortcut menu.
• When you do this, Access adds all the data to the new table. It even initially assigns each column a Data Type that fits with each field’s contents. Although Access creates an Auto Number field during the import, you can delete this column if you don’t need it. However, all you really need to do is modify the column names and save the new table.

Quickly export reports as PDFs to preserve quality while expanding your reach

• Preserving your reports as PDFs is an excellent way to make them universally acceptable without sacrificing quality. However, generally, you could only create them by downloading third party tools or purchasing costly software. We'll show you the Access 2007 alternative that's free, quick to install, and easy to use.
• To easily export report files to the PDF format, we'll:
  • Show you what you'll need to do to begin creating these high-quality, exchangeable files.
  • Create a sample application that allows you to select any available report in your system.
  • Provide the single line of code you'll need to save almost any report as a PDF.
  • Read More: MS Access Tips

Create custom menu items to perform your favorite tasks

• You don't need to waste time manually running code or creating artless forms just to execute custom functions. We'll show you how to tie custom functions to menu items in 5 simple steps for easy, hassle-free execution.
• For an efficient way to execute custom functions, we'll:
  • Show you how to build a single-line macro to execute a function.
  • Easily create and populate a toolbar menu.
  • Link menu options to macros to execute your custom functions any time you want.
• After you create a function to perform a custom import or send an email, how do you initiate it? If you only need to occasionally run it, you can type the function name in the Immediate window and press Enter or create a form that executes the function when you open it or click a button. However, for functions you run more frequently, a better alternative is to place them on a menu where you can execute them as often as you like. We’ll show you how.
  Taking tasks to the menu
  The ability to create custom menus is a great Access feature. Typically, you might create a custom menu to organize groups of tasks that you perform often. For example, if your job requires you to run the Import Wizard daily and execute a particular report, you might add buttons to perform these tasks to a menu so you can complete them without digging through different menus. If you follow the next five steps, you can do the same for your custom functions.
  Step 1: Create custom functions
  Most likely you’ve already completed this step if you’re looking to execute your functions from a menu. However, for testing purposes, we created a module named Test Menu in our database and added the three highly important functions shown in Figure A. You can add these functions to your database or simply use whatever functions you’ve already created.
  Figure A: Create and perfect the functions you want to assign to menu items.

  
  Step 2: Create execution macros
  This step defines the actual action Access will perform when you click a menu item. Now, if creating macros isn’t your thing, don’t worry. The macro you need to execute a function is super simple. First, switch to Macros view and then complete the following steps.
  To create an execution macro:
  1.

  Click the New button.
  2.

  Click in the Action field and select RunCode from the dropdown menu.
  3.

  In the Function Name field, enter your custom function name or use the Expression Builder to select the function.
  4.

  Save the macro and close the window.
  5.

  Repeat steps 1 – 4 for each function you want to execute from the menu.
  We created the three macros shown in Figure B.
  Figure B: Create a simple, one-line macro to execute your custom function.

  
  Step 3: Create the new menu
  The third step is where you actually create the custom menu. You can do this by selecting Tools | Customize from the Access menu to open the Customize dialog box. Then, click the New button and enter a name for your menu. We used the name Ribbon as shown in Figure C.
  Figure C: Use the Customize menu option to create new toolbar menus.

  
  Step 4: Add menu options
  After you complete step 3, Access adds the new menu to the Toolbars list, selects it, and displays the detached menu in the Access window. Now, you need to add some buttons to the menu that you can click. To do this, click on the Commands tab of the Customize dialog box, click File in the Categories list, and drag the custom option to the new menu, as illustrated in Figure D. Do this until the menu contains enough buttons to execute all your functions.
  Figure D: Drag and drop a command on the new menu for each function you want to execute from the menu.

  
  Step 5: Set the menu button properties
  You’re almost done. Now, you need to define a behavior for each menu button. To begin, right-click on one of the menu buttons and select Properties from the shortcut menu. Then enter a Caption and use the On Action combo box to select one of the macros you created in step 2, as we’ve done in Figure E. To complete the menu, select the remaining buttons with the Selected Control combo box and set their Caption and On Action properties accordingly. Now close the Customize window.
  Figure E: Assign the appropriate macro to each menu button.

  
  Test the new menu
  That’s it! Your menu is complete. You can leave it floating in your database window or dock it to the Access toolbar. When you click one of the buttons, Access will execute the function you linked to the button, as we demonstrate in Figure F.
  Figure F: After you create the menu, you can execute your custom functions with a click of your mouse.

  

   

Tip: Prevent accidental or intentional design changes by disabling menu access (2002/2003)

• Most Access developers know that they can change views and modify certain form properties from the shortcut menu Access provides when they right-click on a form. Of course, most Access users know this as well. While you can’t completely stop users from switching views or changing property values without code, you can quietly let them know that you’d rather they not do so by simply taking away their ability to bring up the shortcut menu.
• To disable the shortcut menu for a form, simply open the Properties pane, locate the Shortcut Menu property, and set it to No. Now, when you open the form in Form view and right-click on it, the shortcut menu will not display. Keep in mind that this also prevents the shortcut menu for text boxes from displaying.
• For a more direct approach, you can use code to disable the Design View option on the shortcut and main menus. First, add the following function to a module in your database.
  • Function DesignChanges(allow As Boolean)
    Application.CommandBars("Form View Popup"). _
    Controls("Form Design").Enabled = allow
    Application.CommandBars("Menu Bar"). _
    Controls("View").Controls("Design View"). _
    Enabled = allow
    End Function
• Then, call the function passing in False to disable the option and True to enable it. Keep in mind that when you disable these options, they remain off for the entire Access environment until you turn them back on.

Tip: Determine a form’s current view state with the CurrentView property (2000/2002/2003)

• In Access, you can open a form in Design, Datasheet, or Form view. Unfortunately, many of the form-modifying tasks that you accomplish using code return errors if the form is open in Design View. Fortunately, it’s easy to determine a form’s current view state by checking its CurrentView property. This property returns a value of 0 if the form is open in design view (1 for Form and 2 for Datasheet). As a result, you could add the following function to a module in your database and use it to retrieve the current state of any form:
  • Function formview(frmname) As Integer
    formview = Forms(frmname).CurrentView
    End Function

Tip: Reduce typographical errors and make coding easier with user-defined types (2000/2002/2003)

• Most of the syntax errors that creep into applications are the result of misspelled variables and missing or misused argument values. One way you can prevent these types of errors is to create a User Defined Type for the functions and methods you frequently use. Following is a sample UDT that stores business information:
  • Private Type CompanyInfo
  • Name As String
  • CEO As String
  • EmployeeCount As Integer
  • TotalSales As Integer
  • End Typ
• Three ways a UDT can help you create error-free code include:
  • IntelliSense help.
    • When you use a UDT, Access provides a pop-up window with all the available members. You’re less likely to make a coding error if you don’t have to remember the correct spelling of each variable.
  • Compartmentalization.
    • All the variables you need are in one neat package. This means you can pass one CompanyInfo object in your argument list instead of four individual variables. You’ll never have to worry about the order of the argument list or if you’re missing one of the pieces of data.
  • Improved Flexibility.
    • When you use a UDT, you also don’t have to worry so much about changes to argument lists. So, if you needed to add an InceptionDate field to the member list or change TotalSales from an Integer to a Long, you could do so without modifying existing argument lists.

Tip: The spring forward happens a bit sooner this year—is your system ready?

• In the United States, Daylight Saving Time (DST) will be extended by approximately four weeks this year. As a result, DST will start three weeks earlier on March 11, 2007, and end one week later on November 4, 2007. If your system is currently set to automatically adjust for DST or you have applications that calculate elapsed time, then you should take action now. Microsoft has compiled a list of its products that will be affected by this change. They include
  • Microsoft Windows
  • Microsoft Windows SharePoint Services
  • Microsoft Exchange Server
  • Microsoft Office Outlook
  • Microsoft SQL Server Notification Services
• For a complete list of all affected products and what you will have to do to make sure your systems respond appropriately, point your browser to http://support.microsoft.com/gp/cp_dst.

Tip: Check the contents of arrays and collections without adding unnecessary code (2002/2003)

• A great debugging feature you may have never used is the ability to execute entire blocks of code from the Immediate window. You've probably often used this window to execute a single function or preview a single value. However, what if you’re in the middle of a test and need to view the contents of an array or collection? In the past, you'd probably stop your code, add the necessary For loop, and re-execute the code. However, next time you’re faced with this dilemma, try typing code similar to the following right into the Immediate window:
  • For Each x In Application.CommandBars: Debug.Print _
  • x.Name: Next
• This particular piece of code iterates the Access CommandBars collection and displays the contents in the Immediate window.
• The one thing you need to keep in mind when using this technique is to type all the code on one line or avoid pressing the [Enter] key on any line but the last. Otherwise, Access tries to process the partial code.

Tip: Create an export specification for greater flexibility when exporting to text files with TransferText() (2002/2003)

• Access relies solely on default specifications when exporting data to a text file using TransferText. However, if you have specific formatting needs beyond the defaults and you know you’ll be exporting the same data quite often, you can provide more precise formatting instructions by creating an export specification just for that table.
• To create an export specification:
  • 1. Open your database, right-click on a database table, and select Export from the shortcut menu.
  • 2. Select Text Files from the Save As Type dropdown box and click Export.
  • 3. Click the Advanced button on the Export Text Wizard page.
  • 4. Use the Export Specification page to record your requirements, for example you can change the Field Delimiter to a semicolon (;) and the Text Qualifier to {None}.
  • 5. Click the Save As button and click OK to save the specification (make note of the name)
  • 6. Click Cancel to exit from the Export Wizard.
• To test the new specification, pass the export specification file name to the TransferText() method, like so:
  • DoCmd.TransferText acExportDelim, _
  • "Your Export Specification", _
  • tablename,textfilename

Tip: 7 considerations to help you choose the right tools for storing and displaying data

• Office users often ask if they should use Access or Excel to manage their data. The answer to this question depends on your needs. To ensure that you’re using the right tools for your data needs, it helps to consider some examples of various tasks you might want to perform. Here’s a sample list:
  • To quickly display sophisticated charts, produce custom charts, or apply significant formatting to data, use Microsoft Excel and automate the process with VBA.
  • To easily display standard, tabular reports of data that change over time, use a report in Microsoft Access.
  • To maintain data over time but still leverage some Excel features, store the data in Access, and either automate Excel through VBA in Access, or link to the external Access data from Excel. To decide, consider whether you want users to be working directly with spreadsheets or with forms you create in Access.
  • If many people need to share the data, or you’re dealing with gigabytes of data, you’re best served by a more high-end database program, such as Microsoft SQL Server or Oracle—or, for smaller budgets, Microsoft Visual FoxPro.
  • To gain maximum control of formatting and ensure consistent results across different printers, consider automatically pulling the data from Access into a page-layout program, such as Adobe PageMaker.
  • To create a standalone data-reporting tool that you intend to distribute to other Microsoft Windows users, consider writing it in Visual Basic .NET and leveraging a reporting utility, such as Business Objects SA Crystal Reports.
  • To perform complex mathematical or statistical operations on data, consider using a mathematics package, such as Wolfram’s Mathematica, which can connect to a database via JDBC drivers. Or, for statistics, consider programs such as SPSS or SAS, which can import data from Access.

Tip: Reduce typographical errors and make coding easier with user-defined types (2000/2002/2003)

• Most of the syntax errors that creep into applications are the result of misspelled variables and missing or misused argument values. One way you can prevent these types of errors is to create a User Defined Type for the functions and methods you frequently use. Following is a sample UDT that stores business information:
  • Private Type CompanyInfo
  • Name As String
  • CEO As String
  • EmployeeCount As Integer
  • TotalSales As Integer
  • End Type
• Three ways a UDT can help you create error-free code include:
  • -> IntelliSense help: When you use a UDT, Access provides a pop-up window with all the available members, as shown in Figure A. You’re less likely to make a coding error if you don’t have to remember the correct spelling of each variable.
  • -> Compartmentalization: All the variables you need are in one neat package. This means you can pass one CompanyInfo object in your argument list instead of four individual variables. You’ll never have to worry about the order of the argument list or if you’re missing one of the pieces of data.
  • -> Improved Flexibility: When you use a UDT, you don’t have to worry as much about changes to argument lists. For example, if you have a function that accepts our example UDT as an argument, you can add or remove UDT members without having to change the function's arguments or changing the argument list of each and every call to that function.

Tip: Avoid errors by selecting the right function to round decimal numbers (2002/2003)

• Access offers many options to retrieve the integer portion of a decimal number. You need to make sure you’re using the right one to avoid undesirable results, as some round up and others truncate.
• If your decimal number is a positive number, you can use the Int() or the Fix() function to truncate the decimal portion of the number. For example, the next two statements return 3 in the Access Immediate window:
  • ?Fix(3.5)
  • 3
  • ?Int(3.5)
  • 3
• If you want to round the number instead of truncate, you can use the Round() function or the CInt() function, like so:
  • ?Round(3.5)
  • 4
  • ?CInt(3.5)
  • 4
• If your number is negative, Fix() truncates, while Round(),Int(), and CInt() round down to the lowest whole number:
  • ?Round(-3.5)
  • -4
  • ?Fix(-3.5)
  • -3
• You should also be careful not to double-process your decimal numbers. For example, we get two different values when we round 3.4999 and when we format the value first and then round it:
  • ?Round(3.4999)
  • 3
  • ?Round(Format(3.4999,"Fixed"))
  • 4

Tip: Create standalone software packages with the Microsoft Access Runtime

• Did you know that you can package your Access application as a standalone software application that clients can use even if they don’t have Access installed? You can do this with the royalty-free Microsoft Access Runtime. The Access Runtime is available as part of Microsoft Office Access 2003, but you can’t use it to distribute applications without the license. The name of the package containing the license has changed over the years. As of this writing, what you need is Access 2003 Developer Extensions, which is part of Visual Studio 2005 Tools for the Microsoft Office System. Previous packages that include the license are:
  • Microsoft Office Developer Edition 97
  • Microsoft Office Developer Edition 2000
  • Microsoft Office XP Developer
• Office 2007 testers can find the license with the Visual Studio 2005 Tools for the 2007 Microsoft Office System (Visual Studio 2005 Tools for Office Second Edition Beta, or VSTO 2005 SE Beta for short). You can download this Beta version at http://msdn.microsoft.com/office/tool/vsto/2005SE/default.aspx.

Tip: Create a query to export partial database tables to a text file using the Export Text Wizard (Access 97/2000/2002/2003)

• The Export Text Wizard helps you to easily re-create your Access table data in a text format. Unfortunately, it isn’t very helpful if you only want to export specific fields. You see, if you click the Advanced button, Access displays all the available table fields in the Field Information list. Although Access allows you to delete a field by clicking on one of the field names to select it and pressing the [Delete] key, when you try to exit the Advanced window, it displays the error: The number of fields in your export specifications does not match the number in the table you have chosen to export.
• Although exporting specific fields was a feature in Access versions prior to 97, Microsoft has decided to only acknowledge the absence of the feature as a problem in Access versions 97 through 2003. Fortunately, there are probably many ways to get around this problem. The simplest solution is to create a query that contains only those fields you want to export. Then, click on the query in the Database window and select Export from the File menu. Choose Text Files from the Save As Type dropdown list and click the Export button. This time, when Access loads the Export Text Wizard, you should see just the fields you requested in the query to export.

Tip: Dress up Access reports with curved shapes using the Circle method (2000, 2002, 2003)

• Access doesn’t offer a lot of creative license outside the basic controls it provides. However, if you’re developing a report, it does support one method that allows you to programmatically draw curved shapes, like arcs and circles, anywhere on your report page. This method is called the Circle method. You’ll use it in the Report Format() or Print() events like so:
  • Me.Circle(X,Y,radius,[color], _
    [start],[end],[aspect])
• To draw a circle on a report, open an Access database and a new report in Design view. Next, switch to the report’s Code view and copy the following code into the code module.
  • Private Sub Detail_Format( _
    Cancel As Integer, _
    FormatCount As Integer)
  •  
  • Dim sngHorizontal As Single
  • Dim sngVertical As Single
  • Dim sngRadius As Single
  •  
  • sngHorizontal = Me.ScaleWidth / 2
  • sngVertical = Me.ScaleHeight / 2
  • sngRadius = Me.ScaleHeight / 2
  •  
  • Me.Circle (sngHorizontal, sngVertical), _
    sngRadius, vbRed, , , 1
    End Sub
• If you now return to Design view and preview the report, you should see a red circle on the report page.

Tip: Keep Def <type> declarations out of your production code (2000/2002/2003)

• Veterans of VB may remember Def<type> statements as a quick way to declare a range of junk variables. Well, VBA also supports these types of declarations. They include:
  • DefBool - Boolean
  • DefByte - Byte
  • DefInt - Integer
  • DefLng - Long
  • DefCur - Currency
  • DefSng - Single
  • DefDbl - Double
  • DefDate - Date
  • DefStr - String
  • DefOb - Object
  • DefVar - Variant
• Given these types, we could use code similar to the following to declare a range of variables as dates:
  • DefDate A-C, H-I
• Although these types of declarations are legal and a great testing shortcut, you should avoid using them in production applications. Serious problems could result if you fail to declare one of these variables in your module and not realize it’s defaulting to some other data type. Also, it will make it more difficult for others who must use and support your code to understand it.

Tip: Tap into Access’ system tables to offer users a list of available database tables (2000/2002/2003)

• If you have an application where a user needs to supply the name of a table from the current database, a list of the available table names would be most helpful to him. The most common way to accomplish this task is to iterate the CurrentData.AllTables collection and add the values to the list control, like so:
  • Dim tbl As Variant
  • Combo0.RowSourceType = "Value List"
  • For Each tbl In CurrentData.AllTables
  • Combo0.AddItem tbl.Name
  • Next
• However, an alternative that requires less code is to tap directly into Access’ system tables. Access keeps track of its Tables objects in the MSysObjects system table. You can select from this table just as you would any other Access table. To see what we mean, add a Combo Box control to a new form and set its Row Source property to the following (use one line):
  • SELECT Name FROM MSysObjects
  • WHERE Flags = 0 AND ID > 0
• Requesting all records where Flags = 0 eliminates any deleted objects. Filtering for all records with an ID > 0 removes any Report, Form, and System objects from the list. The result is a list of just the Table objects.

Tip: Create your own Enum types to make it easier to select and use constant values (2000/2002/2003)

• If you have set constants that you’re always using, you should consider adding them to an Enum type. An Enum is nothing more than one or more constants that you group under an object variable. The following code creates a simple Enum that defines cold, warm, and hot temperatures:
  • Public Enum intTemps
  • intCold = 0
  • intWarm = 68
  • intHot = 90
  • End Enum
• With this Enum in place, you can access any one of the temperature values using the syntax enumName.enumVal, like so:
  • If curTemp >= intTemps.intWarm And _
  • curTemp < intTemps.intHot Then _
  • MsgBox ("Today is a nice, warm day")
• As an added bonus, you’ll receive Intellisense help as soon as you type an Enum name to minimize syntax errors.

Tip: Identify VBA controls with the TypeOf keyword (Access 2000/2002/2003)

• Often in your code procedures, you may need to determine what type of control your code has accessed. For instance, you may want to alter a property for every label on a form using the form's Controls collection. There are several ways to test for a control type, but the most efficient method is to use the TypeOf operator.
• Unlike other methods of determining a control's type, such as the TypeName property, the TypeOf keyword doesn't need several roundtrips to the Registry to obtain the information it needs. As a result, it reduces the processing drag necessary for your code.
• This keyword must appear in an If...Then statement, like so:
  • If TypeOf ctl Is CommandButton Then
  • 'Do something
  • End If
• To illustrate this keyword, the following code makes the text of all label controls on a form bold:
  • Dim ctl As Control
  • For Each ctl In Me.Controls
  • If TypeOf ctl Is Label Then
  • ctl.FontWeight = 700
  • End If
  • Next

Tip: Use the current date with other VBA functions to obtain more detailed date information (2000/2002/2003)

• When you call the VBA Date() function, you can come away with more than just the current date. You can use this value along with other VBA functions to obtain a wealth of information about the current date or build new dates. In fact, you may find that there are multiple ways to answer your date questions. For example:
  • 1) To obtain the weekday name for the current day:
    Choose(weekday(date),"Sunday","Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
  • 2) To obtain the first day of the month:
    a) date - datepart("d",date) + 1
    b) Month(Date) & "/1/" & Year(Date)
    c) DateSerial(Year(date), Month(date), 1)
  • 3) To obtain the date for the last day of the previous month:
    a) date - datepart("d",date)
    b) DateSerial(Year(date), Month(date), 1) - 1
  • 4) To obtain the first day of the next month:
    DateAdd("m", 1, Month(Date) & "/1/" & Year(Date))
  • 5) To obtain the date for the last day of the current month (even in a leap year!):
    DateAdd("d", -1, DateAdd("m", 1, Month(Date) & "/1/" & Year(Date)) )
  • 6) To obtain the days in the month:
    (DateAdd("m", 1, Month(Date) & "/1/" & Year(Date))) - (date - datepart("d",date) + 1)
• Although our examples include some direct date manipulations (like Date + 1), you should keep in mind that not all languages support this functionality.

Tip: Mark frequently used code with bookmarks to quickly access during development (2000/2002/2003)

• If you're debugging a large application, there are probably certain spots in the code that always seem to give you trouble or that you constantly refer to. Well, the VBE gives you the ability to mark these spots with bookmarks so you can quickly jump to them from any point in your project. To set a bookmark, place the mouse pointer in the code margin next to the subroutine or line you want to mark. Then, select Edit | Bookmarks | Toggle Bookmark from the main menu. The VBE places a blue box in the margin to indicate that you've marked the line. To jump from bookmark to bookmark, you can select Next Bookmark or Previous Bookmark from the Edit | Bookmarks menu. To clear a single bookmark, place the mouse pointer on the bookmark and select the Toggle | Bookmark option. To remove all bookmarks, select Clear All Bookmarks from the Edit | Bookmarks menu. If you plan to use the Bookmark features often, add them to your toolbar for quick access.

Tip: Mark frequently used code with bookmarks to quickly access during development (2000/2002/2003)

• If you're debugging a large application, there are probably certain spots in the code that always seem to give you trouble or that you constantly refer to. Well, the VBE gives you the ability to mark these spots with bookmarks so you can quickly jump to them from any point in your project. To set a bookmark, place the mouse pointer in the code margin next to the subroutine or line you want to mark. Then, select Edit | Bookmarks | Toggle Bookmark from the main menu. The VBE places a blue box in the margin to indicate that you've marked the line. To jump from bookmark to bookmark, you can select Next Bookmark or Previous Bookmark from the Edit | Bookmarks menu. To clear a single bookmark, place the mouse pointer on the bookmark and select the Toggle | Bookmark option. To remove all bookmarks, select Clear All Bookmarks from the Edit | Bookmarks menu. If you plan to use the Bookmark features often, add them to your toolbar for quick access.

Tip: Put a stop to annoying error message boxes by disabling syntax checking during development (2000/2002/2003)

• The fact that the Visual Basic Editor checks your work before you leave a line of code is a really nice feature. However, the message box that pops up identifying an error it encountered can be quite annoying. This is especially true if the error is an obvious typo or you’re leaving the line of code unfinished for a reason. Of course, you can easily disable this feature. Simply select Tools | Options from the VBE main menu. Then, locate the Auto Syntax Check check box on the Editor tab, clear it, and click the OK button. With this feature disabled, the VBE will still highlight an erroneous line of code when you leave it. However, it won’t display a message box identifying the error or return your insertion point to the bad line for correction.

Tip: Avoid For...Each loops when removing items from a collection (Access 2000/2002/2003)

• The easiest way to iterate a collection is using a For...Each loop. However, if you plan to modify the collection as you loop through its records, you should use a conditional loop instead. As you might suspect, adding or removing in a collection while you're looping through it shuffles the collection's indexes. This, in turn, prevents the For...Each loop from functioning properly. For example, the following code doesn't allow Access to close all open forms:
  • Dim obj As Access.Form
  • For Each obj In Access.Forms
  • DoCmd.Close acForm, obj.Name
  • Next
• In contrast, the following Do Loop closes all open forms:
  • Do Until Access.Forms.Count = 0
  • DoCmd.Close acForm, Access.Forms(0).Name
  • Loop
• You’ll notice that in the Do Loop, you only have to reference index 0 to close each object. That's because VBA shuffles the indexes up with each form it removes.

Tip: Add multiple controls to a form with a single visit to the Toolbox (Access 2000/2002/2003)

• If you need to add multiple controls to a form, you don't have to continually click on the control in the Toolbox and draw it on your form. To make things a little easier, you can first double-click on the desired control icon in the Toolbox. When you do this, you'll be able to add as many controls of that type to your form without clicking on the icon multiple times. Once you've added all the controls you need, simply click on the same control icon (or any other control icon) to turn the feature off.

Tip: Display simple help messages with the ControlTipText property (Access 2000/2002/2003)

• If you need to convey a little helpful information to users, most Access objects provide a property specifically geared toward this: the ControlTipText property. If you aren't familiar with this property, any text assigned to ControlTipText appears in a small yellow ScreenTip when you hover your mouse over the control.
• The ControlTipText property makes it easy to display brief information about an object, and often it may be all you need. However, it does have a few drawbacks. First, the ControlTipText string can be a maximum of 255 characters. In addition, a few seconds typically elapse between when you hover your mouse over a control and when the ScreenTip actually appears. Finally, you don't have any control over the font, color, or length of time the tip is visible.

Tip: Printing headers, footers, and details on separate pages (Access 97/2000/2002)

• There may be times when you'd like the detail information on a report separated from header or footer sections.  For instance, a group footer may contain summary results that you want to print as a separate sheet.  Fortunately, it's easy to control whether headers, footers, and detail sections begin on separate pages.  Simply set the appropriate section's Force new page property.  The default setting (None) prints the current section on the current page.  However, the other settings allow you to force page breaks before and after the current section.  Although you probably rarely print forms, it's worth noting that form sections also support a Force New page property.

Tip: Display simple help messages with the ControlTipText property (Access 2000/2002/2003)

• If you need to convey a little helpful information to users, most Access objects provide a property specifically geared toward this:
  • the ControlTipText property. If you aren't familiar with this property, any text assigned to ControlTipText appears in a small yellow ScreenTip when you hover your mouse over the control.
• The ControlTipText property makes it easy to display brief information about an object, and often it may be all you need. However, it does have a few drawbacks. First, the ControlTipText string can be a maximum of 255 characters. In addition, a few seconds typically elapse between when you hover your mouse over a control and when the ScreenTip actually appears. Finally, you don't have any control over the font, color, or length of time the tip is visible.

Tip: Using intrinsic constants in parameters (Access 97/2000/2002)

• When you create VBA procedures that accept parameters, you may wish that you could take advantage of the constants that are automatically available when working with common properties and methods.  For example, when you enter
  • DoCmd.Close
• in a procedure and press [Spacebar], the IntelliSense feature automatically displays a dropdown list of the relevant object type constants.  Now, let's say that you create a procedure like the following:
  • Sub DoStuff(strObjectName As String, intObjectType As Integer)
  • '...
  • Do stuff
  • '...
  • End Sub
• You need to pass an object name and type to the procedure..  Although the object type is an integer value, you can use an intrinsic constant to pass the appropriate values, such as with the statement
  • DoStuff "EmployeeForm", acForm
• However, you must already know the appropriate constant,  because the VBE won't provide the list of valid possibilities as the procedure is currently written.  Fortunately, there's a way to take advantage of existing constant collections.  To do so, declare the parameter using the appropriate class name.  For example, change the procedure to the following:
  • Sub DoStuff(strObjectName As String, intObjectType As AcObjectType)
  • '...
  • Do stuff
  • '...
  • End Sub
• When you subsequently call the procedure, the dropdown list of constants appears as soon as you add a comma after the name parameter.

Tip: Quickly move to specific records (Access 97/2000/2002)

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

Tip: Create a query to export partial database tables to a text file using the Export Text Wizard (Access 97/2000/2002/2003)

• The Export Text Wizard helps you to easily re-create your Access table data in a text format. Unfortunately, it isn't very helpful if you only want to export specific fields. You see, if you click the Advanced button, Access displays all the available table fields in the Field Information list. Although Access allows you to delete a field by clicking on one of the field names to select it and pressing the [Delete] key, when you try to exit the Advanced window, it displays the error: The number of fields in your export specifications does not match the number in the table you have chosen to export.
• Although exporting specific fields was a feature in Access versions prior to 97, Microsoft has decided to only acknowledge the absence of the feature as a problem in Access versions 97 through 2003. Fortunately, there are probably many ways to get around this problem. The simplest solution is to create a query that contains only those fields you want to export. Then, click on the query in the Database window and select Export from the File menu. Choose Text Files from the Save As Type dropdown list and click the Export button. This time, when Access loads the Export Text Wizard, you should see just the fields you requested in the query to export.

Tip: Suppress repetitive data to avoid distracting readers (Access 97/2000/2002)

• Sometimes, you may have data that needlessly clutters a report.  For instance, suppose your report is listing the fields Company, FirstName, and LastName.  If there are multiple people listed for each company, and the report is sorted by company name, repeating the company information is unnecessary.  Your first instinct may be to create a group header based on the company name.  However, sometimes the best layout for a business need dictates that the redundant data should appear in the report's Detail section -- you just don't want to see it repeated every time.  Fortunately, you can create such a report easily.
• First, open the report in Design view and select the control that displays repetitive information.  Then, display the control's Property sheet and set the Hide Duplicates property equal to Yes.  Finally, save and preview your report.  If the data in the modified control is the same as the data from the previous record, the control is hidden.

Tip: Calculate the percentage of records in a group (Access 97/2000/2002)

• Although it's easy to count the number of records in a group, determining that number's percentage of the report's overall record count may not seem easy to do.  Fortunately, it's easy to calculate the percentage.  First, add a textbox control to the report's Detail section and name it txtCountAll.  Then, set its Visible property to No and set its Control Source property to:
  • =Count(*)
• Then, add a textbox control the group footer.  Name the new control txtCountGroup and set its Visible property to No.  As before, set its Control Source property to:
  • =Count(*)
• Next, add another textbox to the group footer and set its Control Source property to:
  • =txtCountGroup/txtCountAll
• Finally, set the new textbox control's Format property to Percent.

Tip: Simulate fuzzy searches with the Filter() function (Access 2000/2002/2003)

• You're no doubt familiar with the Like keyword in SQL. It allows you to return multiple records based on a selection criteria string. Well, the VBA offers a function that behaves similarly to the Like statement, but for one-dimensional arrays. It's called the Filter() function. The syntax for the Filter() function is as follows:
  • objArray = Filter(InputStrings, Value
  • [, Include[, Compare]])
• For the InputStrings argument, you'll supply a one-dimensional array of string values. The Value argument represents the string of characters you're looking for in the array. The optional Include argument allows you to specify whether the new array should consist of records that contain the Value or records that don't contain the Value. The Compare argument is also optional. A value of 0 indicates you want binary comparison (case sensitive) and a value of 1 requests a text comparison (not case sensitive). Consider the following string array with a few example calls to the Filter() function:
  • Dim test as string
  • test = "Boat,Coat,Load,Float,Brick,Oat Bran"
  •  
  • Dim objArray as Variant
  • objArray = Split(test,",")
  •  
  • Debug.Print (Join(Filter(objArray, "oat", 1, 0), ","))
  • Debug.Print (Join(Filter(objArray, "oat", 1, 1), ","))
  • Debug.Print (Join(Filter(objArray, "oat", 0, 1), ","))
• If you place this code in a function or subroutine in your module and execute it in debug mode, the resulting Immediate window would contain:
  • Boat,Coat,Float
  • Boat,Coat,Float,Oat Bran
  • Load,Brick

Tip: Programmatically limit the length of Text Box responses with the KeyPress event (Access 2000/2002/2003)

• As you may know, you can always apply an input mask to a control in Access to limit the number of characters the user enters into the field. However, if the maximum length is a large number or the maximum length varies based on other form criteria, a mask just isn't practical. In such cases, you can use the KeyPress event restrict the field length. This event procedure has a single integer argument called KeyAscii that identifies the key that the user just pressed. However, you can also use this argument to ignore any keys pressed after the field length reaches a certain point. Consider the following code:
  • Private Sub Text1_KeyPress(KeyAscii As Integer)
  •           If (Len(Text1.Text) + 1) > 5 And _
  •           KeyAscii <> Asc(vbBack) Then KeyAscii = 0
  • End Sub
• This code restricts the Text Box to five characters. First, it checks to see if the key the user just pressed will make the field length greater than five. If it will, the code sets the KeyAscii value to 0, which cancels the key press. The code also checks to make sure the key the user pressed is not the Backspace key, since he should be allowed to clear the field at any time.

Tip: Prevent erroneous data by applying validation rules (Access 97/2000/2002)

• The more you control the quality of the data being entered into tables, the better results you'll get when you need to retrieve, analyze, or manipulate the data.  To ensure that data conforms to your business needs, you can use data validation rules that prevent unacceptable entries.  For example, say you have a table storing purchase information and you want to require that the quantity field always has a number greater than zero, or that a date field only contains values after a specific date.  You can easily create validation rules that require correct entries and provide messages to users when information violates the rules.
• To incorporate validation rules, set the Validation Rule and Validation Text properties at either the control or table field level.  For instance, to require that a value is greater than zero, you can use the following property settings:
  • Validation Rule: >0
  • Validation Text: Quantity must be greater than 0.
• Likewise, date field validation might look like:
  • Validation Rule: >=#1/1/2001#
  • Validation Text: Only dates from 2001 and later are accepted.
• When a user enters an inappropriate value, the message stored in the Validation Text property is displayed.

Tip: Alternate page number placement on reports (Access 97/2000/2002)

• Although you may typically print out single-sided reports, situations may call for a report printed on both sides of the page.  When your reports must support duplex printing, you may want to alternate the position of the page number indicators to create a more professional, book-like appearance.  For instance, you may want the page number to appear on the left side of the footer on an odd-numbered page, but have it appear on the right side on the page when the page number is even.  Fortunately, you don't have to go to a lot of work to set up such a page numbering scheme.
• With your report open in Design view, choose Insert | Page Numbers from the menu bar.  Then, choose whether the number should appear in the header or footer using the appropriate Position option.  Finally, select either inside or Outside from the Alignment dropdown list and click OK.  The Inside setting prints odd page numbers on the left side of the page and even numbers on the right.  Selecting the Outside setting produces the opposite result, with odd page numbers appearing on the right and even numbers on the left.

Tip: Temporarily show all table fields in an Access query (97/2000/2002)

• When you're designing a query, you'll often find that you need to temporarily view fields from the underlying tables beyond the ones you want shown in the final result.  To do so, you probably drag the relevant fields to the design grid and then delete them when you're done.  However, there's an easier way to view the data that you typically want excluded in the query.  To do so, open the query in Design view.  Click in the top pane of the window and then click the Properties button to open the query's properties sheet.  (You can also right-click in the top pane and choose Properties from the shortcut menu.)  Set the Output All Fields property to Yes, close the properties sheet, and run the query.  Data from all of the underlying tables' fields will be displayed in addition to the fields you specified in the query design grid.  To change the query so that it only displays the fields explicitly selected in the query design grid, simply reset the Output All Fields property to No.

Tip: Changing the back-end data source in a split application (Access 97/2000/2002)

• You'll often design an application as two MDB files: a front end that contains objects like forms, reports, and modules, and a back end containing the data tables.  Although you may use the application without ever having to change which database the front end looks to for its data, there may be cases in which you need to change the back end specifications.  For instance, If the back end database file's location changes, you'll have to update the front end to look to the new location.  Sometimes, you'll want to change a functioning front end to use different back end data to meet a particular business need, such as providing an interface to archived data that uses the same structure as your current application's current back end database.
• To change the data location information in a split application, open the front end and choose Tools | Database utilities | Linked Table Manager from the menu bar.  Select the check boxes next to the objects you want to update, or click the Select All button to modify all of the linked table information.  Then, select the Always Prompt For A New Location check box and click OK.  Locate and select the appropriate back end database file and then click Open.  Depending on how many data sources your front end is linked to, you may have to repeat this process multiple times.  When you're returned to the Linked Table Manager, clear the Always Prompt For New Location check box and click Close.

Tip: Associate an orphaned event procedure with a control (Access 97/2000/2002/2003)

• When you're designing a form or report, it's easy to accidentally orphan an event procedure as you tweak, delete, add, and rename controls. You can end up with a control that appears to have a broken event procedure. Even though the control and procedure seem to go together, the event doesn't appear to fire when it's supposed to. Fortunately, the situation is easy to address.
• We'll start by illustrating the problem. First, create a blank form in Design view and add a button to it. Name the button cmdDate. Then, click the Code button to launch the Visual Basic Editor (VBE). At the insertion point, add the following code:
  • Private Sub cmdDate_Click() MsgBox "Today is " & Date End Sub
• Now, close the VBE and switch to Form view. Click the button and Access displays the message box. At this point, switch back to Design view and delete the button. Then, add another one and name it cmdDate.
• Switch to Form view and try the button -- nothing happens. Open the VBE and you'll see that the event procedure is still there. Although the procedure and control names match, they aren't connected. Compiling the project won't fix the problem, nor will saving and reopening the form.
• The fix is easy. All you have to do is switch to Design view and set the command button's On Click property to [Event Procedure]. After you make the change, you'll find that Access once again runs the procedure when you click on the control.

Tip: Clarify list items with column headings (Access 97/2000/2002)

• Although listbox and combobox controls have an associated label by default, sometimes the data in the list would benefit from an additional descriptive heading.  This is particularly true when the list displays multiple columns.  Fortunately, it's easy to add column headings based on the control's row source data.  Just set the control's Column Heads property to Yes.  If the row source is based on a table or query,  the displayed fields' Caption properties appear as column headings.  If the row source is based on a value list, the initial items in the list are used as column headings; using as many items as are needed for the number of specified columns.  Although you can set the property when the Row Source Type is set to Field List, the heading isn't really effective as the first field names are used as column headings, producing inappropriate results.  Note that you can't change the way column headings appear -- if you want formatted headings you'll need to take a different approach, such as adding label controls above a listbox control.

Tip: Center a form or report within the application window (Access 97/2000/2002/2003)

• By default, forms and reports appear positioned at the same place they were when saved. There are probably some forms or reports that you'd prefer always appear in the center of the application window, such as a switchboard form that automatically appears when the application starts. To configure a form or report to appear in the center of the window, simply open the object in Design view and set its AutoCenter property to Yes.

Tip: Leverage pre-existing code in a new application (Access 97/2000/2002/2003)

• When a project requires the same procedures you developed for a previous application, you can always copy the procedures or import their modules into the new file. You have another option if you work in an environment where you'll have continual access to the previously created files. You can simply set a reference to the MDB file that contains the code to gain access to its public procedures.
• To set the reference, open any module in your current database. Then, choose Tools | References from the menu bar. Click the Browse button and change the Files Of Type setting to Microsoft Office Access Database. Locate and select the file, and then click Open. The database name appears in the Available References list with a check beside it. Finally, click OK. From this point, you can use the functions and sub procedures from the original file as if they were stored in the current database.

Tip: Add temporary comments without changing a report's design (Access 97/2000/2002)

• There are plenty of times when you'll want to annotate a report with text that shouldn't become a part of the report's permanent design.  For instance, say that you print a report to get a rough idea of a project's status even though data entry hasn't been completed.  Doing so may quell an immediate business crisis, but it could just as easily stir up another one three weeks down the road when someone happens upon the report and demands to know why there are huge gaps in the data.  However, if you clearly indicate the circumstances behind why a report was printed or who requested the report, you can eliminate confusion, and stress down the road.
• Of course you can just hand-write an explanation on a report, but if you frequently annotate a particular report you can easily incorporate temporary text without continually changing the report in Design view.  There are many ways to approach the task, but the easiest is to use a textbox control that prompts  a user to enter any comments that should be included when the report is opened, in much the same way that a parameter query prompts a user for criteria.
• To set up a control, open the report in Design view and add at textbox control where you want the user-defined text to appear.  Size the control so that it's big enough to describe a likely comment.  Then, set the control's Control Source property to:
  • =[Enter any comments you want to make about this report:]
• Save the report and close it.  From now on, you're prompted to enter a comment whenever the report is previewed or printed.  Enter the appropriate text and click OK.  Your entry appears on the final report.
• Note that you must click OK when you receive the report's prompt--clicking Cancel actually aborts the entire print process.  this doesn't mean you're forced to enter a comment.  Just leave the prompt text box blank and click OK to continue printing without including a comment.

Tip: Suppress headers and footers at a report's beginning or end (Access 97/2000/2002/2003)

• By default, page headers and footers appear on every page when you print a report. If your report includes a report header or footer, which print at the beginning and end of the report, the normal page header and footer may be inappropriate on the affected pages. If so, you can easily prevent a page header or footer from printing. To do so, open the report in Design view and display its properties. You can then control when page headers and footers print using the Page Header and Page Footer properties. These properties accept the following settings:
  • All Pages (the default)
  • Not With Rpt Hdr
  • Not With Rpt Ftr
  • Not With Rpt Hdr/Ftr

Tip: Easily retrieve data for the current month or year (Access 97/2000/2002)

• If you need to limit query results to a particular month or year, you may not have to specify exact beginning and ending dates when establishing your criteria, particularly if the selection criteria are relative to the current date.  Instead, you can simplify things using the Month() and Year () functions.
• For instance, say that you have a table containing the field PurchaseDate.  In the query design grid, add the following expression to a blank Field text box:
  • PurchaseYear: Year([PurchaseDate])
• and enter the following in the column's Criteria text box:
  • Year(Now())
• Running the query displays all of the records with a PurchaseDate value that falls within the current year.  (To hide the year column, just clear the PurchaseYear column's Show checkbox when the query is open in Design view.)
• Likewise, you can find records made in a particular month using the Month() function.  Say you keep the PurchaseYear criteria in place and add a field that uses the expression:
  • PurchaseMonth: Month([PurchaseDate])
• If you set PurchaseMonth's criteria to:
  • Month(Now())
• the query returns records with a PurchaseDate value from the current month of the current year.

Tip: Simplify building query criteria and field expressions (Access 97/2000/2002/2003)

• You've probably used the Expression Builder to construct property setting values. If a property supports one of Access's builders, placing your insertion point in the property's text box displays the Build button (which resembles an ellipsis). You can also use the Expression Builder to help set up queries, even though no Build button appears when you work in a Field or Criteria text box. To access the builder, simply right-click on the Field or Criteria text box and choose Build from the shortcut menu.

Tip: Alphabetize a recordset by either of two fields (Access 97/2000/2002)

• There may be times when you'd like to alphabetize data based on an alternate field if the one you primarily sort by is blank.
• For example, you might want to sort a query by either company name or a contact's last name.  Unlike a traditional sort, you don't want to just group all of the records that lack company names at the beginning of the datasheet.  Instead, if the CompanyName field is blank, you want Access to use the contact's last name in its place.  In other words, you want to treat a person's last name as if it were a company name when the company is missing, to create a datasheet that looks like:
  • CompanyName     FirstName     LastName
  • --------------  ------   -------
  • All-Weather Wear Inc.  Claire       Zemeckis
  •           Larry        Anderson
  • Beckett Motor Company    Brian      Brault
• To accomplish such a sort, create an extra query field specifically to provide the custom sort, then use the NZ() function to replace the contents of one field for Null values in another.  For instance, let's say you're working with a query using the previously described fields.  Open the query in Design view and remove any previously existing sort specifications.  Then, enter the following in a blank Field text box:
  • SortValue: NZ([CompanyName],[LastName])
• Finally, select Ascending from the new column's Sort dropdown list.  When you run the query, the NZ() function returns the contents in LastName instead if CompanyName is Null.

Tip: Password-protect VBA modules (Access 2000/2002/2003)

• Securing your database in Access 2000 and above no longer protects your VBA modules, so you'll need to manually password-protect any code you want to prevent others from seeing. Even if you don't use user-level security, you may want to protect modules to prevent others from borrowing or accidentally changing your code. To do so, press [Alt][F11] to open the VBE. Then, choose Tools | <databasename> Properties from the menu bar and click on the Protection tab. Next, select the Lock Project For Viewing check box. In the appropriate text boxes, enter the password you want to use, then click OK. When you later try to access the VBA code, Access will prompt you for the password.

Tip: Conditionally display text strings (Access 97/2000/2002/2003)

• You'll often want to customize text according to a variable condition. For instance, if one field's value is greater than or equal to another field's, you may want to return one message; if the value is less, you want to return a different message. You can accomplish this with the IIf() function, which uses the syntax:
  • IIf(expr, truepart, falsepart)
• Let's say that a form or report displays fields named Target and Total. You can display a conditional message based on the controls' values by adding another textbox and specifying the following expression as the Control Source:
  • =IIf([Total]>=[Target],"You've met the goal!","Goal not reached.")
• You can also use the IIf() function to create field expressions in queries.

Tip: Inserting line breaks when entering data (Access 97/2000/2002)

• We recently told you how to customize the way in which Access reacts to the [Enter] key during form navigation.  As a refresher, you can control whether pressing [Enter] moves focus to the next control, next record, or does nothing at all.  there's another task you're no doubt used to performing with the [Enter] key--inserting line breaks in text.
• You may rarely need to insert line breaks when working with textbox controls that are bound to text fields, since all fields should be dedicated to storing discrete information.  However, some situations can warrant line breaks.  When you need to insert a line break only occasionally, you can do so by pressing [Ctrl][Enter].  this technique can also be used for things like inserting line breaks in label Caption properties.
• If line breaks are frequently entered in a textbox control, you can configure the control to override Access's navigation behavior and add a line break when just the [Enter] key is pressed.  To do so, set the control's Enter Key Behavior property to New Line In Field.  This is actually the default property setting that's assigned when you add a textbox control that's bound to a memo field.

Tip: Create your own form and report templates (Access 97/2000/2002/2003)

• If you want to frequently reuse the settings you've applied to a form or report, you can configure Access to treat the object as a template for new ones. To do so, choose Tools | Options from the menu bar. Then, switch to the Forms/Reports tab. Enter the appropriate name in the Form Template or Report Template text box and click OK. Note that the template only applies to the current database. Also, it's only used when you create a form or report in Design view -- objects created using a wizard are unaffected.

Tip: Retrieve the current username (Access 97/2000/2002/2003)

• If a database incorporates user-level security, you can easily retrieve the name of the user that's logged into the current instance of the database. To do so, use the CurrentUser() function. For example, set a control's Control Source property to:
  •  =CurrentUser()
• to display the username. Or you might use it to retrieve records that have been flagged with usernames by incorporating CurrentUser() into a query's criteria expression.  

Tip: Easily center a report title on the page (Access 97/2000/2002)

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

Tip: Creating optional arguments in VBA procedures (Access 97/2000/2002/2003)

• Native VBA functions often don't require that you supply values to every supported argument. Chances are you'd like to design your own procedures and functions in the same way. Doing so is easy. When you declare the procedure's arguments, use the Optional keyword as in the following sample procedure:
  • Sub OptionalDemo(lng As Long, Optional lng2 As Long, _ Optional lng3 As Long) Debug.Print 10 * (lng + lng2 + lng3) End Sub
• Note that the Optional keyword must precede each optional argument. Also, once you use the keyword, all subsequent arguments must be optional as well.
• To test the function, try entering the following statements in the Immediate window:
  • OptionalDemo 34,10,5 OptionalDemo 34,,5 OptionalDemo 34 OptionalDemo
• All of the statements except the last one return a result because only the first argument is required.

Tip:  Customize keyboard navigation to suit your work style (Access 97/2000/2002)

• Chances are that you take for granted how your direction arrow and [Enter] keys behave in Access.  Typically, when you press [Enter], the focus moves to the next field and the contents of the field are selected.  Likewise, when you press the direction arrow keys, the focus moves to the next field in the appropriate direction.  You aren't forced to accept these behaviors.  If you'd like, you can set up the arrow keys to move from one character to the next in the current field, rather than moving focus to the next field.  You can also configure the [Enter] key to move to the next record when it's pressed or configure it to do nothing at all.  When you do move focus to  another field, you have the option to place the insertion point at the beginning or end of the field, rather than selecting the field's entire contents.  To modify these settings, choose Tools | Options from the menu bar and click on the Keyboard tab.  Then, simply make the selections you want and click OK.

Tip: Easily substitute strings in place of Null values (Access 97/2000/2002)

• Although you may make a best effort to eliminate Null values from table fields, it's inevitable that some records eventually have fields that are Null.  Null fields may not necessarily be a problem at the table level, but they can be a nuisance when you view a query and see unexplained blank cells in a datasheet.  If you work with the data programmatically, Null fields can break your procedures.  Even worse, fields aren't the only area of concern when you're creating VBA procedures -- Null Variables can be equally troublesome.  You may deal with such problems by incorporating special error-handing or using conditional statements and the IsNull() function to control the procedure statements and the IsNull() function to control the procedure flow, you can often use an easier method to deal with such situations.
• Instead of building conditional statements with If... Then statements or the IIf() function, you can use the Nz() function.  This functions specific purpose is to return a string in place of Null.  The full syntax is:
  • Nz(Value, ValueIfNull)
• The first argument is the variable or field you want to check.  The second argument is the string that should be returned when Value is Null.  For example, say you have a query that retrieves data from a field named Revenue.  If you enter the expression
  • Rev; Nz([Revenue],"Infromation Unavailable")
• as one of the query's fields, the Rev column will display the message "Information unavailable" if the Revenue field is Null, otherwise the value stored in Revenue is displayed in the query datasheet as usual.
• Note that if you leave the ValueIfNull argument blank, a zero-length string is automatically returned when Value is Null.

Tip: Retrieving random records with a query (Access 97/2000/2002/2003)

• We recently discussed how to retrieve a portion of a query's results using the Top Values feature. You can also use this feature to perform a common task - returning a random selection of records. To do so, create a basic select query based on your desired data source. Then, in a blank Field text box in the design grid, add the following expression:
  • RandomEx: Rnd([valuefield]) where [valuefield] is any field in the data source that contains numeric values--the field can be an AutoNumber, Number, or Date/Time field.
• Next, apply a sort order to the RandomEx field (it doesn't matter whether you choose Ascending or Descending). Finally, use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want a number or percentage that isn't listed, simply enter the desired amount in the Top Values text box. When you run the query, you'll find that Access returns a random selection from the underlying record source.

Tip: Returning a subset of top or bottom values in a query (Access 97/2000/2002/2003)

• It's easy to create a query that returns results based on a specified number of values found in a field or a percentage of records based on a field value. To do so, you use a query's Top Values property. For instance, this property can be used to list only records where a field contains the top 10 values found in the underlying recordset. Or, you can return records where a field value is in the lowest 25 percent of all the records' values.
• Let's say you have a query that returns a list of products that has a descending sort order applied to the quantity sold field. When you run the query, the records with the highest number sold are at the top. However, you want just the top 25 percent of the sorted results to show in the query result. To do this, open the query in Design view. Then, use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want the query to return a number or percentage that doesn't appear in the dropdown list, simply enter your own custom value in the associated text box.
• Remember that the sort order applied to the query determines which records are the top values. If you have a descending sort applied, the top values are the largest values. With an ascending sort applied, your top values are the lowest values.

Tip: Draw attention to data with conditional formatting (Access 2002)

• Sometimes you deal with so much data, it's easy for important information to get buried among everything in a form or report.  You can highlight data that users should pay attention to using conditional formatting.  For example, if you want to draw attention to inventory records containing a quantity greater than 1000 units, you can shade the control bound to the quantity field with a bright yellow background.  In older versions of Access, you were forced to crate custom solutions to apply such formatting, but it's easy to do in Access 2002.  In fact, if you've ever used the Excel's comparable feature, you'll find the process is virtually the same.
• To apply conditional formatting, open your report or form in design view.  Then, select the appropriate control and choose Format | Conditional Formatting from the menu bar.  You're prompted to define the first condition, which can be based upon the field value or a custom expression.  In the Condition1 panel, use the formatting controls to define the format that should be applied when the criteria expression evaluates to a True result.  When the expression evaluates to a False result, the formatting shown in the Default Formatting panel is applied.
• For example, to apply special formatting when the field value is greater than 1000, select Field Value is from the first dropdown list, Greater Than from the second dropdown list, and enter 1000 in the associated text box.  Then, select a shade of yellow from the Fill/back Color palette.  Finally, click OK.  When you view the form or report, the control displaying the relevant data appears yellow when the displayed value is greater than 1000.
• Note that you can require that up to three conditions be met before the conditional formatting is applied.  Just click the Add button in the Conditional Formatting dialog box to set another condition.  Keep in mind that when you specify multiple conditions, all of them must evaluate to True for access to apply the desired formatting.

Tip: Create new tables from external queries (Access 97/2000/2002)

• When you create a new Access database, you'll sometimes want to work with the results of an external query but you won't have any other need for the query's underlying tables.  Fortunately, you don't have to import the unnecessary data.  There are a slew of ways to accomplish the task, but the easiest to do may be the easiest to overlook--you can import the query as a new table.  To do so, select File | Get External Data | Import from the menu bar.  Select the appropriate database and click Import, then select the queries you want to import on the import Objects dialog box's Queries property sheet.  Next, click the Options button and select the As Tables option button in the Import queries panel.  Finally, click OK.  Access processes the queries and saves the results as a table with the same name as the original query.

Tip: Display default text when a field is Null (Access 97/2000/2002/2003)

• You can use a field's Format property to display default text information that is unavailable or unknown. To do so, set the Format property to:
  • @;"Your Default Text"
• As you can see, the format consists of two sections separated by a semicolon. The string specified in the second section appears when the field is Null or contains a zero-length string. Note that you can set the Format property at both the control and field level.

Tip: Fine-tune form and report control placement (Access 97/2000/2002)

• when you add a control to a form or report, Access tries to aid you in sizing and aligning it using the Snap To Grid feature.  The grid is typically visible as you work with a form or report in Design view, but if it isn't you can display it by choosing View | Grid from the menu bar.  Although the Snap To Grid feature is usually helpful when you fine-tune the placement of some controls you may find that you need to move some of them to positions that aren't exactly aligned with the design grid.  You can toggle whether the Snap to Grid feature is active by selecting Format | Snap To Grid while in Design view.  However, you don't  have to take this step--you can temporarily disable the Snap To Grid feature by holding down the [Ctrl] key.  Then, you can use your mouse or the cursor arrows to place the controls exactly where you want them.

Tip: Using Favorites shortcuts to open files (Access 97/2000/2002/2003)

• If you frequently work with specific Access applications, you may have set up shortcuts to them on your desktop, the Windows Start menu, or a toolbar. You can also set up shortcuts to frequently used files, or at least the folders containing those files, within Access' Open dialog box. The shortcuts are also visible in the Save As dialog box, making it easy to save to a consistent location. To set up shortcuts, follow the steps appropriate for your version of Access.
  • Access 97:
    • Click the Open button on the Database toolbar. Then, use the Open dialog box to locate and select the file for which you want to create the shortcut. Click the Add To Favorites button in the Open dialog box and choose to add a shortcut to either the database or the folder in which it's stored.
  • Access 2000:
    • Click the Open button on the Database toolbar. Then, use the Open dialog box to locate and select the file or folder for which you want to create the shortcut. Click the Tools dropdown menu and select Add To Favorites.
  • Access 2002:
    • Click the Open button on the Database toolbar. Then, use the Open dialog box to locate and select the file or folder for which you want to create the shortcut. Click the Tools dropdown menu and select Add To Favorites to create a shortcut to a file or select Add To "My Places" to create a shortcut to a folder.
  • Access 2003:
    • Unfortunately, Access 2003 only lets you create shortcuts to folders. Click the Open button on the Database toolbar. Then, use the Open dialog box to locate and select the folder for which you want to create the shortcut. Click the Tools dropdown menu and select Add To "My Places". Note that if you upgraded from a previous version of Access, any previously existing shortcuts you created are stored in your operating system's Favorites folder.
• To take advantage of a shortcut, display the Open or Save As dialog box as you normally would. Then, do the following as appropriate:
  • Access 97:
    • Click the Look In Favorites button.
  • Access 2000/2002:
    • Click the Favorites button (or another appropriate folder button) on the Places bar.
  • Access 2003:
    • Click the appropriate folder button on the Places bar.

Tip: Quickly copy table structures through the user interface (Access 97/2000/2002)

• A short while ago we discussed using action queries to create a blank table that uses an existing table's structure.  As mentioned in the tip, the query technique was intended to help those that need to provide an automated way of creating a table to reduce repetition or aid those unfamiliar with Access.  However, many of you wrote in to point out the ease with which table structures can be manually copied.  We've covered this before, but it seems as though it's good time to revisit the topic.
• Copying a table structure is easy--it's just about as easy as copying and pasting text in word.  First, select the table that has the structure you want to copy in the Database window.  Then, choose Edit | Copy from the menu bar or use the familiar [Ctrl]C keyboard shortcut.  Next, select Edit | Paste or press [Ctrl]V.  Access now displays the Paste Table As dialog box.  to create a new table with the same structure, enter a name in the Table Name text box, select the Structure Only option button and click OK.  Note that if your database contains two tables with the same structure you can also use the Paste Table As dialog box to append data from one table to the other.

Tip: Keep an Access form or report locked in place (Access 2002/2003)

• We recently discussed how setting a form's border style to None prevents users from moving an open form. Unfortunately, this setting choice also hides the form's title bar. That means you have to implement custom ways to perform the duties normally supplied by the title bar's Close button or Control Box. Fortunately, forms in Access 2002 and later support a Moveable property that lets you both show the title bar and lock a form in place. Simply set the property to No and users can't move the form.  

Tip: Programmatically hiding tables safely (Access 2000/2002)

• Some time ago we ran a tip that discussed hiding objects in the Database window.  As you may recall, you can hide an object by right-clicking on it, choosing Properties form the shortcut menu and then selecting the Hidden check box.  One reader wrote in with concerns regarding concerns relating to a bug that can cause hidden tables to be deleted.  First, be reassured that there's nothing to fear--the user interface technique we discussed is totally safe.  The behavior that could cause tables to get deleted only comes into play when a table is hidden programmatically using DAO.
• Although you shouldn't use DAO to programmatically hid tables, there is a safe way to programmatically hide them from view in the Database window--if you have Access 2000 or above.  To do so, use the SetHiddenAttribute method.  This method applies to the Application object and uses the syntax:
  • SetHiddenAttribute ObjectType, ObjectName, fHidden
• Essentially, is the programmatic equivalent to the Hidden check box available through the user interface.  The ObjectType argument accepts one of the standard AcObjectType constants (like acForm or acTable) and is used to indicate the type of object with which you're working.  The ObjectName argument is simply the name of the object and fHidden accepts True or False to determine whether the object is hidden.
• To illustrate, let's say that you have a database containing a table named tblOrders.  you can use the following two procedures to programmatically change whether the table is visible in the Database window.
  • Sub HideTable()
  • Application.SetHiddenAttribute acTable, "tblOrders", True
  • End Sub
  •  
  • Sub  ShowTable()
  • Application.SetHiddenAttribute acTable, "tblOrders", False
  • End Sub
• If you want to check whether a database object is hidden, you can do so with the GetHidden Attribute method.  For example, use the following procedure to display the tblOrders table's visible status in the Immediate window:
  • Sub TableHiddenStatus()
  • Debug.Print Application.GetHiddenAttribute(acTable, "tblOrders")
  • End Sub

Tip: Reclaim the space temporary objects use (Access 97/2000/2002/2003)

• It's easy for Access to catch you by surprise if you aren't careful. Access 2000 and later have a 2 GB file size limit, which many people think they have no chance of ever approaching. (The limit in Access 97 is 1 GB.) However, even if your application's tables are relatively small, you can hit the size limit quicker than you might think if you perform tasks that heavily use temporary tables. Many people don't realize that Access doesn't automatically reclaim space when you delete records and objects in an Access database.
• To ensure that your MDB files stay beneath the size at which problems will occur, compact them to reclaim the space that w