Passthrough Criteria from a form to SQL for a report

[DataEmbryo]

Hi All,

I have read the previous posts on creating passthrough querys with parameters, but for I just can't seem to make it work for me. Here's what I want to do...

I have a form where you select from a list box that queries from a table. When I click a button on the form, I want to use the value of the selection [idissue] in a passthrough query to a SQL server. The query is used in a report.

This is my SQL in the passthrough query...

SELECT idIssue, vchrTitle, dtmRaised, dtmRequired, dtmDelivered, smintUserAssigned, UserAssigned, smintUserRaised, UserRaised, tintStatus, State, tintColor, StatusColor, tintImpact, Impact, vchrClosingComments, vchrReference, vchrComments, CommentEditDate, vchrDescription, DescEditDate

FROM  vw_issue

WHERE IDIssue =     --Criteria from form

GROUP BY idIssue, vchrTitle, dtmRaised, dtmRequired, smintUserAssigned, UserAssigned, smintUserRaised, UserRaised, tintStatus, tintColor, StatusColor, tintImpact, Impact, vchrClosingComments, vchrReference, vchrComments, CommentEditDate, state, dtmDelivered, vchrDescription, DescEditDate

Thank you for your help

Mark

 

[dhookom]

You can use code to modify the SQL property of your saved pass-through query.

   Dim strSQL as String
   Dim qd as DAO.QueryDef
   Dim db as DAO.Database
   Set db = CurrentDb
   Set qd = db.QueryDefs("YourPTQueryName")
   strSQL = "SELECT idIssue, vchrTitle,...."
   strSQL = strSQL & " FROM vw_issue WHERE IDIssue = "
   [b]strSQL = strSQL & Me.txtIDIssue[/b]
   strSQL = strtSQL & " GROUP BY idIssue, ...."
   qd.SQL = strSQL
   Set qd = nothing
   Set db = Nothing

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DataEmbryo]

Thanks Duane!

A co-worker said I needed a weekend to refresh my mind to figure out my problem... I thought about splitting the query up into pieces this morning, and then you confirmed it.

Works great, thanks again!

Mark