Once one delves into the wonderful world of MS Access & VBA programming, you quickly gets faced with the problem of executing queries and SQL statements from within VBA. No in general, this does not pose much of an issue and there are any number of great tutorials that cover the various way to handle this. For instance, Database Journal has a great article by Danny Lesandrini entitled Executing SQL Statements in VBA Code which covers all the methods available to a developer and explains the pros and cons of each.

That said, all of these articles cover but the basic idea of a simple SQL Statement. What happens if you want to execute a parameter query? How do you approach that. In fact, this isn’t too big an issue either, once you are shown how to handle it.

Firstly, let us very briefly cover the subject of a Parameter. What is a parameter? In a query, we can specify a criteria. In most instances, the criteria will be a static value. That said, it become necessary in many instances to make these criteria dynamic, and to do this there are different methods, but one common approach is to refer to a control on a form, thus allowing the end-user the ability to specify the criteria to apply to the query. This is what I am referring to in this post.

Evaluating Query Parameters in VBA

The problem arises that if you merely try to execute a query with a criteria to a form control, it will result in an error “Too Few Parameters“. The work around is that we much first make the database evaluates each parameter before running the query, to do so, we use the following code

    Dim db              As DAO.Database
    Dim qdf             As DAO.QueryDef
    Dim prm             As DAO.Parameter
    Dim rs              As DAO.Recordset

    Set db = CurrentDb
    Set qdf = db.QueryDefs("YourQueryName")

    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm

    Set rs = qdf.OpenRecordset

    'Work with the recordset

    rs.Close 'Close the recordset
    'Cleanup after ourselves
    Set db = Nothing
    Set qdf = Nothing

or to execute a query, you’d do something similar to

    Dim db              As DAO.Database
    Dim qdf             As DAO.QueryDef
    Dim prm             As DAO.Parameter

    Set db = CurrentDb
    Set qdf = db.QueryDefs("YourQueryName")

    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm

    qdf.Execute

    'Cleanup after ourselves
    Set qdf = Nothing
    Set db = Nothing

So how does it work exactly? Well, we start off by specifying which query we are working with by setting the value of the qdf variable. Next, we loop through the parameters and evaluate each one. Finally, we execute the query. So simple, once you are shown the proper technique!

You may wish to look over the sample database found at Roger’s Access Library entitled Too few Parameters.

---

Update 2016-06-06

Using the Eval() Function Directly in the Query

Nothing like teaching an old dog new tricks!

Fellow MVPs Dayle Fye, Joe Anderson and Brent Spaulding have kindly shown me another solution to this problem and that is to use the Eval() function.  If you surround the Parameter in question, you will force Access to evaluate it automatically, so need to handle it in any special manner in VBA as the Query will already have done the evaluation.

So say you have a WHERE clause similar to

WHERE [SomeField] = [Forms]![frmSomeForm]![txtSomeTextBox]

you need simply change it to

WHERE [SomeField] = Eval(“[Forms]![frmSomeForm]![txtSomeTextBox]”)

and now your query can be run directly in VBA without any extra code.  The Eval() function make your query work everywhere.

 

Conclusion

At the end of the day, both solutions are very similar as they both use Eval() to evaluate the parameter(s).  It is up to you if you wish to incorporate it directly within your queries to make them more universal, or keep your queries simpler and handle the evaluation through VBA.  Both work so it’s a personal choice.