Pass Through Query Parameters

[Stacy]

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!!

 

[Lynn Trapp]

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.

 

[Stacy]

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!

 

[Lynn Trapp]

Dim v_Country As String
Dim v_SQL as String

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