?
Solved

DoCmd.OutputTo file help.....

Posted on 2004-08-02
7
Medium Priority
?
516 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 375 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

649 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