Possible to set criteria in VBA?

[LaCour]

I am creating a report based on a dynamic query. I get a recordset of all records on my main table and loop through it to create 1 report.rtf file for each record. With each iteration I set the qryDef.Sql. This works, but I think it is cumbersome to have this large sql string. It would be easier if I could just update the criteria or the where clause, but I can't see that property in the query definition when I am in the VBA.

In Access' qry design mode , you can see this is a propert and Access allows you to define filters all over the place (reports, forms). If I wanted to just create the report to view I could pass the filter, but I am using DoCmd.OutputTo to output a file ... and it does not accept a filter arg, so I am looking for a better way to update the sql the form refers to.

Let me know if you have any thoughts.

Thanks in advance.
Blair

 

[cbhnp]

Look into using ADO recordsets instead of the QueryDefs.
Then you could use the recordset.Filter method.

The Access GUI front end for building queries is useful but its not an object that you can manipulate with VBA.

Dim rsData As ADODB.Recordset
Dim cnDB As ADODB.Connection
Dim db As Database
Dim strSQL As String

Set db = CurrentDb
Set cnDB = New ADODB.Connection
Set rsData = New ADODB.Recordset
Set cnDB = CurrentProject.Connection

strSQL = "select * from table "         
cnDB.CursorLocation = adUseClient
rsData.Open strSQL, cnDB, adOpenStatic,  adLockBatchOptimistic

rsData.Filter = "column1 = 'whatever'"

 

[PHV]

cbhnp, how you use your recordset for the DoCmd.OutputTo method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cbhnp]

I was thinking the filtered recordset could be stored in a table defined as the source of the report. Version1 of the report could be run, the table emptied and repopulated with the next filter. That may not be the best solution though.

A better idea, might be to set a global variable (strMyFilter) to hold the filter string. In the Report_Open event put

Private Sub Report_Open(Cancel As Integer)
Me.Filter = strMyFilter
End Sub

Then loop through the main recordset setting the filter with each records:

Do while not rsData.EOF
strMyFilter= "column1 = " & rsData.Fields(1).Value
DoCmd.OutputTo acOutputReport, "MyReport"
rsData.MoveNext
Loop

 

[LaCour]

Everyone, thanks for your help ... you were able to help me with my creative juices.

I know that I can manipulate the actual SQL in a query, but I only wanted to manipulate the Filter - basically the where clause. I have my canned sql set up only as a select statement ... in the vba, I save off the sql from the currentdb.query.sql into a string ... then I append the dynamic where clause created in my vba and assign the string value back to the currentdb.query.sql

This is helpful because it allows other people I work with (less technical and frankly not interested) to work with the select statements in the warm and friendly access environment and allows me finish it on the fly.

Thanks again
Blair