There I was, typing away, creating some great code, when I realized that I was unsure of the SQL syntax for a complex recordset I needed. Now what?

The Access Query tool came to my rescue! If you've never noticed before, open one of your queries in design mode right now, and look at the choices under the "View" menu. Mixed in with Design View and Datasheet View is something called SQL View. Choose it and there is the answer to my problem. Nice neat tidy SQL code.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

(article continues after sponsor spot)

Here is what I normally do.

Part A - Create the VBA Code

Here is some simple code to show the method in progress. Note specifically the empty set of parenthesis in the OpenRecordset line. This is, of course, where one would normally put the SQL code.

Part B - Get the SQL Code

1. create a new query
2. add all the required tables
3. make sure all the relationships are defined
4. add to the grid all the fields you need
5. put in actual sample values for all required criteria fields
6. test the query, making sure it returns the correct dataset
7. switch to the aforementioned SQL View
8. copy all the SQL code that is there

Part C - Add the SQL code to the VBA Code

1. place your cursor inbetween those 2 parenthesis
2. paste the SQL code
3. clean up the mess

That third item is where all the work is. Up until now it has been easy. Here is what it looked like on my screen after the paste command had been given.

And here is the code after I added " & _ to the end of each line, as a first step in cleaning it up.

This code has an error in it, which Access tells you about if you cursor off the code segment. The quotes around Chicago. This one is easy to fix. Just substitute what I call single quotes.

    CustomerCity)='Chicago') AND

This code should now work just fine. The only problem is that it is "hard-coded" for customers in Chicago with purchases over $500. What if you need to modify these criteria? That is where the power of using textboxes on a form come in. If you not already done so, read this article here. It explains a similar technique that works in the actual query. Since we are running this SQL in VBA within the actual form, we can use a slightly simpler technique.

First, here is the original WHERE clause from above.

    WHERE (((tblCustomers.CustomerCity)='Chicago') AND
    ((tblOrder.OrderAmount)>500));

Here is the clause modified to use 2 textboxes from the form.

    WHERE (((tblCustomers.CustomerCity)='" & me.txtCity & "') AND
    ((tblOrder.OrderAmount)>" & me.txtOrderSize & "));

Notice the use of quotes (") and ampersands (&) to separate the hard-coded portions of the query and the variable criteria portions. Also, the single quotes are still needed to denote a text field. A date field must be denoted using hashes (#).

So, with 2 custom named text boxes and a button connected to the finished code, we have a custom, criteria laden recordset in our VBA code.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved.