By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

How to pass the value of a public variable to a named query as parameter

P: 2
I have a form that has a subform to ask/assign a value (inserted by the user) to a public variable (VB).
The same form has an Option Group that lauches named querys using DoCmd.OpenQuery.
How can I pass the value held in my public variable to the query as a parameter ? The query has a "where" clause that should "understand" the value inserted by the user on my form.
Can anybody help me ?
Sep 12 '07 #1
Share this Question
Share on Google+
3 Replies


JKing
Expert 100+
P: 1,206
Hi there,

I think a better route overall maybe to create reports based off your queries. Except remove the portion of the where clause that equates the user entered value.

Using the Docmd.OpenReport command you can specify the rest of the where statement.

Here's an example of using the Where Condition parameter of the openreport command.

Let's assume we have a report based off this simple query for a book catalogue.

Expand|Select|Wrap|Line Numbers
  1. SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
  2. FROM tblBook
  3.  
Expand|Select|Wrap|Line Numbers
  1.  Docmd.OpenReport "rptBookListing", acViewNormal,, "[bookAuthor] = 'Jared'",acWindowNormal 
  2.  
If you don't want to make reports you can include form fields as part of a where clause.

Let's use the same query as above mention and add a where clause that will be based off the textbox off a form.

Expand|Select|Wrap|Line Numbers
  1. SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
  2. FROM tblBook
  3. WHERE bookAuthor = Forms!frmBook!txtAuthor
  4.  
Forms is the collection of open forms.
frmBook is the name of your form
txtAuthor would be a control on your form i.e. a text box
Sep 12 '07 #2

P: 2
Hi there,

I think a better route overall maybe to create reports based off your queries. Except remove the portion of the where clause that equates the user entered value.

Using the Docmd.OpenReport command you can specify the rest of the where statement.

Here's an example of using the Where Condition parameter of the openreport command.

Let's assume we have a report based off this simple query for a book catalogue.

Expand|Select|Wrap|Line Numbers
  1. SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
  2. FROM tblBook
  3.  
Expand|Select|Wrap|Line Numbers
  1.  Docmd.OpenReport "rptBookListing", acViewNormal,, "[bookAuthor] = 'Jared'",acWindowNormal 
  2.  
If you don't want to make reports you can include form fields as part of a where clause.

Let's use the same query as above mention and add a where clause that will be based off the textbox off a form.

Expand|Select|Wrap|Line Numbers
  1. SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
  2. FROM tblBook
  3. WHERE bookAuthor = Forms!frmBook!txtAuthor
  4.  
Forms is the collection of open forms.
frmBook is the name of your form
txtAuthor would be a control on your form i.e. a text box

My "named querys" have to manipulate data (insert/update) not just recall ... so ... reports are not proper ... :-(
Thanks anyway
Sep 13 '07 #3

JKing
Expert 100+
P: 1,206
Did you read the bottom part of my previous post that explains how to reference controls in a query?

Also you can write action queries on the fly and run them in VB. If you provide an example of one of your queries and the control/variable you're trying to use, I'm sure we could work something out for you.
Sep 13 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.