Do loop syntax

[Mark in Michigan]

I know this can be done, but I always have a hard time with syntax and
defining variables.

I'm trying to automate report generation. I have a form, "Budget_Form" that
has a combo box "Branches". I want to populate "Branches" from the "Branch"
field of table "Branch_auto_print" and print report "Budget_Report" for each
record in the table.

From reading this board, that basis looping code is something like:

Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("MyTable")
Do While Not rs.EOF
'useful stuff in here
rs.MoveNext
Loop
rs.Close

Can anyone help me to get similar code working for this project? Again, I
don't know which varianbles to define and I need the syntax for setting the
value of the combo box and printing the report.

 

[pietlinden]

I know this can be done, but I always have a hard time with syntax and
defining variables.

I'm trying to automate report generation.  I have a form, "Budget_Form"that
has a combo box "Branches".  I want to populate "Branches" from the "Branch"
field of table "Branch_auto_print" and print report "Budget_Report" for each
record in the table.

From reading this board, that basis looping code is something like:

Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("MyTable")
Do While Not rs.EOF
    'useful stuff in here
    rs.MoveNext
Loop
rs.Close

Can anyone help me to get similar code working for this project?  Again, I
don't know which varianbles to define and I need the syntax for setting the
value of the combo box and printing the report.

Makes sense if you're e-mailing the individual reports around (not all
the pages of a huge report)...

const cQUOTE as string = "'" (that's a single quote within double
quotes)
dim rs as dao.recordset
'---open a recordset based on the column we want from the table...
set rs = dbengine(0)(0).OpenRecordset("SELECT Branch FROM
Branch_auto_print",dbOpenSnapshot)

Do While Not rs.EOF
DoCmd.OpenReport "MyReport", acViewPreview, , "[SomeField]=" &
cQUOTE & rs.Fields("Branch") & cQUOTE
'do something with the open report - print it out, e-mail it,
whatever.
'---missing steps here....
DoCmd.Close acReport, "MyReport", acSaveNo
rs.MoveNext '---go to the next record...
Loop

rs.close
set rs=nothing

Okay, so why didn't I answer your question? Well, you don't need to
loop through the combobox. If you open a recordset based on the same
object/query that the combobox has as its controlsource, then you can
just manipulate that. I guess that leads me to another question...
are you trying to print out a report for only the Branch selected in
the combobox? If so, then all you need to do is this:

DoCmd.OpenReport "MyReport", acViewPreview, , "[SomeField]=" & cQUOTE
& me.cboBranch & cQUOTE

if the value in cboBranch's leftmost column is numeric then it's even
easier...

DoCmd.OpenReport "MyReport", acViewPreview, , "[SomeField]=" &
rs.Fields("Branch")

Please clarify... do you want to open the same report with a bunch of
different filters (one per branch, for example) and do something with
that report, like e-mail it? The only reason I ask is because there
are different ways of accomplishing this and the best way depends on
what your ultimate goal is...

Sorry for not really answering your question, but I think we need more
information about what you want to do with the report(s) once it's
open.

 

[Klatuu]

You question on the combo box and the branches is very confusing.
Can you descibe it in a bit more detail, please?

As to the looping, you have that correct. Except, it is a good idea to be
sure you have some records and to fully populate the recordset. It would be
like this:

Set rs = dbEngine(0)(0).OpenRecordset("MyTable")
With rs
If .Recordcount <> 0 Then
.MoveLast
.MoveFirst
Do While Not .EOF
Docmd.OpenReport "My Report", , ,strWhere
.MoveNext
Loop
Else
MsgBox "No Records To Process"
End If
.Close
End With

Note that in the above code, strWhere will filter the report. It is like
the WHERE clause of an SQL statement without the word WHERE. This is the
part you need to explain. How are you populating the combo box, and how do
you want to use it to filter the records.

 

[Dirk Goldgar]

You question on the combo box and the branches is very confusing.
Can you descibe it in a bit more detail, please?

As to the looping, you have that correct. Except, it is a good idea to be
sure you have some records and to fully populate the recordset.

Fully populate the recordset? Why? Unless you need to know exactly how
many records you have, I don't see the point.

 

[Klatuu]

Good question. Can't say I have a specific answer. Just a long established
habit and I can't remember why I started doing it.

 

[Mark in Michigan]

Sorry for the confusion, I'll try to explain it in more detail. Please
ignore the names from the previous post as I'll use names for items here to
try to make it clearer.

The form, "BUD_REPORTS" is currently set up for a user to select a report,
there are about 300 possibilities, to view or print. There are separate view
and print buttons assigned to macros. The combo box,"COMBO" selects a budget
report from table "Budget_List". This table lists the entire population of
available reports. One the button is pressed, a series of queries builds the
report and either views or prints. This is all working fine.

End the end of the budget process, there are about 50 such reports that we
want to print automatically. This will be done by upper management and we
don't want to make them hunt out the final reports needed, just provide a
different button to automatically print all 50 needed reports in order.

My idea was to list those 50 reports in a new table, "Auto_print_budgets".
I would then like to set up an automated process to loop through
"Auto_print_budgets", populating the combo box with the report codes one by
one. It would then perform the functions to build the reports and print,
basically running the print macro that is already set up, then continuing
until EOF "Auto_Print_Budgets".

I understand that this might not translate well to VB, that there is
probably a way other than using the combo box and the macros I've already set
up.

I hope this explains it better - thanks for your response.

 

[Klatuu]

Yes, that helps. Now there is one thing I don't understand here:

One the button is pressed, a series of queries builds the
report and either views or prints. This is all working fine.

What do you mean by "a series of queries builds the report"?

Normally, the report would have a query as its record source. That query
may depend on other queries and tables to pull the data together for the
report to use. Now, in very, very rare and unusual cases (like almost never
really required), you may need to build a temporary table to use as the
report's record source, but that would be unlikely.

It would not be possible to do what you are wanting to do using Macros. In
fact, the way you are doing it now requires a lot more work that if you used
VBA. The way I handle this situation is to use the combo box to select a
report from the reports list table. Then I have two buttons, one to create a
preview of the report, and the other to print the report. There is usually a
function that does that. It will look something like this:

Private Function DoReport(lngView As Long)

Docmd.OpenReport Me.cboReport, lngView

End Function

The in the buttons' After Upate events, I don't do any VBA, I just put the
function directly in the event's text box in the properties dialog. For the
Preview button it would be:

=DoReport(acViewPreview)

And for the Print Button

=DoReprt(acViewNormal)

Okay, now for those reports that need to be done automatically in a batch, I
would suggest you add a Yes/No field to your report list table that you can
use to identify those reports to be included in the automatic list.

Now, all you need to do is create a query that returns the report names
filtered for only those to be included in the automatic list. It would go
something like this untested "air code" written here on the fly, so don't
expect it to work without some debugging:

Private Sub GenAutoReports()
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT RptName FROM ReportsTable
WHERE IncludeInMgmtRpts = True;")

With rst
Do While Not .EOF
Docmd.OpenReport !RptName, acViewNormal
.MoveNext
Loop
.Close
End With
Set rst = Nothing
End Sub

That really is about all there is to it.

 

[Mark in Michigan]

I often use temporary make tables, mostly due to the fact in many cases it is
much quicker to download the portion of tables I need via ODBC, and then
manipulate that data in the db. I have one application where a download and
build report routine was taking up to 30 minutes, by adding a temporary made
table, I cut that time to about 7 minutes. Perhaps if I knew more about VB,
there would be a better way, but this seems to work for me.

Thanks for your help, I will try out the techniques you suggested.