'Serving the Microsoft® Access
SQL to VBA
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
Part C - Add the SQL code to the VBA Code
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.
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
Here is the clause modified to use 2 textboxes from the form.
WHERE (((tblCustomers.CustomerCity)='" & me.txtCity & "') AND
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.