Displaying SQL results

[Guest]

Hello all,

How can I display the results of SQL in VB. Here is the SQL and it will
have multiple records.

vSql = "select count(*), area_id from wcs_to_via_t " & _
"where trans_stt = '00' " & _
"and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
"group by area_id " & _
"Order by area_id, count(*)"

DoCmd.RunSQL (vSql)

 

[Douglas J Steele]

RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT ... INTO or
DELETE)

You would have to create a query and run the query:

Dim qdfTemp As DAO.QueryDef

Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
DoCmd.OpenQuery "qryTemp"

Note that qryTemp cannot already exist in this case. If this is something
you're going to do repeatedly, you're best off creating a "permanent" query
object, and just resetting its SQL property as required:

Dim qdfTemp As DAO.QueryDef

Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
qdfTemp.SQL = vSQL
DoCmd.OpenQuery "qryTemp"

 

[Guest]

It is something that will run quite frequent. How can I pass by variables to
the query?

 

[Douglas J. Steele]

Notice that I specified vSql in my code, the same variable name that you
were assigning the SQL string to. Simply continue assigning vSql the way you
have been.

 

[chris.nebinger]

I'm wondering, if you repeatedly rewrite the SQL, the query would not
be optimized. Wouldn't it be better to use parameters?

SQL:

select count(*), area_id from wcs_to_via_t "where trans_stt = '00' and
dtimecre Between [SDate] and [EDate] group by area_id Order by area_id,
count(*)


VB Code:

Dim dbs As DAO.Database
Dim rst As DAO.QueryDef
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryTemp")
qdf.Parameters("sDate") = vSDate
qdf.Parameters("eDate") = vEDate
Set rst = qdf.OpenRecordset



Chris Nebinger

Dim

 

[Guest]

Douglas,

Thanks for getting this going. I have one more question.

I'm wanting the results to be displayed on a sub report in a form. How can I
get the results to display in the sub report? I'm using the:

If this is something

 

[Douglas J Steele]

That's a completely separate request!

You don't actually want to open a query. Instead, you want to set the
RecordSource for the subform to your query.

 

[Guest]

Well, maybe I need I a little of both. I have a form that has a selection
shift, or hour. Then they select the date from a calendar control. In my vb
if use these fields and format the date for the query. I would then like to
have the results display in a pane on the form. The pane could be a text
field, or even a sub report.

The problem with using a sub report is it populates when the form is first
opened.

I have used recordsets before and populated it by reading line by line and
putting it in a text field. But, I was hoping that there was a better way.

 

[Douglas J Steele]

You can not set the Recordsource property for the subform (i.e.: set the
property to blank), so that it doesn't load when the parent form is loaded,
and then set the property back to what it should be once you're ready to
have the subform populated.