Solved

DoCmd.OutputTo file help.....

Posted on 2004-08-02
7
466 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
  • 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now