Note: If the phrase "Tips & Tricks" is included in a topic link, there's numerous tips & tricks on that particular topic .

 
 
 

Can't Use the Equals Operator with Between

  • You can't use the equal operator with the BETWEEN...AND operator.
  • The correct way to express this condition is as follows: "[field] Between 1 AND 3"
  • The BETWEEN...AND operator simply doesn't need the equal operator.
 

Caps Lock Feature

Have you ever pressed Caps Lock unintentionally? All of us have--it's easy to do because it's just above the Shift key. That's why Access disregards Caps Lock mode (when it's on) if you press the Shift key for one letter and then release Shift to type the remaining letters in your entry.

  • You can easily test this feature.
  • Simply press the Caps Lock key and type the word access into a empty table cell--without pressing the Shift key to enter the first letter, as you normally would.
  • Access returns the string ACCESS.
  • Now, with Caps Lock still on, hold down the Shift key and type the word access without releasing the Shift key.
  • This time, Access returns the string access (all lowercase).
  • Neither example presents any surprises.
  • Access returned the strings we expected.
  • Now, enter the word access one more time with Caps Lock still on--but this time hold down the Shift key while you type the first letter.
  • Then release the Shift key and enter the remaining letters, ccess
  • Instead of returning aCCESS, which you might have expected, Access returns the string Access (proper case).
  • In Caps Lock mode, if you hold down the Shift key for just the first letter.
  • Access assumes the Caps Lock mode is on by mistake and ignores it.

This behavior can be a real time-saver. It can also be a nuisance if you meant to enter aCCESS, not Access. If this is the case, you can turn off this feature. Choose AutoCorrect from the Tools menu, and deselect the Correct Accidental Use Of cAPS LOCK Key option.

 

Combo Box/List Box

AutoExpand

The combo box control has a unique property named Auto Expand. The Yes setting (the default) searches the item list for the first matching item as you enter characters, and displays the first matching item in the text box. This drastically cuts down on the amount of typing you must do. If you want to accept the item, you simply stop typing.

For instance, let's assume your list contains the items aardvark, annie, annotate, and anteater. When you enter the letter A, the combo's text box component displays the item aardvark--the first item in the list that matches "a". If you then enter the letter N, the text box displays the word annie--the first item in the list that matches "an". If your third letter is T, the control displays anteater. However, if you enter a second N, the text box continues to display annie. Then, if your fourth letter is an O, the control updates to display annotation.

If you don't like this feature, you can turn it off. Simply set the Auto Expand property to No.

back to top

Column Count

You probably know that a combo box or a list box can have multiple columns. In fact, the control will display all of the fields in the bound table or query from left to right up to the number you specify as the control's Column Count property. In other words, if the bound object has ten columns and you set the control's Column Count property to eight, the control will display the first eight columns from left to right.

There is one catch you might not be aware of--the Column Count property value must be an integer from 1 through 255. Neither the combo box nor the list box can display more than 255 columns. However, this limitation shouldn't cause too much trouble, since 255 columns will be more than adequate for most uses.

This means the control's Bound Column property can't be greater than the Column Count property. The Bound Column property determines where the control stores its value. You can display many columns, but the control is bound to only one column. While this seems obvious, it's a mistake that's easy to make and hard to find. It's especially likely to occur if you change the Column Count property setting but forget to update the Bound Column setting.

back to top

Correcting #Error

Access returns error messages when it can't return a value for some reason.  One of the most common error messages is #Error.  You'll see this when Access can't evaluate the expression.  You can generally trace the failure to one main reason:  You've supplied the wrong argument or you've omitted an argument. 

  • For instance, the following expression will return #Error:
  • =DCount("[LastName]", "tblEmploye")

Instead of returning the number of employees you have. Why?   Take a close look at the table name--shouldn't the table's name be "tblEmployee" instead of "tblEmploye" (the incorrect reference has only one e)?  A simple mistake like this can be hard to find, but not if you know where to look.  Next time you encounter the #Error message, review your expression's arguments carefully for misspellings and omissions.

back to top

Cutting Out a Reference

If you use DAO for referencing, you're probably familiar with the following statements:

  • Dim db As Database
  • Dim rst As Recordset
  • Set db = CurrentDB()
  • Set rst = db.OpenRecordset("tablename")

You can eliminate a few statements by using the following form:

  • rst = CurrentDb.OpenRecordset("yourtablename")

Making this change shouldn't cause any problems unless you reference the database object somewhere else in your code.

back to top

Data Sheet

Resizing Multiple Columns

