?
Solved

DoCmd.OutputTo file help.....

Posted on 2004-08-02
7
Medium Priority
?
510 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Technology Partners: 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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 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