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...
would go into:
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"
If Not IsDate(Me!txtEndDate) Then
MsgBox "Ending date is not a valid date"
If Me!txtStartDate > Me!txtEndDate Then
MsgBox "Ending date must be greater than starting date"
Set db = CurrentDb()
Set rstSites = db.OpenRecordset("qrySites", dbOpenDynaset)
Do While Not rstSites.EOF
Me!txtSite = rstSites("Site")
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)
intCount = rst("cnt")
Set rst = Nothing
Set qdf = Nothing
If intCount > 0 Then
DoCmd.OutputTo acOutputReport, "Mott Billing Report", acFormatRTF, Me!txtLocation & _
Me!txtSite & ".rtf"
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.