Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    19

    Unanswered: DoCmd.OutputTo help...

    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
    thanks for your help

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I would suggest either

    1) A SelectCase statment to asign a string variable to the path required depending on the 'SitID' which could be used in the 'outputfile' argument of the DoCmd. OR

    2) Creat a 'Lookup' table in the database and use a DLookup function to asign to a string variable for the path\file required again depending on the 'SitID' which could be used in the 'outputfile' argument of the DoCmd.


    I would favour the second option as hard coding something that you may want to change (more than once!), or add to, in the future is not so convenient.

    HTH


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •