Thank you.
So you are saying in the Build Event of the print option
form I can Construct a SQL string. How do I get the SQL
string to the report.
There is no form Build Event; I use the AfterUpdate event
of each control to build a selection string which I then
store in another control on the form. You can set Visible
= False for this control if you don't want your users to
see it. See the code below, which applies to a form
containing only combo boxes. The generic
WriteFilterString subroutine is called whenever one of the
combo boxes is updated. It loops through all the relevant
controls (ignoring labels, etc.), and writes the string
out to the invisible control.
To get the report to use the string as the criteria, set a
variable equal to the control, and pass the variable as an
argument to the OpenReport method. See the
cmdPreviewReport sub below.
If I can create only two reports, one for the total queue
counts and another for the % of times on goal, then I can
provide the query the options for the team, job type, and
the date range.
Usually the report asks where it is getting its data from.
do I need to put into the control source "forms!
nameofmyform"?
The report gets its data from the table or query specified
in the Form level Record Source property.
HTH
Kevin Sprinkel
Form Module Code
============================================
Option Compare Database
Private Sub cboBuildingType_AfterUpdate()
Call WriteFilterString
End Sub
Private Sub cboClient_AfterUpdate()
Call WriteFilterString
End Sub
Private Sub cboOwnerType_AfterUpdate()
Call WriteFilterString
End Sub
Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control
'Reinitialize string
Me!txtFilterString = ""
' Loop through all form controls; if there's data, add
to filter string
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
' ctl.Name is the name of the control, e.g.,
' cboBuildingType. The field name corresponding to this
' control, for which I wish to set criteria is named
' BuildingType, so the code strips the 'cbo' from the
' control name to use in the filter string.
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & LTrim
(Right(ctl.Name, Len(ctl.Name) - 3)) & _
"=" & ctl.Value & " AND "
End If
End If
Next ctl
' Strip end of filter
Me.txtFilterString = Left(Me.txtFilterString, Len
(Me.txtFilterString) - 5)
End Sub
Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click
Dim strDocName As String
Dim strFilter As String
stDocName = "rptBidReturn"
strFilter = Me.txtFilterString
DoCmd.OpenReport stDocName, acPreview, , strFilter
' Reset all controls
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
ctl.Value = ""
End If
Next ctl
Me!txtFilterString = ""
Exit_cmdPreviewReport_Click:
Exit Sub
Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click
End Sub
