Place your ad here! This domain gets 400 visitors per day and has Google Page Rank of 3 or 4. Get great ad rates!
Place your banner ad here!
Get more traffic coming to your Web site today! Advertise with us! We get 12,000 visitors & 27,000 page views per month and have Google Page Rank of 3 or 4.
What are "gem" tips? These tips are the gems that experienced, expert Access developers have found to increase their productivity, prevent common errors, and help prevent database corruptions. Why do we include corruption prevention in this section? Because it's usually the Access developer -- the expert on the database -- who is tasked with rebuilding the MDB file whenever a database becomes corrupted. Developers busy fighting fires lose productivity. Take steps to avoid database corruption whenever you can.
If you heed the advice from these experts, you'll find that your Access development time will speed up, and database application maintenance won't be nearly as difficult or time-consuming as it used to be. Here are several gems to get started with:
Option Explicit is used to require the declaration of variables in VBA (Visual Basic for Applications) code. For example, you might declare a variable like this:
Dim strCustLastName As String
but inadvertently misspell the variable in code when you are using it:
strCostLastName = Me!txtCustLastName
VBA will correct for undeclared variables at run-time if you have not used Option Explicit. However, it does this by creating variant variables. Variants are the least efficient of any of the variable data types. Using Variant variables -- when you think your are using a different variable data type -- can lead to hard-to-catch run-time errors in code, as well as inaccurate calculations. Without the use of Option Explicit, you may never catch these errors.
Unfortunately, the VB Editor is not configured to include Option Explicit in code modules by default. You can change this behavior for all new code modules (this does not affect any existing modules) by opening the VB Editor, then selecting the Tools -> Options menu. Select the Editor tab and place a check in the "Require Variable Declaration" check box. Now all future code modules will automatically have the "Option Explicit" statement placed prior to the first procedure within the module.
Statement from Microsoft
"However, every experienced developer knows that the failure to use the Option Explicit statement to force explicit variable declaration is a coding blunder of the highest order. Using undeclared variables can introduce subtle, hard-to-find bugs into your code that are easily avoided by using this one, simple technique."
While you have the VB Editor Options dialog window open, I recommend deselecting "Auto Syntax Check." This is a rather useless option in my opinion, since it just causes one to have to click "OK" to accept an error message when there is a syntax error. These syntax errors show up in red font (by default) anyway, so one doesn't really need an extra message box to learn about them.
Avoid "Compile On Demand" -- By Tom Wickerath
On the General Tab of the VB Editor Options dialog window, I recommend removing the check in the "Compile on Demand" option. Michael Kaplan, a Microsoft employee who is a former member of the Access Development team (and an absolute wizard at Access), has stated that this option is known to cause troubles, where code that was deleted seems to run as if it wasn't deleted.
You should make these changes to the VB Editor option settings on every PC that you use to do Access work. You should only need to make these changes one time, unless the need comes up to reinstall the Microsoft Office software.
Four very useful toolbar buttons for the VB Editor -- By Tom Wickerath
These four toolbar buttons can be real time savers in the VB Editor:
They allow you to work with entire blocks of code (i.e., several lines selected at once) to "Indent," "Outdent," "Comment Block" and "Uncomment Block."
For example, suppose you want to experiment with a subroutine or function. You might first decide to back up your entire database, so that in case something goes wrong, you can easily recover. A quicker method is to copy the procedure and then comment out all of the lines of code in the back-up copy using the third button from the left, the "Comment Block" button. If you need to restore the code to its original condition, you simply delete the copy you have been working on, select all of the lines in the commented out back up copy, and then click on the last button on the right, the "Uncomment Block" toolbar button.
To add these buttons to your toolbar, open the VB Editor, then select the View > Toolbars > Customize... menu item.
Select the Commands tab.
Select the Edit category.
Scroll down the list of commands. About halfway down the list, you should see these four toolbar buttons.
One-by-one, drag each of them to your toolbar and drop them at the location where you want them to be placed.
Select the "Close" button on the "Customize" dialog window when you are finished.
Useful VB Editor toolbar buttons for compiling code and viewing other windows -- By The Gunny
Speed up the writing of VBA code by placing many of the most commonly used built-in menu items on the VB Editor's "Standard" toolbar. Save, Find, and Object Browser buttons are already on the "Standard" toolbar, but do you also have Compile, Last Position, Definition, and View Object buttons on your toolbar?
From left to right, the buttons are: Save, Compile <Project Name>, Find, Object Browser, (Return to) Last Position, View Definition, and View Object.
Compile -- After making changes to the code, the code should be saved and compiled, but to compile the code requires selecting the Debug -> Compile <Project Name> menu. Placing the Compile button next to the Save button on the toolbar makes this step even faster.
Last Position -- After editing multiple code modules or searching through the code using the "Find" feature, pressing <CTRL><SHIFT><F2> or selecting the View -> Last Position menu will place the cursor back on the line of code previously edited or viewed so that the user doesn't need to manually locate the module and then scroll to the previous line of code.
View Definition -- To view a procedure's formal parameters and definition, the user highlights the procedure name within a line of code and presses <SHIFT><F2> or selects the View -> Definition menu to jump to the first line of code in the code module that contains the procedure definition.
View Object -- To return to the Access window from the VB Editor, the user usually presses <ALT><F11> or selects the Access window button on the toolbar, then finds the object of the corresponding code module being worked on if it wasn't the last object viewed in the Access window. A better way is to use the "View Object" feature to return directly to the object in the Access window. Pressing <SHIFT><F7> or selecting the View -> Object menu will set focus to the corresponding object in the Access window, regardless of whether or not it was the last item viewed in that window, because that's the code window that currently has the focus.
To add these four buttons to your VB Editor's "Standard" toolbar:
Right-click on any command bar and select the "Customize ..." menu item from the popup menu.
Select the "Commands" tab in the Customize dialog window.
Select "Debug" in the "Categories" list box.
Select "Compile Project" in the "Commands" list box, drag it to the "Standard" toolbar, and place it next to the "Save" button.
Select "View" in the "Categories" list box.
Select "Object" in the "Commands" list box and drag it to the "Standard" toolbar.
One-by-one, drag the "Definition" and "Last Position" items from the "Commands" list box to the "Standard" toolbar.
Select the "Close" button on the Customize dialog window when you are finished.
You'll notice that there are no icons for the "Definition" and "Last Position" buttons. To change this, right-click on the "Definition" button on the toolbar and select "Change Button Image" to select any built-in button image. Right-click on this button again and select "Default Style" on the pop-up menu to remove the text and allow only the icon to show. Repeat the same steps with the "Last Position" button to place an icon on the button. As an alternative, please see the following Web pages for instructions to use custom button images instead of the built-in button images (as I've done on my buttons in the picture above):
Useful toolbar buttons for repositioning/resizing groups of controls quickly -- By D.C. Conlin
Do you often find yourself moving groups of controls around your form or report, or just wanting to make all of these controls the same size quickly? If you use the Microsoft Access built-in "Format" menu, then you'll have to navigate your mouse through the menu multiple times if you need to "bump" the controls in a certain direction more than once. Even if you use the keyboard, you'll need three keystrokes for each "bump" or resizing. The quicker way is to customize your Formatting (Form/Report) design toolbar with the buttons that would otherwise require the most time-consuming navigations through the menu to make format changes.
The following customized toolbars show the most common alignment, spacing, sizing and layer positioning buttons used for customizing groups of controls on a form or report:
To quickly add these buttons to your toolbar:
Right-click on any command bar and select the "Customize ..." menu item from the popup menu.
Select the "Format" menu on the menu command bar.
Select the Align menu item.
Press the <CTRL> key while dragging the "Left" align submenu item to the location on the toolbar where you want to place it.
One-by-one, drag the rest of the alignment submenu items ("Right," "Top," and "Bottom") from the "Format" menu to your toolbar while pressing the <CTRL> key.
Follow these same steps to drag the submenu items from each of the "Horizontal Spacing" and "Vertical Spacing" menu items from the "Format" menu to your toolbar.
When finished, select the "Format" menu again.
Press the <CTRL> key while dragging the "Bring to Front" menu item to the location on the toolbar where you want to place it.
Follow these same steps to drag the "Send to Back" menu item to the location on the toolbar where you want to place it.
When finished, select the "Format" menu again.
Select the Size menu item.
Press the <CTRL> key while dragging the "To Tallest" submenu item to the location on the toolbar where you want to place it.
Right-click on the "To Tallest" button you just created to bring up the popup menu.
Select the "Name" menu item on this popup menu.
Change "To &Tallest" to "&T"
Follow these same steps to drag the "To Shortest," "To Widest," and "To Narrowest," submenu items to the locations on the toolbar where you want to place them and change the "Name" displayed on each new button to "S," "W", and "N," respectively by using the popup menu.
Follow the appropriate steps for any other buttons you wish to display on your toolbar from the "Format" menu items.
Select the "Close" button on the Customize dialog window when you are finished.
Now, instead of navigating the menu three times to squeeze the horizontal space between a group of controls, you can just select the group of controls, then quickly click the "Decrease Horizontal Spacing" toolbar button three times with your mouse.
Remove the default setting that automatically creates indexes for any field that includes ID, Key, Code, or Num at the beginning or end of a field's name whenever a new table is created or imported into the database. Select the Tools > Options... | Tables/Queries tab and clear the text box for the "AutoIndex on Import/Create:" option. This option creates too many duplicate indexes for tables and allows Access too much power to silently modify the schema of your tables.
To prevent automatic indexes in Access 2007:
1. Click on the Office button in the upper left corner.
2. Click on the "Access Options" button in the lower right:
3. Select "Object Designers." Delete the entries indicated below:
