Send variables to pass-through query and set result as report reco

[Guest]

Greetings,

Can anyone direct me to a resource where I can learn to write VBA to pass
variables to a pass-through query (MySQL back end) and set the query results
as the recordset of a report in my Access front end? I've got the basics,
such as the connection string, etc., but I just can't make the mental
connection between the query results and the report's recordset property.

Any help will be appreciated.

 

[Guest]

I would create a generic pass-through type query that can be assigned as the
record source of your query. Assume the P-T is named "qsptMyPT" and you have
two text boxes on your form that are used as parameters in the P-T. You can
use DAO code like:

Dim strSQL as String
Dim qdPT as DAO.QueryDef
Dim db as DAO.Database
Set db = CurrentDb
Set qdPT = db.QueryDefs("qsptMyPT")
strSQL = "SELECT FieldA, FieldB, .... " & _
"FROM tblProduction " & _
"WHERE ProdDate Between '" & Me.txtStart & _
"' AND '" & Me.txtEnd & "'"
qdPT.SQL = strSQL
Set qdPT = Nothing
Set db = Nothing

 

[Guest]

Duane,

Thanks for the info. I still need some direction, however. You wrote that I
should create a generic P-T query as the record source of my query. Did you
mean the record source of the report?

When you say a "generic P-T" query, do you mean it has no SELECT statement,
just the connection string?

Finally, I've added the code to the click event of a button on a form, but
the statement "qdPT.SQL = strSQL" generates an error: Object variable or With
block variable not set.

here is the code:

Dim stDocName As String
Dim myVariable As String
Dim strSQL As String
Dim qdPT As DAO.QueryDef
Dim db As DAO.Database
myVariable = "Antunez"
Set db = CurrentDb
setqdpt = db.QueryDefs("qsptMyPT")
strSQL = "SELECT * FROM Faculty " & _
"WHERE FacLastName = '" & myVariable & "'"
qdPT.SQL = strSQL


stDocName = "Faculty"
DoCmd.OpenReport stDocName, acPreview
Set qdPT = Nothing
Set db = Nothing


I do appreciate your assistance on this.