Sometimes you may need to adjust the width of a data sheet's columns to view the entire contents of each cell. If it's just one column, a quick fix is to simply double-click the right border of the column's header cell (the row of gray cells along the top). Doing so increases the size of that column so it can accommodate the longest entry in that column. You can also resize several columns at the same time by selecting multiple columns and then double-clicking the right border of any column header cell within the selection.

There are two ways to select multiple columns.

  • You can drag the mouse pointer across the appropriate column header cells, or
  • You can click a column header cell, hold down the Shift key, and
  • Then click the remaining column header cells to add those columns to the selection.
  • If you don't want to use this method, you can position the mouse pointer between any two selected column header cells.
  • The mouse pointer turns into the horizontal double arrow pointer.
  • At this point, drag the borderline between the two column header cells.
  • Access resizes each of the selected columns accordingly.

Using this method, you can also reduce the column width(s).

back to top

Selecting the Whole Cell

It's easy to select a cell in data sheet view--simply click it. However, doing so doesn't select the entire entry or even the entire cell. It selects a portion of the entry, depending on where you click. Access selects the entry up to the cursor. Pressing F8 extends the selection to the end of the current word. In other words, pressing F8 continues to extend the selection--by components--until you've selected the entire cell. For instance, if you click between the P and the I in the entry Apple Pie, Access selects "ie". Pressing F8 extends that selection to include the entire word, "Pie". A second press selects "Apple Pie" and a third press selects the entire cell.

back to top

Fields

Adding Fields to an Existing Form

It's better to include only the needed fields and then go back and add fields later if necessary.

To add a field to an existing form:

  • Click the Field List button to open the underlying table's field list.
  • Open the form in Design View.
  • Drag the field from the Field List to the form.
 back to top

Avoid Using Duplicated Field Names

Access forces you to use unique filed names in each table.  You can, however, use the same names in other tables.  For instance, all your tables can have a field name LastName.   It is recommended to avoid repeating filed names across tables whenever possible.    You'll find your tales much easier to work with if each filed name is unique.

back to top

Changing Data Types

Anytime you need to store decimal values, you must remember to declare the field or variable's data type as Single or Double. If you forget, you can always change the data type later to accommodate decimal values. However, we would like to offer one word of caution about doing so. If you change a data type after you've entered data, you risk losing that data, and you can't undo the damage. Here's what happens: If an existing value is too large for the new data type, Access may truncate, round, or even delete that value. This caution applies anytime you change a field property when data already exists.

back to top

Using the Field List

You may want to add several fields, and dragging them one at a time can get tedious.  You can rely on a few selection tricks to drag more than one field to your form at a time.

  • Double-click the Field List's title bar to select all the fields.
  • To select several contiguous fields, click the first or last item in the block, hold down the SHIFT key, and then click the field at the other end of the block.
  • To select several noncontiguous fields, click the first one, hold down the CTRL key, and then select all the remaining fields one at a time.
back to top

File Management

Compacting Database

Compacting your databases  regularly will improve your application's speed, which defragments your database.  When saving a file, seldom does the file save in one continuous block.  The disk manager starts at the center of the disk and fills in empty blocks of memory with each saved task.  If the first free block isn't large enough to hold the entire file, the disk manager saves only the portion that fits.   Then the disk manager moves on  to the next hole and fills it up.  The disk manager continues in this manner until it has saved the file.  If the files is particularly large, it could be spread all over your disk in several noncontiguous blocks.   As a results, it takes longer to load the file and access information as you work with it.  

Another benefit of compacting is the reduction in the size of your file.  Compacting,  in most cases, significantly reduces the size of your file - especially if you add and delete records on a frequent basis.  It is recommended that you make compacting a regular part of your database routine.

back to top

Filters Slow Down Forms

If you use the Filter By Form feature, you may notice that your form takes a little longer to close.  That's because Access saves the last filter when you exit your form, which slows things down a bit. If your form has a Close or Exit button, you can easily solve this problem.  Simply add the following line of code to your close or exit procedure: DoCmd.Close acForm, "frm_MyForm", acSaveNo.  The acSaveNo argument tells Access not to save the form before it shuts down, so your form closes quickly.

back to top

Forms

Adding Fields to an Existing Form

It's better to include only the needed fields and then go back and add fields later if necessary.

To add a field to an existing form:

  • Click the Field List button to open the underlying table's field list.
  • Open the form in Design View.
  • Drag the field from the Field List to the form.
 back to top

Continuous Forms & Subforms

