View Full Version : Using DoCmd.SendObject, but specifying arguments?


thiazi
04-15-2007, 11:14 AM
I have a form that I'm using DoCmd.OpenReport now that takes arguments from the textboxes/comboboxes and produces a report. The code I'm using for that button is as follows:

Private Sub btnReportOpen_Click()
Dim strWhere As String
Dim cData As Date

strWhere = " EmployeeID = " + EmployeeCombo.Column(0)
If Not ckReturnAllResults Then
strWhere = strWhere + " AND Metric_Data.DATE >= " + CStr(CLng(txtStartDate)) + _
" AND Metric_Data.DATE <= " + CStr(CLng(txtEndDate))
End If
If txtPH <> "" Then
If cbPH = "0" Then
strWhere = strWhere + " AND Metric_Data.P_HR >= " + txtPH
Else
strWhere = strWhere + " AND Metric_Data.P_HR <= " + txtPH
End If
End If
If txtRP <> "" Then
If cbRP = "0" Then
strWhere = strWhere + " AND Metric_Data.P_RP >= " + txtRP
Else
strWhere = strWhere + " AND Metric_Data.P_RP <= " + txtRP
End If
End If
If txtAT <> "" Then
If cbAT = "0" Then
strWhere = strWhere + " AND Metric_Data.AT >= " + txtAT
Else
strWhere = strWhere + " AND Metric_Data.AT <= " + txtAT
End If
End If
If txtAH <> "" Then
If cbAH = "0" Then
strWhere = strWhere + " AND Metric_Data.AH >= " + txtAH
Else
strWhere = strWhere + " AND Metric_Data.AH <= " + txtAH
End If
End If

cbMonth.SetFocus
If cbMonth.Text = "" Then
DoCmd.OpenReport "Summary_Report", acViewPreview, "", strWhere
Else
cData = CDate(CLng(cbMonth))
strWhere = " EmployeeID = " + Employee.Column(0) + " and weekinmonth >= 1 and year = " + CStr(Year(cData)) + _
" and month = " + CStr(Month(cData))
DoCmd.OpenReport "Summary_Report_Monthly", acViewPreview, "", strWhere
End If

End Sub

I'm trying to take the same report and send it via e-mail using DoCmd.SendObject, but am finding that I cannot specify my arguments (strWhere). For the Summary_Report_Monthly (the second report), it's working just fine, but for Summary_Report (the first report), it's showing all the results and not just the results for that particular employee ID in the combobox.

How can I limit/filter that information?

pbaldy
04-15-2007, 11:52 AM
Generally speaking, you either need the report's source query to use the parameters itself, or have code in the open event of the report to set its source dynamically. Simplest for you is probably to move this code to the open event and modify it accordingly.