Dim'ed data used in a query.

J

James O

Hello all,

Ok here goes, I have a form that connectes to a table, I have a checkbox on
the form that the user clicks when the account issue is resovled. I have a
button on the form.

What I would like to happen in this. When I click the button on the form the
Code category that is on the table and displayed on the form is loaded to
memory then an update query is run that looks at the code category that was
dim'ed, teh query looks at all the accounts that have that code and then
makes the checkox for all those accounts = true.

I can dim the code that I need to and I can make a query that checks the
complete boxes of all the accounts with that code. But I dont know how to get
my Dim'ed info from the form in my query. Thanks for any help/direction that
you can offer!

James O
 
W

Wayne Morgan

Place a textbox on the form (hidden if desired). Set the value of the
"Dim'ed" variable to the value of the textbox. In the query, you can use a
parameter that refers to this control.

Example:
Me.txtMyTextbox = MyVariable

Then in the query:
=Forms!frmMyForm!txtMyTextbox

You can also set the parameters in the query using code, then run the query
from that code.

Example:
Dim db As DAO.Database, qdf As DAO.QueryDef, prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
Set prm = qdf.Parameters!ParameterTextInQuery
prm = MyVariable
qdf.Execute dbFailOnError
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

Another option would be to use code to rewrite the SQL property of the
QueryDef object.
 
J

James O

Awesome! Thanks, its so simple I wish I had thought of it myself. Thanks again.
 
Top