Report Print Based on Search Form

[sonics]

Hi all
I have search form based on Allen Brown and report print command based on Allen Brown also. Everything works fine except the report print does not work what I want. I want to add more Criteria in the Print Command button in the search form with EmployeeID, Department and date ranges. One EmployeeID work in different department and dates. How do I print report based on only the current records displaying in the search form. The code I used below print all the records related to EmployeeID.
Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print


MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

I always appreciate all your helps.

[ranman256]

you can have many controls to pick from , then the vb will filter the ones used and open the report:

Public Sub btnReport_Click()
Dim sSql As String, sWhere As String

    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value

    'remove 1st 'AND'
sWhere = Mid(sWhere, 4)

    'open the query or report here!
DOCMD.OPENREPORT "rMyReport",,sWhere

End Sub

[sonics]

I followed your codes but I was not lucky. The search form I use continuous form, record source based on the crosstab query. Please help me I just start the vba.
thank you

Public Sub cmdPrint_Click()
Dim sSql As String, strWhere As String
Const conjetdate = "\#mm\/dd\/yyyy\#"
sSql = "SELECT* FROM crosstabqry"

If Not IsNull(txtEmployeeID) Then strWhere = strWhere & " and[EmployeeID]= '" & txtEmployeeID & "'"
If Not IsNull(cboShift) Then strWhere = strWhere & " and[Shift]= '" & cboShift & “’”
strWhere = Mid(strWhere, 4)
DoCmd.OpenReport "CrosstabReport", acViewPreview, , strWhere
-------------------------------------------------------------------------------------------------------------------------- -----------
Private Sub cmdSearch_Click()
Dim strWhere As String
Dim lngLen As Long
Const conjetdate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txt EmployeeID) Then
strWhere = strWhere & " ([EmployeeID] = " & Me.txtEmployeeID & ") AND "
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " ([Date] >= " & Format(Me.txtStartDate, conjetdate) & ") AND "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " ([Date] < " & Format(Me.txtEndDate + 1, conjetdate) & ") AND "
End If

If Not IsNull(Me.cboShift) Then
strWhere = strWhere & " ([Shift] = " & Me.cboShift & " ) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub