Learn how to select cells in large tables using the keyboard, find a value in a data range with an array formula, and make Access reports easier to read with alternate line shading.
Select cells in large tables using the keyboard
As the size of a table increases, the harder it becomes to use the mouse as your sole means of navigation in a table. For example, to select a column with the mouse, you need to move the pointer along the top gridline of the first cell in the column until it changes to a down arrow and then click.
However, using the keyboard simplifies this process. Position the pointer anywhere in the column, press [Alt], and select any cell. To use the keyboard to select an entire table, click anywhere in the table and, with Num Lock off, press [Alt]5 on the numeric keyboard.
Like Excel, Word XP and Word 2003 also let you press [Ctrl] to select nonadjacent cells. For example, to select columns 1 and 3 using the keyboard, press [Alt], click somewhere in column 1, press [Ctrl][Alt], and click somewhere in column 3.
Find a value in a data range with an array formula
You can use Excel's built-in Find feature to determine whether a specific value appears in a range of cells. But this isn't your only option; you can also use an array formula.
For example, let's say you have a spreadsheet that lists names, addresses, and phone numbers in the range A3:H110. You've named this range Customers, and you use cell B1 (named NamedEntry) to enter the name you wish to verify.
Follow these steps:
If you enter "Mary" in B1, the formula compares the value Mary to each cell in the Customers range. If there is a match, the formula displays Found in C1; if there is no match, it displays Not Found.
Make reports easier to read with alternate line shading
Financial reports that list scores of records with many columns of numbers can be difficult to follow. However, you can make these reports easier to read by adding alternate line shading.
Follow these steps:
Const vbLightGrey = 12632256
If Me.CurrentRecord Mod 2 = 0 Then
Me.Section(acDetail).BackColor = vbLightGrey
Me.Section(acDetail).BackColor = vbWhite
When you run the form, Access will use a white background color for odd records and a light gray background color for even records. Please note that the background color of any text boxes in the record will remain the same.