QuickBooks® - QODBC Quick Start Part 5 for Beginners

This is part of a Quick Start tutorial on using QODBC with QuickBooks®.

The tutorial starts here.


Previous posts explained how to:
  1. create a Pass-Through query to;
  2. import the QuickBooks® customer table into;
  3. a Microsoft® Access database and;
  4. create a new customer form and;
  5. fill the form with the imported customer data.
This post explains how to create a Command Button on the form to refresh customer data directly from QuickBooks® without using IIF files, spreadsheets or linked tables.

The Command Button

Running a Pass-Through query each time you open the customer form wastes time. You need a way to refresh customer data only when desired.

Your form may look different than these illustrations but no matter. For example, ListID and IsActive do not appear in these illustrations in Form View. I have moved them off the form because although queries require them, viewing them is unnecessary.

To avoid tabbing to fields you do not use or fields located off the form:

  1. Select the field's control in Design View
  2. Double-click the control to open the Properties window
  3. set the control's Tab Stop property to No.

You may have chosen different fields for your form than shown here. The fields you include are up to you but remember you must include ListID.

With the form in Design View, expand the form header to make room for a Command Button.

Create a new Command Button by selecting the Command Button icon from the Toolbox Menu. Hint: the Toolbox Menu appears on the Microsoft® Access menubar when your form is in Design View. If you do not see the Toolbox Menu:
  1. Select View from the menubar
  2. Select Toolbars from the View menu
  3. Check Toolbox on the Toolbars menu

After placing the new Command Button on the form, click Cancel if the Command Button Wizard appears.

Name the Button

Double-click the new Command Button to open the Properties window.

Click the Format tab and type the Text you wish to appear on the new Command Button to indicate its function. You may use any words you like. Some suggestions are:
  • Reload Customers
  • Refresh Customers
  • Get Customers
  • Re-query Customers
  • Connect to QuickBooks
  • Import QuickBooks Data

Click the Other tab to name the new Command Button. This name will appear in the Visual Basic Editor so assign the Command Button an easily recognized name.

The preferred method for naming controls is to use a prefix. One generally accepted prefix for Command Buttons is cmd. Prefixes are known as VBA naming conventions in programming circles. That is a fancy way of saying: a set of rules for naming variables so you know what the variable represents when you see it.

You may use VBA Naming Conventions or design your own, so long as you know what the prefixes mean. This insures that you can find command buttons in your code by searching for cmd if you decide to add or change code later.

Create an Event

Click the Event tab and place your cursor on the line next to On Click. You will create an On Click Event. You will tell Microsoft® Access what to do when someone clicks the new Command Button. Clicking is an Event. A much smaller event than a concert or football game but an event just the same.

When you place the cursor in the text box next to On Click, three dots appear at the end of the text box. Click the dots to open the Visual Basic Editor.

The Visual Basic Editor creates a Procedure. Do not let the word Procedure intimidate you. Procedures are simply blocks of statements and nothing more.

Procedures begin with Private Sub plus a name. In this case, the name is the name you gave the Command Button plus the suffix: _Click(). This translates to Visual Basic as:
When a user clicks the Command Button with this name, come to this Procedure.

Procedures end with the words: End Sub to tell Visual Basic where to stop.

Between Private Sub and End Sub, we add instructions for Visual Basic to follow.

We want to refresh the customer table with a button click. We already wrote a function to accomplish this so now we only need to tell Visual Basic to perform that function.

Continue to the next post


Popular Posts