Parameters are ignored when export & email command buttons are use

A

Andy

Access 2003, WIn XP
Hi,
My code is below. I needed to take a break from this to clear my head but I
am back. I can view my report on screen and the report only has data based
on my user's parameter inputs. However, when the user clicks on the "Output
Report" or "Mail Report" buttons, the parameter inputs are ignored and the
report that is either exported or emailed has the data from the entire table.

Is anyone able to show me why the parameters are followed when viewing the
report on screen but the parameters are ignored when exporting or emailing?
Thanks.

Andy


Private Sub Command36_Click()

Dim strStartDate As String
Dim strEndDate As String

Dim strWhere As String

' build data conditions.

strStartDate = "#" & Format(Me.activeXCalStart.Value, "mm/dd/yyyy") & "#"
strEndDate = "#" & Format(Me.activeXCalEnd.Value, "mm/dd/yyyy") & "#"

strWhere = "(PROJ_START >= " & strStartDate & _
" and PROJ_START <= " & strEndDate & ")"

' combo box.

strWhere = strWhere & " and (OPR = '" & Me.cboOPR & "')"

'MsgBox strWhere
Dim intBox As Integer
Dim strMsg As String
Dim strDisplayStartDate As String
Dim strDisplayEndDate As String

strDisplayStartDate = Format(Me.activeXCalStart.Value, "mm/dd/yyyy")
strDisplayEndDate = Format(Me.activeXCalEnd.Value, "mm/dd/yyyy")

strMsg = "You will see " & Me.cboOPR & " data with a start date between "
& strDisplayStartDate & " And " & strDisplayEndDate & ". Is this the data you
want?"
'strMsg = "You will see " & Me.cboOPR & " data with a start date between
" & strStartDate & _
'" And " & strEndDate & " Is this the data you want?"

intBox = MsgBox(strMsg, vbQuestion + vbYesNo, "OPR Cost Report")
If intBox = vbYes Then docmd.OpenReport "repCostTotalbyOPR",
acViewPreview, , strWhere

End Sub

Private Sub Form_Load()
activeXCalStart.Value = Date
activeXCalEnd.Value = Date
End Sub
Private Sub activeXCalEnd_AfterUpdate()
'Purpose: Update the text boxes
txtEndDate.Value = Format(activeXCalEnd.Object.Value, "dddddd")
End Sub

Sub cmdEndNextDay_Click()
activeXCalEnd.Object.NextDay
End Sub

Sub cmdEndPreviousDay_Click()
activeXCalEnd.Object.PreviousDay
End Sub

Sub cmdEndNextWeek_Click()
activeXCalEnd.Object.NextWeek
End Sub

Sub cmdEndPreviousWeek_Click()
activeXCalEnd.Object.PreviousWeek
End Sub

Sub cmdEndNextMonth_Click()
activeXCalEnd.Object.NextMonth
End Sub

Sub cmdEndPreviousMonth_Click()
activeXCalEnd.Object.PreviousMonth
End Sub

Sub cmdEndNextYear_Click()
activeXCalEnd.Object.NextYear
End Sub

Sub cmdEndPreviousYear_Click()
activeXCalEnd.Object.PreviousYear
End Sub
Private Sub activeXCalStart_AfterUpdate()
'Purpose: Update the text boxes
txtStartDate.Value = Format(activeXCalStart.Object.Value, "dddddd")
End Sub

Sub cmdStartNextDay_Click()
activeXCalStart.Object.NextDay
End Sub

Sub cmdStartPreviousDay_Click()
activeXCalStart.Object.PreviousDay
End Sub

Sub cmdStartNextWeek_Click()
activeXCalStart.Object.NextWeek
End Sub

Sub cmdStartPreviousWeek_Click()
activeXCalStart.Object.PreviousWeek
End Sub

Sub cmdStartNextMonth_Click()
activeXCalStart.Object.NextMonth
End Sub

Sub cmdStartPreviousMonth_Click()
activeXCalStart.Object.PreviousMonth
End Sub

Sub cmdStartNextYear_Click()
activeXCalStart.Object.NextYear
End Sub

Sub cmdStartPreviousYear_Click()
activeXCalStart.Object.PreviousYear
End Sub

Private Sub Command39_Click()

Dim strStartDate As String
Dim strEndDate As String

Dim strWheree As String

' build data conditions.

strStartDate = "#" & Format(Me.activeXCalStart.Value, "mm/dd/yyyy") & "#"
strEndDate = "#" & Format(Me.activeXCalEnd.Value, "mm/dd/yyyy") & "#"

strWheree = "(PROJ_START >= " & strStartDate & _
" and PROJ_START <= " & strEndDate & ")"

' combo box.

strWheree = strWheree & " and (OPR = '" & Me.cboOPR & "')"

MsgBox strWheree

docmd.OutputTo acOutputReport, "repCostTotalbyOPR"
End Sub

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim strStartDate As String
Dim strEndDate As String

Dim stDocName As String

stDocName = "(PROJ_START >= " & strStartDate & _
" and PROJ_START <= " & strEndDate & ")"

stDocName = stDocName & " and (OPR = '" & Me.cboOPR & "')"

docmd.SendObject acSendReport, "repCostTotalbyOPR", , , , , , , ,
stDocName

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


docmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub
 
M

MacDermott

Please note that your DoCmd.OpenReport command includes strWhere as an
argument, so this information is passed to the report as it is opened.
In contrast, your DoCmd.OutputTo command does not include the strWhere
information. Therefore, the entire report is transmitted.
To my knowledge, there is no way to include a filter string like this in
the OutputTo command. However, if you include references to the controls
which display the filter information in the query underlying the report (and
of course the form is open at the time you run the report), these parameters
will be applied to the report both when it is displayed on the screen and
when it is sent out.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top