The previous post explained:
- how Microsoft® Access reacts when you try to overwrite a table used as a record source.
- how to incorporate Visual Basic code that:
- clears the form's record source to avoid errors when recreating the table
- calls a function that recreates the form's record source
Record Source Function
Copy this Function and paste it into the Visual Basic Editor of your form.
Remember, the reason for this function is to change the form's record source from anywhere in your form's code by typing: fncForm_RS.
Hint: The _RS extension of this function's name tells you it changes a record source. Coding functions and procedures with extensions let's you find them quickly when reprogramming or debugging.
'if an error occurs, go to the error line to report it
On Error GoTo Form_RS_err
'set the form's record source to the reloaded customer table and orders the customer alphabetically by name
Form.RecordSource = "select * from tblCustomer_reloadable order by name"
'return to the procedure
'come to this line if an error occurs
'print information about the error in the immediate window
Debug.Print Err.Number & " " &; Err.Description
'display the error to the user in a message box
MsgBox Err.Number &; " " &; Err.Description
Now if you learn more VBA and want to change the record source, you only need to change the Function. This habit of creating separate functions or procedures for record sources saves time.
For example, you may decide to program a control to search for customers. In that case, you would rewrite the record source function to use criteria. Then you would call the function for each search. If this is beyond you, do not worry. All you need to do is copy and paste. If you read the green notations, you will also learn as you go.
Test the Button
Here is a fun way to test your new button.
- Open the customer center in QuickBooks®.
- Create a new test customer with some fake information. Hint: Begin the customer name with an asterisk (*) to make it appear as the first customer on your form.
- Return to the Microsoft® Access database and click the reload button.
- Look for the new test customer.
- Return to QuickBooks® and delete the test customer.
- Return to the form and click the reload button again to see that the customer is no longer there.
After the form's record source is reset, data appears in the text boxes again.
If you did not begin the test customer with an asterisk, you may need to scroll the form to find the test customer.
You now have a Microsoft® Access customer database with a live link to QuickBooks® using QODBC. You simply refresh customer data with the click of a button.
If you haven't redesigned the form to your liking, you may do that now. That is the beauty of doing your own programming.
Hint: I like to use pretty colors and fun clip art to keep work interesting.
Remember, the main reason for creating a customer form in Microsoft® Access is:
- to provide you with as many additional customer fields as desired
- to create a contact table so that each customer may have as many contacts as needed.
Next you will learn how to add additional fields and link them to your customers. There are two reasons for using QODBC:
- a quick, live link without the need for importing IIF files or Microsoft® Excel spreadsheets
- QODBC gives you access to QuickBooks® ListIDs that make excellent unique identifiers because:
- QuickBooks® assigns them
- they are unique
- they are permanent.
Your database will use these ListIDs to link customers to additional records in your database.
Continue to the next post… or return in a day or two for the next installment. Check http://www.twitter.com/vbquick for news and updates.