Pass Through Query Parameters

I'm using an SQL pass through query and would like to run
it based on criteria selected in a form. I've tried to
call the form fields in the SQL statement, but keep
getting errors. Can this be done? It runs if the criteria
is hard-coded in, but there are many different variables.
Any help is much appreciated!!

With a pass through query, Access turns everything over to the external
database engine. Therefore, the reference to a control on a form is an
unknown quantity to the engine doing the query processing. You will need to
create the query in VBA where you can put the value of the form control into
a variable.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Stacy" wrote in message
...
I'm using an SQL pass through query and would like to run
it based on criteria selected in a form. I've tried to
call the form fields in the SQL statement, but keep
getting errors. Can this be done? It runs if the criteria
is hard-coded in, but there are many different variables.
Any help is much appreciated!!

Thanks so much for the reply. I don't have much of a VBA
background... so maybe you can help me figure out the
code to use with this trimmed down example?

I want to show the name of all children (field:
ChildName, ODBC datbase table: dbo.Child) that live in a
country specified by the user entering the country name
in a textbox named "txtCountry" on the form
named "frmChildSearch". This will set the parameter for
the dbo.Child.Country field.

Thank you!

-----Original Message-----
With a pass through query, Access turns everything over
to the external
database engine. Therefore, the reference to a control
on a form is an
unknown quantity to the engine doing the query
processing. You will need to
create the query in VBA where you can put the value of
the form control into
a variable.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Stacy" wrote in
message
...
I'm using an SQL pass through query and would like to
run
it based on criteria selected in a form. I've tried to
call the form fields in the SQL statement, but keep
getting errors. Can this be done? It runs if the
criteria
is hard-coded in, but there are many different
variables.
Any help is much appreciated!!


.

Dim v_Country As String
Dim v_SQL as String

v_Country = Me.txtCountry
v_SQL = "Select * From dbo.Child Where Country = " & v_Country & ";"

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Stacy" wrote in message
...
Thanks so much for the reply. I don't have much of a VBA
background... so maybe you can help me figure out the
code to use with this trimmed down example?

I want to show the name of all children (field:
ChildName, ODBC datbase table: dbo.Child) that live in a
country specified by the user entering the country name
in a textbox named "txtCountry" on the form
named "frmChildSearch". This will set the parameter for
the dbo.Child.Country field.

Thank you!

-----Original Message-----
With a pass through query, Access turns everything over
to the external
database engine. Therefore, the reference to a control
on a form is an
unknown quantity to the engine doing the query
processing. You will need to
create the query in VBA where you can put the value of
the form control into
a variable.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Stacy" wrote in
message
...
I'm using an SQL pass through query and would like to
run
it based on criteria selected in a form. I've tried to
call the form fields in the SQL statement, but keep
getting errors. Can this be done? It runs if the
criteria
is hard-coded in, but there are many different
variables.
Any help is much appreciated!!


.