Can't get this outputto loop to work

[KonaAl]

Hi,

Trying to loop through a combo box on a form which outputs a report. Any
help is appreciated. TIA.

Allan

Dim rs As Recordset
Set rs = Me.FindBranch

Do Until rs.EOF
Dim stDocName As String
Dim stFileName As String
stDocName = "rptScorecard"
stFileName = "p:\fp & a\operations metrics\BMP_" & Me.FindBranch &
"_" & Me.FindBranch.Column(1) & "_" & Me.FindPeriod & "_" & Me.FindYear &
".snp"

DoCmd.OutputTo acReport, stDocName, acFormatSNP, stFileName

rs.MoveNext
Loop

 

[Tom Wickerath]

Hi Allen,

"Can't get this outputto loop to work" is not much of a description for us
to go on. What happens when you attempt to run your code? Have you tried
adding a break point and single stepping through your code, using the F8 key?

A couple of suggestions:

1.) Use Dim rs As DAO.Recordset instead of just Dim rs As Recordset, to
prevent a possible run-time error 13: Type Mismatch. More details here:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html

2.) These three lines of code should be placed before you enter the loop:

Dim stDocName As String
Dim stFileName As String
stDocName = "rptScorecard"

3.) Add a Debug.Print stFileName statement directly after the assignment
statement. Examine the results in the Immediate Window. Does it look
reasonable?

4.) You should close your recordset and set it to nothing when you are done.


What type of controls are FindBranch, FindPeriod and FindYear?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[KonaAl]

Hi Tom,

Thanks for the reply and apologies for the poor description. I'm new Access
and VBA and have been scouring these forums and Viescas' Access 2003 book as
I work on my DB. I'm also going to check out the links you provided --
thanks.

At any rate, I get a type mismatch error after implementing your suggestions
at #1 and #2. I'm trying to understand #3 and #4. Regarding your question
about the controls, FindBranch and FindPeriod are combo boxes based on a
query and table, respectively, and FindYear is a text box the user inputs
(with an input mask).

The code I'm writing is attached to a command button on the form, if this
matters.

Thanks again for your suggestions! Allan

 

[Tom Wickerath]

Hi Al,

John Viescas' book is a very good resource. I highly recommend it.

At any rate, I get a type mismatch error after implementing your suggestions
at #1 and #2.

I suspect that you get the same type mismatch error before implementing my
suggestions as well. The reason is that you cannot set a recordset equal to a
combo box (ie. Set rs = Me.FindBranch).

I'm trying to understand #3 ....

Insert a Debug.Print statement as indicated below with the arrow. After
running the procedure at least once, examine the results by pressing Ctrl G
(the Control and G keys at the same time):

stFileName = "p:\fp & a\operations metrics\BMP_" _
& Me.FindBranch & "_" & Me.FindBranch.Column(1) _
& "_" & Me.FindPeriod & "_" & Me.FindYear & ".snp"
Debug.Print stFileName '<-------
DoCmd.OutputTo acReport, stDocName, acFormatSNP, stFileName

Does the path point to a valid folder?

...and #4.

In general, you should close and destroy objects that you create, in order
to prevent database bloat.

Private Sub MyProc()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = db.OpenRecordset("TableName")

With rs
Do Until (.BOF Or .EOF) = True
'Your code goes here
Loop
End With

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing '<-------------------
db.Close: Set db = Nothing '<-------------------
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure MyProc..."
Resume ExitProc

End Sub


where "TableName" is the name of a saved table, query or a SQL (Structured
Query Language) statement (SELECT This, That FROM TableName WHERE TheOther =
'Some Value').

What goal are you trying accomplish with this command button on your form?
If it is to just print out the current record, then you are approaching this
wrong.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[KonaAl]

Thanks, Tom. I'll work through your suggestions and hopefully I can get this
to work.
Regarding your question:

What goal are you trying accomplish with this command button on your form?
If it is to just print out the current record, then you are approaching this
wrong.

The long answer is ... my report is a metric/dashboard for each of 200+
stores. The report has several subreports which is based on 25 or so queries
(lots of tables for the non-financial data). The form, which provides the
parameters to the queries, is great for previewing individual stores. But I
want to distribute the report, by branch, to a network drive so the store
manager can review(1 report for each store). Of course I don't want to
manually select each store in order to output the report!

Again, thanks for you help. I guess I need to re-read Chapters 21 & 22 of
Viescas' book!

Best regards,

Allan