Capture filtered records only on report

D

Dulce Candy

Hello,
I am using ACCESS 2000. I have a search form that is working perfectly. I
can select records by associate name, supervisor, site, and by start and end
dates. I want to add a command button that will open a report using the
filtered records only. Currently the filtered records appear in a datasheet
subform in the form footer of my search form. This is code I am using to
filter on my Search form:


Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Associate Name
If Nz(Me.AssociateName) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.associatename = '" &
Me.AssociateName & "'"
End If

' If Supervisor
If Nz(Me.Super) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Super = '" & Me.Super & "'"
End If

' If Site
If Nz(Me.Site) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Site = '" & Me.Site & "'"
End If

' If Start Date
If IsDate(Me.StartDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] >= " &
GetDateFilter(Me.StartDate)
ElseIf Nz(Me.StartDate) <> "" Then
strError = cInvalidDateError
End If

' If End Date
If IsDate(Me.EndDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] <= " &
GetDateFilter(Me.EndDate)
ElseIf Nz(Me.EndDate) <> "" Then
strError = cInvalidDateError
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Exports", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Exports_subform.Form.Filter = strWhere
Me.Exports_subform.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Your help is appreciated!
 
D

Dulce Candy

Not sure if I am understanding your question but currently the user selects
data from combo boxes on a form. When "Search" button is selected it runs the
code listed in initial post and filters the records based on the user
selected criteria. The filtered records appear in the form footer. What I
would like to do is to pull a report based off the filtered records.
Currently when I add a "report" button it is returning a report with all the
records instead of the filtered records.

Thank you for your help!

Klatuu said:
How are you applying the filtering in the subform?

--
Dave Hargis, Microsoft Access MVP


Dulce "Candy" said:
Hello,
I am using ACCESS 2000. I have a search form that is working perfectly. I
can select records by associate name, supervisor, site, and by start and end
dates. I want to add a command button that will open a report using the
filtered records only. Currently the filtered records appear in a datasheet
subform in the form footer of my search form. This is code I am using to
filter on my Search form:


Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Associate Name
If Nz(Me.AssociateName) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.associatename = '" &
Me.AssociateName & "'"
End If

' If Supervisor
If Nz(Me.Super) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Super = '" & Me.Super & "'"
End If

' If Site
If Nz(Me.Site) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Site = '" & Me.Site & "'"
End If

' If Start Date
If IsDate(Me.StartDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] >= " &
GetDateFilter(Me.StartDate)
ElseIf Nz(Me.StartDate) <> "" Then
strError = cInvalidDateError
End If

' If End Date
If IsDate(Me.EndDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] <= " &
GetDateFilter(Me.EndDate)
ElseIf Nz(Me.EndDate) <> "" Then
strError = cInvalidDateError
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Exports", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Exports_subform.Form.Filter = strWhere
Me.Exports_subform.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Your help is appreciated!
Click to expand...
Click to expand...
 
D

Dulce Candy

Not sure if I am understanding your question but currently the user selects
data from combo boxes on a form. When "Search" button is selected it runs the
code listed in initial post and filters the records based on the user
selected criteria. The filtered records appear in the form footer. What I
would like to do is to pull a report based off the filtered records.
Currently when I add a "report" button it is returning a report with all the
records instead of the filtered records.

Thank you for your help!

Klatuu said:
How are you applying the filtering in the subform?

--
Dave Hargis, Microsoft Access MVP


Dulce "Candy" said:
Hello,
I am using ACCESS 2000. I have a search form that is working perfectly. I
can select records by associate name, supervisor, site, and by start and end
dates. I want to add a command button that will open a report using the
filtered records only. Currently the filtered records appear in a datasheet
subform in the form footer of my search form. This is code I am using to
filter on my Search form:


Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Associate Name
If Nz(Me.AssociateName) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.associatename = '" &
Me.AssociateName & "'"
End If

' If Supervisor
If Nz(Me.Super) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Super = '" & Me.Super & "'"
End If

' If Site
If Nz(Me.Site) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Site = '" & Me.Site & "'"
End If

' If Start Date
If IsDate(Me.StartDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] >= " &
GetDateFilter(Me.StartDate)
ElseIf Nz(Me.StartDate) <> "" Then
strError = cInvalidDateError
End If

' If End Date
If IsDate(Me.EndDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] <= " &
GetDateFilter(Me.EndDate)
ElseIf Nz(Me.EndDate) <> "" Then
strError = cInvalidDateError
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Exports", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Exports_subform.Form.Filter = strWhere
Me.Exports_subform.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Your help is appreciated!
Click to expand...
Click to expand...
 
K

Klatuu

What I mean is when you select from the combo boxes and click search, does it
create a query the subform uses or is the subform's record source query based
on the values in the combo boxes?

What you have to do, one way or another, is create a record source for the
report that is exactly the same as the source for the subform.
--
Dave Hargis, Microsoft Access MVP


Dulce "Candy" said:
Not sure if I am understanding your question but currently the user selects
data from combo boxes on a form. When "Search" button is selected it runs the
code listed in initial post and filters the records based on the user
selected criteria. The filtered records appear in the form footer. What I
would like to do is to pull a report based off the filtered records.
Currently when I add a "report" button it is returning a report with all the
records instead of the filtered records.

Thank you for your help!

Klatuu said:
How are you applying the filtering in the subform?

--
Dave Hargis, Microsoft Access MVP


Dulce "Candy" said:
Hello,
I am using ACCESS 2000. I have a search form that is working perfectly. I
can select records by associate name, supervisor, site, and by start and end
dates. I want to add a command button that will open a report using the
filtered records only. Currently the filtered records appear in a datasheet
subform in the form footer of my search form. This is code I am using to
filter on my Search form:


Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Associate Name
If Nz(Me.AssociateName) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.associatename = '" &
Me.AssociateName & "'"
End If

' If Supervisor
If Nz(Me.Super) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Super = '" & Me.Super & "'"
End If

' If Site
If Nz(Me.Site) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Exports.Site = '" & Me.Site & "'"
End If

' If Start Date
If IsDate(Me.StartDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] >= " &
GetDateFilter(Me.StartDate)
ElseIf Nz(Me.StartDate) <> "" Then
strError = cInvalidDateError
End If

' If End Date
If IsDate(Me.EndDate) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Exports.[AuditMonth] <= " &
GetDateFilter(Me.EndDate)
ElseIf Nz(Me.EndDate) <> "" Then
strError = cInvalidDateError
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Exports", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Exports_subform.Form.Filter = strWhere
Me.Exports_subform.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Your help is appreciated!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search Form runtime error 2448 1
runtime error 3075 2
Error when Filtering on Unbound Form with Subform 7
Problem with Runtime Error 2448 4
StrWhere error runtime 2448 5
Date Filter in VBA 1
Multiple criteria selected using checkboxes determine query result 0
Search Code 2

Top