Using Results from Search Form To Create A Report

[marvelousme]

I have created a search form using VBA that works perfectly. I want to be able to take that same filtered data and use it for a report. I'm having trouble getting my report to open with the filtered results from my form. My search form has a command button containing this code:
Private Sub cmdFilter_Click()
On Error GoTo errr
Me.subProductivtyReport.Form.RecordSource = "Select * From qryProductivty " & BuildFilter
Me.subProductivtyReport.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
Const conJetDate = "\#mm\/dd\/yyyy\#"
varWhere = Null

If Me.txtFrom > "" Then
varWhere = varWhere & "([date] >= " & Format(Me.txtFrom, conJetDate) & ") AND "
End If
If Me.txtTo > "" Then
varWhere = varWhere & "([date] <= " & Format(Me.txtTo, conJetDate) & ") AND "
End If

If Me.cboStatus > "" Then
varWhere = varWhere & "[EmpType] like '" & Me.cboStatus & "' AND "
End If

If cboReviewer > "" Then
varWhere = varWhere & "[CreatedBy] like '" & Me.cboReviewer & "' AND "
End If

If cboVendor > "" Then
varWhere = varWhere & "[Vendor] like '" & Me.cboVendor & "' AND "
End If

If cboRole > "" Then
varWhere = varWhere & "[role] like '" & Me.cboRole & "' AND "
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

I attempted to add a command button to open the report in print preview:
DoCmd.OpenReport "qryProductivty", acViewPreview",, tmp
But it just opens the report unfiltered. What am I doing wrong?

 

[pbaldy]

I don't see where you give tmp a value (other than """"), and it would need to be declared somewhere publicly, or it's only available within BuildString.

 

[marvelousme]

Thanks for the quick response. So what do I need to enter in order to filter my report if "tmp" isn't the right name?

 

[pbaldy]

I didn't say it wasn't the right name, I said you haven't given it a value. Give it the value of varWhere before the "WHERE" gets added.

 

[marvelousme]

Can you please provide an example? Thanks!

 

[pbaldy]

tmp = varWhere

 

[marvelousme]

So I changed my code but the report is still coming up as unfiltered. Do I need to add some sort of command to requery the report first?

 

[pbaldy]

Did you address:

it would need to be declared somewhere publicly, or it's only available within BuildString.

Can you attach the db here?

 

[marvelousme]

Here you go.

 

[pbaldy]

Sorry, but your placement of

tmp = varWhere

gave me a chuckle. It has to be after varWhere is set, like I said right before you add WHERE. Declare the variable above the first sub. Change this bit to:

    If IsNull(varWhere) Then
        varWhere = ""
    Else
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
            tmp = varWhere
            varWhere = "WHERE " & varWhere
        End If
    End If

 

[marvelousme]

figures it would be something really simple for me to get wrong! Thank you so much for your help!!!

 

[pbaldy]

No problem!

 

[JMichaelM]

So the final looks like this:

Option Compare Database
Private Sub cmdClear_Click()
Me.subProductivtyReport.Form.RecordSource = "Select * From subProductivity "
Me.subProductivtyReport.Requery
cboReviewer = ""
txtFrom = ""
txtTo = ""
cboStatus = ""
cboVendor = ""
cboRole = ""
txtFrom.SetFocus
End Sub

Private Sub cmdFilter_Click()
On Error GoTo errr
Me.subProductivtyReport.Form.RecordSource = "Select * From subProductivity " & BuildFilter
Me.subProductivtyReport.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
varWhere = Null
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Me.txtFrom > "" Then
varWhere = varWhere & "([date] >= " & Format(Me.txtFrom, conJetDate) & ") AND "
End If
If Me.txtTo > "" Then
varWhere = varWhere & "([date] <= " & Format(Me.txtTo, conJetDate) & ") AND "
End If
If Me.cboStatus > "" Then
varWhere = varWhere & "[EmpType] like '" & Me.cboStatus & "' AND "
End If
If cboReviewer > "" Then
varWhere = varWhere & "[CreatedBy] like '" & Me.cboReviewer & "' AND "
End If
If cboVendor > "" Then
varWhere = varWhere & "[Vendor] like '" & Me.cboVendor & "' AND "
End If
If cboRole > "" Then
varWhere = varWhere & "[role] like '" & Me.cboRole & "' AND "
End If
If IsNull(varWhere) Then
varWhere = ""
Else
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
tmp = varWhere
varWhere = "WHERE " & varWhere
End If
End If
BuildFilter = varWhere
End Function

Private Sub cmdPrint_Save_Click()
DoCmd.OpenReport "qryProductivty", acViewPreview, tmp
End Sub

 

[pbaldy]

Doubt that would work.