Microsoft Access Programmer In Huntington, West Virginia

Using Microsoft Access Option Explicit

This technical information comes directly from our owner and principal programmer, Alison Balter. We don't expect you to become an expert in Microsoft Access - this material is presented so you can see for yourself the expertise and technical Microsoft Access programming skills we offer all our clients. When you need any Microsoft Access prgrammer services, contact MS Access Solutions at (323) 285-0939 for rapid response and expert Access programming.

The material below originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted with the author's permission. There may be references to "Figures" or "Chapters"that are not reprintable and are not used on this page.

---

Option Explicit is a statement that you can include in the General Declarations section of any module, including the Class module of a form, or report. When you use Option Explicit, you must declare all variables in that module before you use them; otherwise, an error saying that a variable is undefined will occur when you compile the module. If Access encounters an undeclared variable when it compiles a module without Option Explicit, VBA will simply treat it as a new variable and continue without warning. At first glance, you might think that, because Option Explicit can cause compiler errors that would otherwise not occur, it might be better to avoid the use of this option. However, just the opposite is true. You should use Option Explicit in every module, without exception. For example, look at the following code:
intAmount = 2
intTotal = intAmont * 2

Clearly, the intent of this code is to multiply the value contained in the variable intAmount, in this case 2, by 2. Notice, however, that the variable name is misspelled on the second line. If you have not set Option Explicit, VBA views intAmont as a new variable and simply continues its processing. The variable intTotal will be set to 0 instead of 4, and no error indication will be given at all. You can totally avoid this kind of result by using Option Explicit.

TIP

In earlier versions of Access, you had the option of globally instructing Access to insert the Option Explicit statement in all new modules. In Access 2007, ]the default setting is to insert the Option Explicit statement in all new modules. To review this setting in Access 2007, with the VBE active, choose Tools, Options. Under the Editor tab, click Require Variable Declaration (see Figure 8.4). It's important that you place the Option Explicit statement in all your modules, so make sure this option is set to True. Option Explicit will save you hours of debugging and prevent your cell phone from ringing after you distribute your application to your users.

In addition to a General Declarations section and user-defined procedures, forms, and reports, Class modules also contain event procedures that are associated with a particular object on a form. Notice in Figure 8.5 that the Object drop-down list says cmdHello. This is the name of the object whose event routines you are viewing. The drop-down list on the right shows all the events that you can code for a command button; each of these events creates a separate event routine. You will have the opportunity to write many event routines as you read through this book.

Creating Event Procedures

Access automatically creates event procedures when you write event code for an object. For example, Access automatically creates the routine Private Sub cmdHello_Click when you place code in the Click event of the cmdHello command button, shown in Figure 8.5. To get to the event code of an object, follow these steps:

1. Click on the object in Design view and click the Property Sheet button on the toolbar, or right-click on the object and choose Properties from the context-sensitive menu.
2. Click on the Event properties tab.
3. Select the event for which you want to write code (for example, the On Click event).
4. Select [Event Procedure] from the drop-down list.
5. Click on the ellipsis button, which places you in the VBE within the event code for that object.

You are now ready to write code that will execute when that event occurs for the selected object.

---

This material originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development . Reprinted here by author's permission.

When you need a Microsoft Access programmer for your Huntington West VA business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We create Access database applications for all sectors, consisting of hospitals, government agencies, the U.S. military, universities, agriculture, workers services, and insurance provider. We can take care of the most advanced as well as complicated Access and also SQL Server database programming for your business as well as smaller projects, like fixing damaged Access database forms, MS Access reports, Access macros, and VBA code.

Office Updates, Broken References, And The "Cant Find Project Or Library" Surprise

A Windows or Office update can change libraries Access depends on. The result is usually not subtle. Buttons stop responding, automation fails, or you see compile errors that never showed up before.

We start by opening the Visual Basic Editor, compiling the project, and checking references. If a reference is missing, we swap to a safe library, update declarations, and retest the workflows that matter, like exports, email routines, and report buttons.

Why This Happens More In Shared Office Environments

Mixed installs are common. One computer is on Microsoft 365, another is on Access 2019, and a third is still 32-bit because of an older add-in. That mix can work, but the database has to be prepared for it.

In practice, we fix this by tightening up references, reducing dependency on controls that go missing, and building ACCDEs for the bitness you run. We also add a quick startup check so users get a clear message when something is out of sync.

What You Get After We Clean It Up

• A clean compile state with corrected references.
• Updated code declarations for 32-bit or 64-bit Office as needed.
• A short note on what changed and how to avoid repeat surprises.
• If you want help, call (323) 285-0939 and tell us you are in Huntington.

Front End Versioning: Stop "Which File Is Current" Problems

If a database lives on a shared drive, people will copy it. That is human nature. Then you end up with multiple front ends in the wild, and a small change turns into a week of confusion.

Our fix is simple and repeatable. Each user runs a local front end, but the file checks a central version number at startup. If there is a newer build, it pulls it down automatically or prompts the user to close and relaunch.

A Practical Checklist For Cleaner Deployments

1. Split the database so the back end stays put and the front end can be replaced safely.
2. Store the master front end in one controlled folder with version numbers.
3. Add a startup routine that checks versions and copies the newest file locally.
4. Log the version and last update date so support calls start with facts.

Once this is in place, fixes roll out faster, and you stop chasing problems that only happen on one computer. That is a big deal when you have deadlines and people are relying on reports.