Setting Query Criteria Through Code

[Neily]

Hi again,

Separate problem requires separate post. I would also
like a button on one sheet to run a query, but based on a
field in the active record.

ie The user is working on a form, and then they click on a
button that runs a query from a different table based on a
reference number in a field on that form.

I have the button that opens the query, and can quite
easily change the query so that it asks for the parameter
value, but does anyone know how to get that data from the
field and use it as the criteria in the query.

Ta Again....

 

[Nikos Yannacopoulos]

Neily,

Make the button fire some code that reads the field value
from the form and stores it in a public variable,
Write a function that returns the public variable's value.
Use the function in the Filter line in your query.

HTH,
Nikos

 

[wiselys]

Dim Rowselect as string

Rowselect = "SELECT * FROM CLIENTS WHERE STATE = """ &
ME.STATE & """"

DOCMD.RUNSQL (ROWSELECT)

'ME.STATE refers to a text box on the current form
called "state"

'The multiple quotes are a little confusing. 2 quotes
inside a quote equals one quote. 4 quotes together is a
means of starting a quote, the 2 inner quotes equals one
quote, and the last quote ends the quote. This is all
required because the sql statement requires literals to be
surrounded by quotes. Put a breakpoint at the
docmd.runsql statement and open the immediate window.
Type print rowselect and hit return to see what rowselect
looks like. You can copy and paste from the sql view of
the query editor then make the necessary edits to generate
this type of code.

 

[Neily]

That's braw, Ta......

-----Original Message-----

Dim Rowselect as string

Rowselect = "SELECT * FROM CLIENTS WHERE STATE = """ &
ME.STATE & """"

DOCMD.RUNSQL (ROWSELECT)

'ME.STATE refers to a text box on the current form
called "state"

'The multiple quotes are a little confusing. 2 quotes
inside a quote equals one quote. 4 quotes together is a
means of starting a quote, the 2 inner quotes equals one
quote, and the last quote ends the quote. This is all
required because the sql statement requires literals to be
surrounded by quotes. Put a breakpoint at the
docmd.runsql statement and open the immediate window.
Type print rowselect and hit return to see what rowselect
looks like. You can copy and paste from the sql view of
the query editor then make the necessary edits to generate
this type of code.



.