Solved

DoCmd.OutputTo file help.....

Posted on 2004-08-02
7
506 Views
Last Modified: 2008-03-17
I have a form which generates and seperates reports in my query, based on sites.

On my form there is a location box where users enters in the location they want the report to go to... I have about 80+ sites..

What I want to do is instead of having the user enter in the location of where to generate the report.. have the reports go directly into the folder of that particular site...

ie..

site1
site2
site3

would go into:

folder1/site1
folder2/site2
folder3/site3

[code]
Private Sub cmdStart_Click()

Dim db As Database
Dim rstSites As Recordset
Dim rst As Recordset
Dim qdf As QueryDef
Dim strSQL As String
Dim intCount As Integer

If Not IsDate(Me!txtStartDate) Then
    MsgBox "Starting date is not a valid date"
    Exit Sub
End If

If Not IsDate(Me!txtEndDate) Then
    MsgBox "Ending date is not a valid date"
    Exit Sub
End If

If Me!txtStartDate > Me!txtEndDate Then
    MsgBox "Ending date must be greater than starting date"
    Exit Sub
End If

Set db = CurrentDb()
Set rstSites = db.OpenRecordset("qrySites", dbOpenDynaset)
rstSites.MoveFirst
Do While Not rstSites.EOF
    Me!txtSite = rstSites("Site")
    DoEvents
    strSQL = "select count(*) as cnt from qryBillingReportTest"
    Set qdf = db.CreateQueryDef("", strSQL)
    qdf.Parameters("[Forms]![dlgMonthlyReport]![txtStartDate]").Value = Me!txtStartDate
    qdf.Parameters("[Forms]![dlgMonthlyReport]![txtEndDate]").Value = Me!txtEndDate
    qdf.Parameters("[Forms]![dlgMonthlyReport]![txtSite]").Value = Me!txtSite
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    rst.MoveFirst
    intCount = rst("cnt")
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    If intCount > 0 Then
        DoCmd.OutputTo acOutputReport, "Mott Billing Report", acFormatRTF, Me!txtLocation & _
                        Me!txtSite & ".rtf"
    End If
    rstSites.MoveNext
Loop

End Sub
[/code]

thanks for your help
0
Comment
Question by:aCCeSSgRanteD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 11698333
You will need to establish in advance what site is about to be reported and make adjustments to the outputo command line based on that finding
0
 
LVL 32

Accepted Solution

by:
jadedata earned 125 total points
ID: 11698368
rstSites could contain the path to the folder you want to use.

in this line of code
DoCmd.OutputTo acOutputReport, "Mott Billing Report", acFormatRTF, Me!txtLocation & Me!txtSite & ".rtf"
Me!txtLocation becomes rstSites.PathToFolder

or something like that...
0
 
LVL 2

Author Comment

by:aCCeSSgRanteD
ID: 11698501
Thanks for the fast respose...

But I don't seem to understand all to much about coding..soo please bare with me..

do you mean like so?

    If intCount > 0 Then
        DoCmd.OutputTo acOutputReport, "Mott Billing Report", acFormatRTF, rstSites.\\nameofserver\site$\abc\reports & _
                        Me!txtSite & ".rtf"
    End If
    rstSites.MoveNext
Loop

End Sub


thanks
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 32

Expert Comment

by:jadedata
ID: 11698821
rstSites should have a field in it that contain the path to the folder you want to write a report to.

the value would be passed into the command string by reference.

What is the name of this field in the table supporting the rstSites recordset in your code???
0
 
LVL 2

Author Comment

by:aCCeSSgRanteD
ID: 11704605
Correct me if I'm wrong but do you mean something like...

        DoCmd.OutputTo acOutputReport, "Mott Billing Report", acFormatRTF, Me!txtLocation & _
        Me!txtSite & "\" & Me!txtSite & ".rtf"

also,

what if when i run reports for a particular month and a site does not have a report for that month...

how would I display something like.. "no report for" txtSite .. for that site during that month?

thanks for your help!
 

0
 
LVL 32

Expert Comment

by:jadedata
ID: 11705203
"what if when i run reports for a particular month and a site does not have a report for that month..."

if you know this will happen then be prepared to deal with it when the possibility comes up.  This is the reason for error trapping and conditional process control.

"how would I display something like.. "no report for" txtSite .. for that site during that month?"
msgbox on the determination that no records for that month/site exist.
if your reports are designed to fit this, you may be able to use the report OnNoData event to detect this "lack" of records.
0
 
LVL 2

Author Comment

by:aCCeSSgRanteD
ID: 11707572
Thanks! I'll go ahead and read up on this matter with your suggestions..

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question