asked on

Pass Through Query from SQL

I have a Pass-Through query that is pulling from a SQL procedure. When I put in a form control as the criteria in my query that is using the Pass-Through as the source it doesn't seem to work at all and returns 0 records. But when I change the criteria and hard-code it to a specific value it works fine. Is there something special when using a Pass-Through as the source?

Dale Fye

This is because SQL Server has know idea what the value is on your forms control.

Generally, when I want to use a pass-through and a variable value (something that comes from a form), I'll revise the SQL of the pass-thru query in the afterupdate event of the control. Something like:

Private Sub txt_SomeValue_AfterUpdate

Dim strSQL as string

strSQL = "SELECT [Field1], [Field2], [Field3] FROM yourTable " _
& "WHERE [SomeTextField] = '" & me.txt_SomeTextField & "'"
currentdb.querydefs("yourSavedPassThruQuery").SQL = strSQL

End Sub

Then you can call that query and return the results that are specific to the control on your form.

Lawrence Salvucci

ASKER

This pass-through query is the source for my subform but I want it to requery when a combobox on my main form is changed. Should I put this SQL code in the AfterUpdate event of my combobox on my main form?

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial