Can VBA Pass Criteria to a Query or Report?

  • Thread starter Thread starter Bill Johnson
  • Start date Start date
B

Bill Johnson

I have to produce 145 reports, a separate report for each
of 145 branches. I've set up a form where I can choose
each branch in turn, and at a click of a button it
produces a report based on a query that gets it's criteria
from the drop down list on my form. I have to perform
this function 145 times to get them all.

How much better would it be to run a loop in VBA! But I
don't know how to get the query to accept a variable
output from my little private module, to select each
branch in turn.

This must be fairly basic stuff for Access right? Is there
a way instead to have the Report itself filter for a
variable from VBA?
 
Hi Bill,

A couple of ideas come to mind, I'm sure that others may
have additional ideas.

There are a few common ways to manipulate queries in
VBA. One is to just construct the SQL code in VBA by
concatenating the constant portions of the query sql with
the variable values. Another is to define a dao querydef
object and set it equal to a pre-existing query that
contains parameters. Then, you just set each
qdf.parameter equal to the appropriate vba variables. In
either case you could then set the result as the record
source for the report.

Your other idea of setting the report filter may work as
well. It would depend whether you could accomplish what
you need simply by filtering the same source query. If
that is the case, this might be the simplest option. I
haven't had a need to do this before, but I would guess
that all you would have to do is define and set your
report object, then assign the filter string and turn the
filteron property to true.

Hopefully this will help, post back if you have
additional questions on any of the suggestions.

-Ted Allen
 
Thanks!

Okay, since my report query filters exactly the same for
every report, the only difference being the branch number,
I decided to try the Report Filter option. Here is some
code I'm just playing with to try and get results I can
then work from.

---------------Begin sample-------------------------------

RRUU1 = "0102" ' Static Branch number for now

stDocName = "rptEMIS32byRRUU"
stSavName = "M:\Suspense\Reports\Emis32\rptEMIS32by" &
RRUU1 & ".snp"

DoCmd.OpenReport stDocName, acViewPreview, RRUU =
RRUU1", , acWindowNormal

DoCmd.OutputTo acReport, stDocName, acFormatSNP,
stSavName

-------------------------------End Sample-----------------

This doesn't appear to pass filtering information in the
right format to the report. The report returns as though
it had been run with no data (ERRORS) I know that there
is data for 0102 so it must be my syntax. When I checked
the Properties of the Report Object, in the Filter there
was "(0)". Once I'm past this step I'll probably be
stuck again as I will have to convince
the "DoCmd.OutputTo" step to use the filtered report.

I went this route mostly because I'm not at all versed in
SQL.

Thanks again!
 
Hi Bill,

I think you were very close. But, using docmd.openreport
you actually want to list your condition as the where
argument rather than the filter. The filter is looking
for a query name. Also, I think your concatenation was a
little off for the where condition.

I wrote a short test code to do something similar to what
you are trying to do (in this case it opens a recordset
of all project ID's in a table and then loops through the
recordset and exports the report for each project ID).
I've pasted the code below, try to adapt this to your
situation (watch the wrapping). Also, as you did with
your test code, you may want to comment out the looping
part of the code just to test, or step through the code
in debug mode.

Private Sub cmdOpenRpt_Click()

Dim strReportName As String
Dim strSQL As String
Dim strWhere As String
Dim strFileOut As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

strReportName = "rtest"

strSQL = "SELECT ProjectID FROM uprsadmin_tProject_Info"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF
With rst
strWhere = "ProjectID = " & !ProjectID
strFileOut = "C:\Data\" & !ProjectID & ".snp"
DoCmd.OpenReport strReportName, acViewPreview, ,
strWhere
DoCmd.OutputTo acOutputReport, strReportName,
acFormatSNP, strFileOut, False
DoCmd.Close acReport, strReportName, acSaveNo
.MoveNext
End With
Loop

Set rst = Nothing
Set db = Nothing

End Sub

HTH, Ted Allen
 
Back
Top