Pass Through Criteria (1 Viewer)

[dark11984]

I'm hoping someone can help me find a simple solution to this.

I have a pass through query in my Access database where currently i have to open the SQL to change my date criteria.

SELECT DATANS.POOMST.PKORDN, DATANS.POOMST.PDORDD
FROM DATANS.POOMST
WHERE DATANS.POOMST.PDORDD = '20130109'

I would like to be able to select the date criteria from a drop down box on a form and then run the query by clicking a command button.

I know this is possible when querying Access tables, however i am not sure it works with pass through queries.

Thanks

 

[AccessMSSQL]

You need to modify the pass through query dynamically.

Use something like this function in your code:

Public Sub ChangePTStatement(p_QueryName As String, p_sql As String)
'for changing pass-through's in this db
    Dim qdef As dao.QueryDef
 
    Set qdef = CurrentDb.QueryDefs(p_QueryName)
    qdef.SQL = p_sql
    qdef.Close
    Set qdef = Nothing
 
End Sub

Then behind your command button you can do something like this:

Dim strSQL as string
Dim strDate as string
strDate = 'set this to the value in your dropdown list
 
strSQL = "SELECT DATANS.POOMST.PKORDN, DATANS.POOMST.PDORDD"
strsql = strsql  &  " FROM DATANS.POOMST"
strsql = strsql  " WHERE DATANS.POOMST.PDORDD = '" & strDate & "'"
 
'call the pass through function
ChangePTStatement "YourQueryName",strSQL

 

[dark11984]

Thanks for your response.

I am still trying to get my head around how this works.

I have put the first code in a module. Do I change p_QueryName to the name of my pass through query or do i leave it as p_QueryName?

Am I still required to have a pass through query or does the query now only exist in the command button code?

 

[AccessMSSQL]

Leave p_Queryname as is. This is just the name of the variable/parameter you are passing to the procedure.

 

[AccessMSSQL]

No you still need the pass through query.
You need to replace "YourQueryName" with the name of your passthrough query.

 

[dark11984]

I'm getting an error "Compile error: Ambiguous name detected: ~" on ChangePTStatement.

Also should my pass through query look like:

SELECT DATANS.POOMST.PKORDN, DATANS.POOMST.PDORDD
FROM DATANS.POOMST

or:

SELECT DATANS.POOMST.PKORDN, DATANS.POOMST.PDORDD
FROM DATANS.POOMST
WHERE DATANS.POOMST.PDORDD =

 

[AccessMSSQL]

Please post your code. You need to pass the full SQL syntax of the pass through query to the ChangePTStatement procedure, including the WHERE statement.

 

[dark11984]

This is my Pass through query code, however the date needs to be linked to a textbox or combo box on a form.

SELECT DATANS.POOMST.PKORDN, DATANS.POOMST.PDORDD 
FROM DATANS.POOMST
WHERE DATANS.POOMST.PDORDD = '20130115'

This is my code in the module

Public Sub ChangePTStatement(p_QueryName As String, p_sql As String)
'for changing pass-through's in this db
Dim qdef As dao.QueryDef
 
Set qdef = CurrentDb.QueryDefs(p_QueryName)
qdef.SQL = p_sql
qdef.Close
Set qdef = Nothing
 
End Sub

This is my code on the command button

Private Sub CmdExtract_Click()
 
Dim strSQL as string
Dim strDate as string
strDate = me.txtOrdDt
 
strSQL = "SELECT DATANS.POOMST.PKORDN, DATANS.POOMST.PDORDD"
strsql = strsql  & " FROM DATANS.POOMST"
strsql = strsql  & " WHERE DATANS.POOMST.PDORDD = '" & strDate & "'"
 
'call the pass through function
ChangePTStatement "QryPTPOData",strSQL
 
End Sub

 

[AccessMSSQL]

Not sure why you are getting the ambigous name detected error. Your original pass through query has date formatted as '20130115'. So the strDate variable needs to be formatted the same way. Does your pass through query run fine from the design window with the way it is now? If you txtOrdDate field is passing the date as 1/15/2013 this will not work. What field format is your DATANS.POOMST.PDORDD column?

 

[dark11984]

The code that i posted earlier (#8) works. Not sure where the ambiguous name error came from but its not happening now.

Thanks for your assistance!

 

[AccessMSSQL]

Great! Glad you got it working.