Continuous forms are great for viewing several records at the same time.  However, you can't view a form in Continuous mode if that form contains a subform, an ActiveX control, or a bound chart.  If your form contains one of these three objects, you must set the main form's Default View property to Single Form or Datasheet.  If you really must view the main form in Continuous mode, try working with two forms--one with the embedded object and one without--and use a pop-up form to display the embedded subform or chart.

 back to top

Opening to a Specific Record

By default, a form displays the first record in the underlying record set.  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.

 back to top

Restricting Data Entry

Access forms, by default, allow users to add new records. An easy trick that inhibits this behavior. You simply add a required field to your form and then don't display that field on your form. That way, the underlying table won't accept a new record because the user can never supply data for the required field. There's an easier way, though, and it's built right into the form.

  • In Design View, open the form's property sheet and choose the No setting for the Allow Additions property.
  • When you return the form to Form View, you'll notice that the New Record navigational button is disabled.
  • Now, your users can edit existing records, but they can't add new ones.
 back to top

Speeding Up Your Form

You're probably always looking for ways to speed up your form performance.  One easy way is to include only those fields you need from the record source.  Generally, it's easier just to include all the fields, whether you're using a wizard or working from scratch to create your form.  However, every field slows things down a bit. So the next time you get ready to include all those extraneous fields, stop.  Think about your form's purpose and add only the necessary fields.  You can always go back and add a field later if it becomes necessary.

 back to top

Generating a Days-Outstanding Value

This tip was sent in by Jonathan Mueller working in TRU's MIS Client Services Department, Paramus.

For you VBA, Access or Excel gurus   this tip is for dealing with dates that may or may not be valid anymore:

In databases that track billing and payment transactions, you'll often want to display the number of days an invoice or other item is outstanding.

  • To generate this value in its basic form, subtract the invoice's creation date from the current date.
  • On a form or report, you could enter the following expression in a textbox's Control Source:
  • =DateDiff("y",[DateCreated],Date())

This expression subtracts the current date from the invoice's creation date and returns the result as the number of elapsed days. By itself, however, this expression continues to generate the days-outstanding value even when the invoice is paid or completed.  To prevent this, use the modification:

  • =Nz(DateDiff("y",[DateCreated],[DatePaidInFull]),
  • DateDiff("y",[DateCreated],Date()))

This expression first subtracts the date in [DatePaidInFull] from [DateCreated].  If [DatePaidInFull] is null, however, then this expression also returns a null value. As a result, Access evaluates the second expression in the Nz() function and subtracts the current date from the invoice's creation date as we explained earlier.

back to top

Getting Rid of a   Blank Record

Most people consider the blank record at the bottom of a continuous form a nuisance. You can  use the Allow Additions property to inhibit the blank record in a continuous form. Of course, this means you can't add more records, but that's not generally a problem. Few people use continuous forms for data-entry purposes. Rather, they use continuous mode to view several records at the same time.

back to top

Importing/Exporting

Exporting Selections

You can easily export Access objects to foreign formats using the Save As/Export command.

  • Select the object in the Database window.
  • Choose File, Save As/Export.
  • When Access opens the Save dialog box, choose the appropriate format from the Save As Type control and continue.

You can also save selections of data instead of the entire object using the same routine. However, instead of selecting the object in the Database window:

  • You'll need to open it and select just the data you want to export.
  • Choose File, Save As/Export.
  • After choosing a Save As Type format, check the Selection option in the Save section.
  • This option won't always be available.
  • If it isn't, try changing the Save As Type format.
back to top

Importing Data

You can import foreign data into an Access table. You can't import just any data, though--Access must recognize the data's format.  Generally, you can easily import data from Excel, FoxPro, dBase, and Approach. You can also import data stored as text.  The key to successful importing is making sure the structure of the foreign data and of the table into which you're importing match.  If the foreign file doesn't resemble your table, you may experience some errors.  Often these are unavoidable. However, editing your foreign data before importing it can save a lot of errors.

Sometimes the Import wizard doesn't always work. Lots of times, Access just doesn't interpret the format of the data and returns errors. When this happens, try to find a go-between format. For instance, if you're having trouble importing data from Lotus Approach, try saving the Approach file in another format, such as dBASE, and then try importing that file instead of the Approach file. Often you'll find Access interprets the data just fine once it's in a more compatible format

back to top

Indexes

Indexes can be confusing to even the experienced Access user. The first thing you should know about an Access index is that an index is really an internal function. Access uses an index to sort data in a logical order; that order is determined by the field's data type. You can define an index, but beyond that, you have little control.

You might think the more indexes, the faster your application--but you'd be mistaken. Indexes, if applied incorrectly, can slow down your application. That's because Access updates an index each time you add or modify a record if you index more than one field.

A few guidelines can help you define efficient indexes. First, primary key fields are automatically indexed. Beyond that, most tables will not require further indexing. If you feel the need, your index candidate should meet all of the following conditions:

  • The field should be a Text, Number, Currency, or Date/Time data type.
  • You should sort or search on the field often.
  • The field should, for the most part, contain unique data.
back to top

Linking to another Database

Creating a link to another database programmatically can involve a lot of referencing and coding, if you use the traditional method. That method involves creating a tabledef object and then using that object's properties to connect and refresh the link. We've got some code that uncomplicated the task.

Next time you need to link, try this procedure:

  • Function LinkToTables()
  • Dim db As Database, cnt As Container, doc As Document
  • Dim strPath As String
  • strPath = OpenDatabase(strPath)
  • Set cnt = db.Containers!Tables
  • For Each doc In cnt.Documents
  • On Error Resume Next
  • DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable,
  • doc.Name, doc.Name
  • Next doc
  • End Function

The For loop cycles through each table in the database specified in the variable strPath. During each loop, the TransferDatabase sets a link to each table.

The above For loop does have one drawback--it also links the system tables. To avoid this, use the following loop:

  • For Each doc In cnt.Documents
  • On Error Resume Next
  • If Left$(doc.Name, 4) <> "MSys" Then
  • DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable,
  • doc.Name, doc.Name
  • End If
  • Next doc
back to top

Measurements

Access allows you to use almost any unit of measure you like when working with objects.  For example, you can specify an object's size in inches or centimeters.  Access itself, on the other hand, uses a measurement known as twips. You can use the following information to help convert a unit of measure to twips:

  • 1 inch equals 1440 twips
  • 1 centimeter equals 567 twips
  • 1  font point equals 20 twips
back to top

Null Values

Don't let Null values intimidate you--they can be confusing even for the experts. Fortunately, a few basic principals should help you out.

  • A Null value indicates one of two conditions:
    • The value is missing or unknown.
    • The value doesn't apply.

When a value is missing or unknown, it simply means the information may exist--we simply don't know it. Occasionally, the information doesn't apply to a particular record and you must leave a field blank.

Let's suppose you've got a table of phone numbers and two fields are blank.

  • In one case, you know the person has a phone--you just haven't been able to acquire the number yet. Eventually, you may fill that field.
  • In the second case, you know the person doesn't have a phone.
  • That field will remain empty (unless the person gets a phone). In each case the field returns a Null value.
  • When there is no phone (versus no phone number), consider using an empty string ("") or a string value such as "no phone" or "N/A" so you can readily tell the difference between an unknown phone number and no phone.

back to top

Using Null Values vs. Zeroes

You need to be careful about using null values in place of zeroes in Access.

  • Access will try to add a number and come up with null.
  • For example, 25 + 0 is 25, but 25 + null is null."
back to top

Primary Key Field

You cannot leave a field blank if that field is a primary key field. A primary key value uniquely identifies each record.  It stands to reason that you wouldn't want to leave a primary key field blank, so Access simply doesn't allow you to.   While designing your tables, be sure the field you choose as your primary key field can stand up to this Null test.  On the other hand, foreign key fields can contain blanks, but you'll rarely need to leave a foreign key field blank.

back to top

Number of Characters in an Expression

Did you know that there's also a limit on the number of characters you can use in any expression?  An expression can contain up to 2045 characters. While this should be adequate most of the time, it is possible to write an expression 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.

back to top

Objects

Object Properties

Most objects have two properties that are easy to confuse--the Name and Caption properties.

  • The Name property is the string you use to identify an object.
  • The Caption property is the string you display on the object.

Let's compare the two properties on a simple command button. You might name the button cmdButton. When you refer to the button in events or other code, you'd use the Name property string--cmdButton. On the other hand, if this same cmdButton's task is to print a specific report, cmdButton's Caption property might be Print Report. As you can see, the two property settings aren't interchangeable, but we can understand how people might get them confused.

back to top

Updating Code

Changing the name of an object in Access is a not easy;   Access doesn't update all your references; you must do that yourself. Invariably you miss a few, and they'll eventually cause trouble. Fortunately, updating these references in your modules is fairly easy. Simply use the Replace feature.

  • Open your module and click the Find button on the Visual Basic toolbar.
  • Enter the object's original name in the Find What control and then click the Replace button.
  • In the resulting window, enter the object's new name in the Replace With control.
  • Then click Find Next to run the feature.

Access highlights each occurrence of the original name and asks if you want to replace it. Updating object names this way is quick and thorough.

back to top

Percentage/Top Values

You can  return a percentage of records using the Top Valuesproperty.

  • Simply include the percent sign (%) when you enter the value.
  • For instance, if you want to see the top 5 percent of the records, you'd enter 5% as the Top Values setting.
back to top

Printing Options

Print Preview the Easy Way

Did you know that you can use Print Preview from the Database window and do not have to open the object first.  Instead of opening the object and  then previewing it:

  • Select the object in the Database window and then click Print Preview on the Database toolbar.

You can view most of your objects in Print Preview, simply by selecting the object you want to view in the Database window and clicking the Print Preview button, located on the toolbar.  If the button isn't available, choose Print Preview from the File menu.

Previewing an object in this manner give you a quick look at your object, its structure, and in some cases, its data.  The benefit is saved time and paper. 

 Another advantage of Print Preview  is the capability to print just one page of a report instead of the entire report.

  • Find the page you want to print.
  • Choose File, Print.
  • Indicate the page by number in the Print Range options.

This is a great way to save time and paper when only a portion of a report is needed. You don't have to create a new report, nor do you have to print the entire report--simply print the needed pages from an existing report.

back to top

Queries

A Better Query

You might think one is more efficient than two, but that's not always the case.  For instance, if you're running a Totals query that includes a JOIN, you might consider breaking that query into two parts:

  • Group the records in the first query
  • Base the JOIN query on the above query

In this particular case, the two queries are usually faster than the comparable lone query.

back to top

Combining SQL Statements to create Union Queries

A union query combines similar tables. The resulting set is never wider than either table, but generally contains more records than either of the underlying queries or tables, since the purpose is to combine the two tables. For instance, let's suppose you have historical data for the last five years in one table, and you base two queries on that table. One query returns all the records for 1998; another returns all the records for 1994. To combine the two queries, you'd create a UNION query. Y

  • Open the first query in Design View and choose SQL View from the View button.
  • Copy the SQL statement to the Clipboard.
  • Open the second query in Design View and choose SQL View from the View button.
  • Position the cursor at the end of the statement and delete the semicolon.
  • Enter a space character and then the SQL clause Union.
  • Paste the first SQL statement from the Clipboard to the SQL Select Query window.
  • Run the query.

Of course, if you know the exact SQL statement, the hard way is really the easier way. However, few of us get a SQL statement right on the first try, so if you're not an expert, our easy way might be the easiest way for you. Just remember--this only works with UNION queries.

back to top

Finding the Highest & Lowest

You can sort the results of a query to position a high or low value at the top of your record set. Simply set the Sort order in the query design grid for the field you want to sort by. If you'd like to return just one record--either the highest value or the lowest value in a particular field--you'll need the Top Values property.

Let's suppose you want to see the largest order for a specific time period. In your query design grid, you might include the order number, the company, the salesperson who made the sale, the date he or she made the sale, and the total amount of the sale. To specify a specific time period, you'd enter a criteria expression in the Date field's Criteria sale. Then you'd assign the Descending sort property to the Amount Of Sale field.

Now you're ready to set the Top Values property. First, right-click the query design grid (click the background, don't click a field). In the resulting shortcut list, choose Properties to open the query's property sheet. Find the Top Values property and enter the value 1. Then run the query to return the number one sale for the specified time period. You can also return the lowest sale. Simply change the sort order to Ascending and run the query again.

Just because you set the Top Values property to 1 doesn't mean you'll return just one record. If all the criteria are met and the sort field has more than one record with the same value, the query returns those records. For instance, our previous example could return more than one record for the number one order if another order of the same amount exists within the same time period. Of course, in this example, the chances are slim that this will happen.

back to top

Running a Query from a Macro

You probably know that you can use the OpenQuery macro action to run an existing query. What you might not realize is just how easily you can create that macro.

  • Simply drag the query in question from the Database window to a blank Action cell in an open Macro window.
  • Access defines the appropriate property settings to run the dropped-in query.
back to top

Saving a SQL Statement as a Query

  • Open the form or control in Design View.
  • Locate the Record Source property and click the Builder button to the right.
  • When the SQL Query Builder opens, simply save it as you would any other query.
  • You can click the Save button on the Query Design toolbar or choose Save As from the File menu.
  • Substitute the SQL statement in the Record Source property with the query.
back to top

Total Query for Multiple Criteria

Need a total based on criteria? A Totals query works fine until the criteria involve a range of values instead of just one. For instance, let's suppose you want to sum percentage values by regional status. If you just want to sum percentages for all the records that pertain to one region, a Totals query will work just fine.

If you want to sum all the percentages for more than one region, you may run into difficulty. A Totals query will return the result you need--you just need to rethink the criteria. For instance, in this situation, you'd simply group the Totals query by the Region field and Sum the Percentage field. To narrow the criteria, you'd still select Sum from the Percentage field's Total field.

But here's the trick: Specify the criteria in the Percentage field's Criteria cell. For instance, to sum all the percentages for Kentucky, Indiana, and Ohio, you'd use an expression similar to this: [Region] = "KY" Or "IN" Or "OH"   The results of the query will be one value--the sum of the percentage values in Kentucky, Indiana, and Ohio.

back to top

 

Using the Hourglass Method

Most tasks run lickety-split in VBA, but occasionally we run across a procedure that takes a little more time than expected. This can happen when you're working with a complex query or an especially large record set. When this happens, your user won't really know what's going on and might try to continue working. Anytime you find your code ties up processing, use the Hourglass method to display the hourglass mouse pointer. That way, your user knows something is happening behind the scenes and waits (you hope).

To display the hourglass pointer:

  • Position the statement DoCmd.Hourglass True just before the code that takes a while.
  • At the end of the code, include the statement DoCmd.Hourglass False to turn off the hourglass mouse pointer.
back to top

Record Count

  • The RecordCount property requires a Long data type.
  • If you assign anything else, your code will return an error.
  • This unfortunately isn't intuitive, but the explanation makes sense.
  • An Integer data type handles values from -32,678 to 32,767, but a table can store many more records than 32,767.
  • By enforcing the Long data type, VBA ensures there will be no problems returning the correct record count  owners.
back to top

If FindNext Doesn't Work

You can use the Recordset object's FindNext method to locate records that match given criteria. Simply use the form: rst.FindNext criteria where criteria is a SQL WHERE clause without the WHERE. For instance, you might use the statement rst.FindNext "[LastName] = 'Smith'" to find the next record with the string Smith in the LastName field.

However, occasionally these statements don't work, and you can waste a lot of time trying to figure out what's wrong with the criteria component. Most likely there's nothing wrong with your criteria component. Rather, you've not defined the correct Recordset type. You need to define the Recordset as a Dynaset using the form: db.OpenRecordset("tablename", dbOpenDynaset)

If you omit the type argument for your statement, Access opens a table-type Recordset (dbOpenTable) unless you specify a linked table or a query. In those cases, the default is the Dynaset-type. So before you waste a lot of time trying to restate your criteria expression, check the Recordset's type.

back to top

Quick Record Count

The Recordset object has a few properties, and one of those properties is RecordCount.  As its name implies, this property returns the number of records accessed by the Recordset object. (The TableDef object also supports the RecordCount property.)

To get an accurate count, you can use the MoveLast method to access the last record and then assign the RecordCount property to a variable using a code snippet similar to the following:

  • Dim db As Database, rst As Recordset, lRecordCount As Long
  • Set db = CurrentDB
  • Set rst = db.OpenRecordset("tablename", dbOpenDynaset)
  • rst.MoveLast
  • lRecordCount = rst.RecordCount
back to top

Using Count

Did you know that Count() is really a SQL function and not one of Access's native functions?

  • You can use it to count the number of entries in a given field using the form
  • Count([fieldname])

However, if you're not using a traditional SQL statement in which you can also specify criteria, you should consider using the syntax Count(*) The second form is much faster than specifying a particular field name.

back to top

Relationships

Automatic Relationships

Relationships are the real power behind Access. Relating tables allows you to omit redundant data, which saves resources and your time.

  • One way to create a relationship is to open the Relationships window and simply drag a field from one table list to another.
  • However, Access will create the relationship for you if the following conditions are met:
    • Both tables contain the same field.
    • Both fields have the same data type and field size.
    • One of the fields is a primary key.

If you don't want Access to create relationships automatically, you can turn this feature off.

  • Choose Tools from the Options menu.
  • Click the Tables/Queries tab, and deselect the Enable AutoJoin option.
back to top

Relationship Window

You can create a relationship with the Relationships window.

  • You simply open the window by choosing Relationships from the Tools menu.
  • Once you've displayed the window, you add the necessary tables to the window.
  • Then drag one field to another as necessary to create the relationships you need.
back to top

Reports

Reporting the Easy Way

There are several ways to create an Access report--you can use a report wizard or you can build it from scratch. If you have a form based on the same table or query, you might be able to use a different 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).
  • You can select Copy from the Edit menu or press the CTRL+C keys to copy the selected controls to the Clipboard.
  • Choose Report from the New Object button's drop-down list.
  • In the New Report dialog box, select Design View and click OK.
  • Then select Paste from the Edit menu or press the CTRL+V keys 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 time-saver.

back to top

Rounding Decimal Values

If you're rounding decimal values to an integer, you don't really need special expressions for rounding. Simply assign the value of your procedure as an Integer data type.

Function BMI(pounds As Integer, feet As Integer, inches As Integer) As Integer

By assigning the entire procedure to an Integer value, we limit our function to returning only Integer values; therefore, it rounds automatically. If the result of the expression is 21.45, the function returns 21; if the result is 22.55, the function returns 23.

The function doesn't care how many decimal values the expression returns. While this isn't a typical programming approach, it is a quick solution in the right situation.

back to top

Sort Order is Saved

Did you know that Access saves the last sort order in a form, query, or table? That means the next time you open that object, Access will remember the sort order you chose last and sort the object accordingly. For instance, the default sort order is ascending.

  • If you're viewing a set of records in Form View and click the descending sort button, Access re-sorts your records.
  • If you then close the form
  • without re-sorting the records in ascending order, the next time you
  • open that form, you'll see your records in descending order.

This behavior is also true of tables and queries.

back to top

Spreadsheets are for Excel

If you're new to Access, you may be making a mistake that's fairly common to new users.  That mistake is wanting your tables to resemble spreadsheets.   Keep in mind that a table isn't a spreadsheet at all, just a means of storing data.    If you dump all of your data into one table, you're missing the point--and power--of using a relational database.

If you suspect you might be storing data incorrectly, check for redundant data.  Do you often repeat the same information, such as a customer's address?  If so, you're probably using Access tables as you would an Excel spreadsheet.

How do you fix the problem?  Apply the following rules of normalization when creating tables:

  • All fields must be atomic, which means you cannot divide the data any further.
  • All fields must refer to a key field--a primary or foreign key.
  • All fields must be independent of one another.
  • A primary key field contain a unique value for each record. 
  • A foreign key stores primary key data in a related table.

Learning how to moralize data can seem a bit intimidating, but it's easy once you get the hang of it.  The best way is to read about it and then practice. 

back to top

SQL

Creating SQL Statements

SQL statements are one of the more difficult components of VBA code to work with. There are a lot of rules, particularly when it comes to delimiting strings and concatenating variables. A good starting point is to let Access build as much of the statement as you can. How? There are two ways.

  • First, when working with record sets, you can open the query design grid and create a close proximity to the situation you are trying to create with code.
  • Then click the View button and choose SQL.
  • Copy the SQL statement from the SQL window to your module and revamp as necessary.

 

  • The second way involves the SQL Statement Builder, which you sometimes use when setting a control's Row Source property.
  • Once you've launched the builder (from a Row Source property, most likely) and designed the query, open the SQL window and copy the SQL statement to your module.

As a rule, neither method will give you the exact statement you need, but it's a good place to start.

back to top

DeBugging SQL Statements

Here's a good rule to follow when debugging it. 

  • Position a break point after the statement that contains your SQL statement, then run the code.
  • If your code produces an error at the SQL statement (and it probably will if you're like most of us), open the Debug window by pressing Ctrl-G and enter the statement ?strSQL
  • Where strSQL represents the variable that contains your SQL statement.
  • Copy the resulting string into the SQL window of a new query.
  • Then try to run the query.
  • Access displays an error message regarding the SQL statement that should help you pinpoint the problem.
back to top

Controlling SQL Strings

When  copying a SQL string from the SQL window to a module window, it's usually in block form instead of being one long statement.  As a consequence, you then have to assemble all the clauses on one line, and doing so often produces 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.

  • If the SQL statement comprises three separate lines, don't pull the second line around to the first.
  • Then pull the third line around to the first.
  • Instead, pull the third line around to the end of the second by positioning the cursor at the beginning of the third line and pressing Backspace.
  • Then pull the combined second line around to the first line.
back to top

Faster Queries

It's common to use a SQL statement as a combo or list box's Record Source. You'll often see SQL statements used as a form's Record Source property. However, a SQL statement can slow things down a bit. That's because the Jet optimizer preoptimizes a saved query. In contrast, this doesn't happen with the SQL statements, which means they require optimizing over and over again at runtime. If your application contains many of these statements, you may want to reconsider your design. However, most applications won't show much improvement, so only consider this change if your forms or controls are slow enough so your users notice the wait.

back to top

Saving a SQL Statement as a Query

  • Open the form or control in Design View.
  • Locate the Record Source property and click the Builder button to the right.
  • When the SQL Query Builder opens, simply save it as you would any other query.
  • You can click the Save button on the Query Design toolbar or choose Save As from the File menu.
  • Substitute the SQL statement in the Record Source property with the query.
back to top

Using Count

Did you know that Count() is really a SQL function and not one of Access's native functions?

  • You can use it to count the number of entries in a given field using the form
  • Count([fieldname])

However, if you're not using a traditional SQL statement in which you can also specify criteria, you should consider using the syntax Count(*) The second form is much faster than specifying a particular field name.

back to top

Toolbars

Default Status Bar Message

When creating a new table, have you noticed the Description field to the right of the Data Types field?  You can enter descriptive or explanatory text in this field.  You might find this extra step unnecessary.  However, if you knew you could use it to display a message in the Status Bar automatically, would you pay more attention to it? Well, that's how it works. In Form View, Access displays the contents of a field's Description field when that control has focus.  Not only can you use this as a means of explaining your field, you can also communicate information to your users.

back to top

Docking Toolbars

In Access 97, toolbars are very easy to modify and to position.  There are two positions, docked and floating toolbars. 

  • A docked toolbar is attached to the top, bottom, or one of the sides of the Access application window.  Although you can move a docked toolbar, you must undock that toolbar first. 
  • A floating toolbar resides inside the Container window and moves easily.

You can alternate between a docked and floating toolbar by simply double-clicking the toolbar. 

  • If the toolbar is floating, double-click its title bar to dock it. 
  • To undock the toolbar, double-click the raised handles at the toolbar's left edge.
back to top

Resetting Toolbars

To reset the toolbar to its default settings:

  • Right-click the toolbar and choose Customize from the resulting shortcut menu.
  • Click the Reset button in the Customize dialog box.
  • Remember resetting the toolbar doesn't undo just the last change; rather, it returns the toolbar to the original settings it had when you installed Access (or Office).
  • If you've made a lot of custom changes you don't want to lose, resetting the toolbar probably isn't a good idea.
back to top

Updating Code

Changing the name of an object in Access is a not easy;   Access doesn't update all your references; you must do that yourself. Invariably you miss a few, and they'll eventually cause trouble. Fortunately, updating these references in your modules is fairly easy. Simply use the Replace feature.

  • Open your module and click the Find button on the Visual Basic toolbar.
  • Enter the object's original name in the Find What control and then click the Replace button.
  • In the resulting window, enter the object's new name in the Replace With control.
  • Then click Find Next to run the feature.

Access highlights each occurrence of the original name and asks if you want to replace it. Updating object names this way is quick and thorough.

back to top

Uppercase Letters

There are a couple of ways to display text in uppercase letters.

  • The easiest way is to use the > code as a field's Format property.
  • You can also use VBA's UCase() function. As an example, open the Debug window
  • and enter the statement:   ? UCase(This sentence is in uppercase) .
  • When you run the statement, Access will return the string THIS SENTENCE IS IN UPPERCASE.
back to top

Variable Doesn't Work?

When a value doesn't display or consider a decimal component in a calculation--and you know it should--check that value's data type. The value must be a Single or Double data type to store a decimal component. This is a common mistake, and it's easy to make. While it's obvious that the Integer and Long Integer data types both store integers, we use them so often that's it's easy to enter Integer or Long without realizing the repercussions. Another problem is that we don't always know during the design stage that a variable might need to accommodate a decimal value.

back to top

VBA

Property or Method?

If you're fairly new to VBA, you may be a little confused about properties and methods, since the syntax for both is similar.  By that, we mean that you separate both a property and a method from the object with the dot identifier (.).  For instance, to set a property, you'd use the form: object.property

  • To execute a method,  you'd use the form: object.method
  • They look pretty much the same, don't they? However, their functions are very different. Properties represent an object's attributes, and methods represent that object's behavior.
  • In other words, if you want to make a font blue, you'd set the object's Fore or Font property to a value that represents blue in the form: cmdButton.Fore = 16711680

Methods, on the other hand, generally execute some behavior or action. For instance, the Requery method updates the data underlying a specific form (or object). Requerying a form might require a statement similar to: frm.Requery

back to top

 

Running an Event with the Event

  • When you're adding VBA code to an event, you'll probably want to test the code often. This can be a nuisance if you have to execute the event each time you want to test the subprocedure. Fortunately, you don't have to. When you're ready to test event code, simply press F5.
  • This button has the same effect as choosing Go, Continue from the Run menu.
  • You can also click the Go, Continue button on the Visual Basic toolbar.
back to top