Solved

DoCmd.OutputTo file help.....

Posted on 2004-08-02
7
474 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access Append Query From CSV File Into Multiple Tables 26 47
Run Time Error 3075 15 41
Open CSV, modify and save as xls from Access 12 19
Running sum query 6 25
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

932 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

10 Experts available now in Live!

Get 1:1 Help Now