Hi, Darock.
The way I like to do report criteria input forms is to use build a filter
string and place it in a form control, then you can use the control's value
as the criteria parameter in the OpenReport call, and for printing on the top
of the report.
Call the procedure WriteFilterString below in the AfterUpdate event of each
control on the form. It loops through the controls and builds the string.
It knows the field name from using a consistent naming convention. After you
have it working, you can make the control invisible if you like.
Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control
On Error Resume Next
'Reinitialize control
Me!txtFilterString = ""
' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox, acTextBox
‘ I use a naming convention of a 3-character prefix and the underlying
field name
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
‘ handle other cases here, for example when selecting
‘ greater than, less than, equal to, or between
End Select
Next ctl
' Strip the end of the filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)
End Sub
' Command button to preview the report
Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click
Dim strDocName As String
Dim strFilter As String
strDocName = "YourReport"
strFilter = ""
' If no criteria selected, preview entire report
If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If
Exit_cmdPreviewReport_Click:
Exit Sub
Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click
End Sub
I include another command button to clear all the criteria by setting each
control to Null.
Hope that helps.
Sprinks
