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

[dsalex]

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 ?

[JKing]

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.

SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
FROM tblBook
 

 Docmd.OpenReport "rptBookListing", acViewNormal,, "[bookAuthor] = 'Jared'",acWindowNormal 
 

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.

SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
FROM tblBook
WHERE bookAuthor = Forms!frmBook!txtAuthor
 

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

[dsalex]

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.

SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
FROM tblBook
 

 Docmd.OpenReport "rptBookListing", acViewNormal,, "[bookAuthor] = 'Jared'",acWindowNormal 
 

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.

SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
FROM tblBook
WHERE bookAuthor = Forms!frmBook!txtAuthor
 

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

[JKing]

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.