INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query Questions

How do I use VB or VBA variables as query parameters? by grtammi
Posted: 19 Jul 02

I responded to a post of a similar type back a while ago, and I did mean to write an FAQ for it then - guess I'll do it now though - better late than ever.

The following is my preferred method of handling the variable parameter - as the French say, "Chacun son gout!"

We will need to create the following variables:

  Dim dbs As Database
  Dim strSQL As String
  Dim strQueryName As String
  Dim qryDef As QueryDef

   '(** of course, you can name the variables whatever you want, as long as you remain consistent. :) )
  On Error Resume Next
   'set variable values
  Set dbs = <database path or CurrentDb>
  strQueryName = "<Name of Query Here>"
'delete old query, if it exists
  dbs.QueryDefs.Delete strQueryName
  strSQL = "SELECT <table name>.<field1>, <tablename>.<field2> ... <tablename>.<fieldX> FROM <tablename> WHERE <tablename>.<fieldname> LIKE '" & <variablename> "';"
 
  Set qrydef = dbs.CreateQueryDef(strQueryName, strSQL)

And you're finished ... but I'm guessing that all those < and > and <field ... > statements may be pretty confusing ... so, let's use a real-time example and show you how the process really works.

Example:
Bob needs to dynamically create a report for a month of the year that his users enters on a form.  His table (called BobTable) contains a month field (called BobMonth) and a currency field (BobBucks).  Once he extracts the data into a query (called BobQuery), he will need to open his report in preview mode (report is called - oh, you guessed it,  BobReport - man, I use really stupid names! ) ... Of course, we're assuming that Bob has done all his error checking, and the month that the user selected has been assigned to a string variable called strMonth (see?  A normal name!)

Anyways ...

On Error Resume Next

Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef

'set variable values
Set dbs = CurrentDb
strQueryName = "BobQuery"

'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName
'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.
strSQL = "SELECT BobTable.BobMonth, BobTable.BobBucks FROM BobTable " _
  & "WHERE BobTable.BobMonth LIKE '" & strMonth & "';"

'Create query definition
Set qrydef = dbs.CreateQueryDef(strQueryName, strSQL)
'Open report for viewing
Docmd.OpenReport "BobReport", acViewPreview
' *** End of Code ***

...

Hope this wasn't TOO confusing for you - I tried to be as detailed as possible - if you need assistance with this code, please feel free to leave me a post.

Greg

PS:  Oh, one last thing!  You will need to set your report recordsource to the name of the query you created.  This way, your report will always contain the data required.



Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close