Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Decompile

In Windows XP…

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "YourDriveLetter:\PathToYourDatabase\YourDatabase.mdb or accdb" /decompile

In Windows 7 64 bit…

"C:\Program Files (x86)\Microsoft Office\YourOfficeFolder\MSACCESS.EXE" "YourDriveLetter:\PathToYourDatabase\YourDatabase.mdb or accdb" /decompile

Loading

Subscript Out of Range

Several reasons for this… Could be the Excel spreadsheet has too many columns, could be there is some corruption in the Excel spreadsheet, could be the way Access is translating your columns in Excel, could be there are calculated fields in Excel that Access can’t translate for whatever reason. Review the checklist below and see […]

Loading

Add *ALL* to Combo Box

SELECT YourFieldName FROM YourTableName UNION Select "ALL" as Bogus FROM YourTableName;

Then set the Default Value for the Combo Box as “ALL”

Loading

DLookup causes “You cancelled the last operation” error message

The message indicates that the 3rd argument you are supplying is not correct. It may be misspelled, lacking square brackets around problem names, lacking delimiters, lacking a value (e.g. where you concatenate a Null), lacking an operator, or something else.

This article explains how to form the 3rd argument… Getting a value from a table: […]

Loading

When do Tab Controls fire?

The Tab Control On_Change Events fires when you click on (and change) the tab page you are on The Tab Control On_Click event fires if you click in the tab area where there is no tab The Page On_Click event fires (for the selected page) when you click in the area below the tab […]

Loading

Persistent Connection explained

You just went grocery shopping and your car is full of groceries. Which is faster?

Take bag from car, open door to house, put bag inside house, go back out door closing and locking it behind you, repeat as many times till done

OR

And, here’s the persistent connection part… Take bag from car, open […]

Loading

UPDATE Query Samples

Simple UPDATE query…

UPDATE YourTable SET YourTable.YourField1 = "Your Value";

Update a field in your table with values from another field in your table…

UPDATE YourTable SET YourTable.YourField1 = [YourField2];

Update a field based on a field on your form… Note: The form must be open to perform this action!

TEXT

UPDATE YourTable SET YourTable.YourField1 […]

Loading

Refer to Controls on Forms/Subforms

It’s always a nightmare when you’re trying to reference a control from one Form to another or one Report or another. You think you’ve typed it correctly but it’s still not returning the value. Here are two ways to get that accomplished…

From The Access Web… Refer to Form and Subform properties and controls

OR

[…]

Loading

#NAME?

Here are some reasons…

Make sure that the field specified in the control’s ControlSource property hasn’t been removed from the underlying table or record source. Check the spelling of the field name in the control’s ControlSource property. If you specified an expression in the control’s ControlSource property, make sure that there is an equal sign […]

Loading

FollowHyperlink to file regardless of File Extension

To open a file regardless of the File Extension you can use…

The below is run from a Command Button on a Form the has a Control listing all the files in said directory.

Windows XP…

Dim strFileName As String strFileName = Dir("C:\Documents and Settings\Regina Whipp\My Documents\YourFolcer\" & Me.FieldOnYourFormThatHasFileName & "*") Application.FollowHyperlink ("C:\Documents and Settings\Regina […]

Loading