QuickBooks® - QODBC Quick Start Part 6 for Beginners

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


Review

The previous post explained how to:

1. create a Command Button for the Customer Form
2. name the button
3. create an On Click event
4. open the Visual Basic Editor

Overview

Let us refresh for a moment where you are in the creation process. If you do not understand the terminology or exactly what you are doing, no matter. Understanding is secondary. Following these instructions produces results without understanding.

1. You copied and pasted fncCustomerTable into your database. This function connected your database to QuickBooks® using QODBC and then created a table from the results.
2. You tested the function by typing fncCustomerTable into the Immediate Window of the Visual Basic Editor.
3. When you created the customer form, you chose the customer table as its record source. Remember the New Form Wizard where you chose a table? If not, that is fine.
4. Instead of typing fncCustomerTable into the Immediate Window every time you need to refresh the customer table, you will now program a button to click. The button will use fncCustomerTable to reload customers into the database for you.

Overwriting a Form's Record Source

The function you created overwrites the table: tblCustomer_reloadable. You used a suffix (_reloadable) as a descriptive code. Coding tables, queries and functions provides clues for working on your database in the future. A suffix like _reloadable lets you know you may safely delete the table because your database contains code to re-create it.

Let us examine what happens when you try to overwrite a form's record source.

Between the lines Private Sub and End Sub, type:

fncCustomerTable

Close the Visual Basic Editor and select Form View from the View Menu of the Microsoft® Access menubar. Save the form if you have not already done so.

Test the Button

Click the Command Button and wait while Microsoft® Access runs the function and displays the error message below.

Whenever you see this message, you know that Microsoft® Access tried to overwrite a table that is currently a record source for an open form, report or control. Microsoft® Access will not overwrite a table in use. We will fix this.

Fix the Button

You could be lazy and fix this by:

1. clearing the form's record source as on line 100
2. running the function as on line 110
3. resetting the form's record source as on line 120.

Private Sub cmdReloadCustomers_Click()
100     Form.RecordSource = ""
110     fncCustomerTable
120     Form.RecordSource = "tblCustomer_reloadable"
End Sub

However, in the future you may create forms that use complicated queries or SQL statements for record sources. You may also need to recreate the form's record source using criteria that change by user selection. Do not let this confuse you, just continue and we will fix it properly.

A Better Fix

The better method is to:

1. write a Function that creates the record source
2. clear the form's record source  
3. perform the activity
4. call the Function to re-create the form's record source

To accomplish this, delete fncCustomerTable from the code above and replace it with the code below. Be sure to keep the beginning of the procedure and the end of the procedure (Private Sub and End Sub)

'make the mouse pointer an hourglass

100     Screen.MousePointer = 11
'clear the form's record source
110     Form.RecordSource = ""
'run the function that re-creates the customer table
140     fncCustomerTable
'call the function that re-creates the form's record source
150     fncForm_RS
'make the mouse pointer an arrow
160     Screen.MousePointer = 0



Your Visual Basic Editor should now look like this:

Notice line 150 says: fncForm_RS. This is where Visual Basic leaves the button-click procedure to re-create the form's record source. We will write this function next. It is shorter than the one you just wrote so you are only minutes away from importing customers with a click.

Continue to the next post.