SQL Pass-through Query - How Do You Reference an Access Form

  • Thread starter Thread starter Mike Riley
  • Start date Start date
M

Mike Riley

I have a pass through query that pulls data from a couple
tables. Currently, I have hard coded order number and
supp version criteria as shown below:

p.order_number = 54315 and p.supp_version = 8

What I want is to replace the "54315" and "8" values with
variables from a form similar to the follow which does NOT
work as shown:

p.order_number = [Forms]![frmCriteriaEntry]!
[txtEnterOrderNo] and p.supp_version = [Forms]!
[frmCriteriaEntry]![txtEnterOrderNoSupp]

Can anyone help me with the syntax .. assuming one can
indeed reference an Access form in a pass-through query.

Thanks.
 
Mike Riley said:
I have a pass through query that pulls data from a couple
tables. Currently, I have hard coded order number and
supp version criteria as shown below:

p.order_number = 54315 and p.supp_version = 8

What I want is to replace the "54315" and "8" values with
variables from a form similar to the follow which does NOT
work as shown:

p.order_number = [Forms]![frmCriteriaEntry]!
[txtEnterOrderNo] and p.supp_version = [Forms]!
[frmCriteriaEntry]![txtEnterOrderNoSupp]

Can anyone help me with the syntax .. assuming one can
indeed reference an Access form in a pass-through query.
Click to expand...

You can't. PT queries are executed on your server which has no knowledge about or
ability to reference your forms. You can change the SQL of the PT so that it
includes the literal value it would find in the form and then run it.
 
Here is a sample of how to re-create a query object in code: (works for SPT
queries too since the connection property is cached.)

Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
'strCriteria = (some condition that you choose, such as a form
reference.)

strSQL = "SELECT table1.field1, table1.field2 "
strSQL = strSQL & "FROM table1 "
strSQL = strSQL & "WHERE (" & strCriteria & ");"

If QueryExists("MyQuery") = True Then
dbs.QueryDefs.Delete "MyQuery"
End If
Set qdf = dbs.CreateQueryDef("MyQuery")
qdf.SQL = strSQL

DoCmd.SetWarnings False
DoCmd.OpenQuery "MyQuery"
DoCmd.SetWarnings True
MsgBox ("This is a different query than the original one!!")

Set dbs = Nothing

The function QueryExists should be placed in a standard module.
It is used to test if a given query exists in the database.

Function QueryExists(QueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(QueryName))
End Function

--
Joe Fallon
Access MVP



Rick Brandt said:
Mike Riley said:
I have a pass through query that pulls data from a couple
tables. Currently, I have hard coded order number and
supp version criteria as shown below:

p.order_number = 54315 and p.supp_version = 8

What I want is to replace the "54315" and "8" values with
variables from a form similar to the follow which does NOT
work as shown:

p.order_number = [Forms]![frmCriteriaEntry]!
[txtEnterOrderNo] and p.supp_version = [Forms]!
[frmCriteriaEntry]![txtEnterOrderNoSupp]

Can anyone help me with the syntax .. assuming one can
indeed reference an Access form in a pass-through query.
Click to expand...

You can't. PT queries are executed on your server which has no knowledge about or
ability to reference your forms. You can change the SQL of the PT so that it
includes the literal value it would find in the form and then run it.
Click to expand...
 
Back
Top