Report Filter Via a Form? Help?

G

Guest

I have a form to filter a my rptinventory:

everything is working fine, I set my properties and set the filter and the
report filters properly, but what I sould like to do, is on the "Clear"
function it clears my Filter properties, but I would like the clear to also
clear and return the report to original format with no filters?

Any suggestions?

Here is my coding for my frminventorysearch

Begin Code:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptInventory].Filter = strSQL
Reports![rptInventory].FilterOn = True
Else
Reports![rptInventory].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptInventory"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptInventory", A_PREVIEW
DoCmd.Maximize
End Sub

End Code *****
 
D

Duane Hookom

In over 10 years of creating Access reports, I have never set a filter
property of a report like this. I create a filter string/expression prior to
opening the report and use the strSQL in the DoCmd.OpenReport method like:

Dim strSQL as String, intCounter As Integer
For intCounter....

Next
DoCmd.OpenReport "rptYourReport", acPreview, , strSQL

Your strSQL ends up as the filter of the report. There is nothing to clear.
 
G

Guest

Thanks Duane,

Do you think that I have poor design? B/c it works really well, I have my
form with 5 different (cbobox) filter options, and I can choose one or all
of them to filter my report.

its not a big deal if I can't return to the original state of the form
(with no filter), I was just wondering if it was possible and it might be
nice if it could be done.

Thanks,

Brook

Duane Hookom said:
In over 10 years of creating Access reports, I have never set a filter
property of a report like this. I create a filter string/expression prior to
opening the report and use the strSQL in the DoCmd.OpenReport method like:

Dim strSQL as String, intCounter As Integer
For intCounter....

Next
DoCmd.OpenReport "rptYourReport", acPreview, , strSQL

Your strSQL ends up as the filter of the report. There is nothing to clear.

--
Duane Hookom
MS Access MVP


Brook said:
I have a form to filter a my rptinventory:

everything is working fine, I set my properties and set the filter and the
report filters properly, but what I sould like to do, is on the "Clear"
function it clears my Filter properties, but I would like the clear to
also
clear and return the report to original format with no filters?

Any suggestions?

Here is my coding for my frminventorysearch

Begin Code:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptInventory].Filter = strSQL
Reports![rptInventory].FilterOn = True
Else
Reports![rptInventory].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptInventory"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptInventory", A_PREVIEW
DoCmd.Maximize
End Sub

End Code *****
Click to expand...
Click to expand...
 
D

Duane Hookom

I am somewhat surprised that the filter property is retained without a
"save" command in there. I suppose you could set the filter to "" in the On
Close event of the report.

--
Duane Hookom
MS Access MVP


Brook said:
Thanks Duane,

Do you think that I have poor design? B/c it works really well, I have
my
form with 5 different (cbobox) filter options, and I can choose one or
all
of them to filter my report.

its not a big deal if I can't return to the original state of the form
(with no filter), I was just wondering if it was possible and it might be
nice if it could be done.

Thanks,

Brook

Duane Hookom said:
In over 10 years of creating Access reports, I have never set a filter
property of a report like this. I create a filter string/expression prior
to
opening the report and use the strSQL in the DoCmd.OpenReport method
like:

Dim strSQL as String, intCounter As Integer
For intCounter....

Next
DoCmd.OpenReport "rptYourReport", acPreview, , strSQL

Your strSQL ends up as the filter of the report. There is nothing to
clear.

--
Duane Hookom
MS Access MVP


Brook said:
I have a form to filter a my rptinventory:

everything is working fine, I set my properties and set the filter and
the
report filters properly, but what I sould like to do, is on the "Clear"
function it clears my Filter properties, but I would like the clear to
also
clear and return the report to original format with no filters?

Any suggestions?

Here is my coding for my frminventorysearch

Begin Code:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & "
=
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptInventory].Filter = strSQL
Reports![rptInventory].FilterOn = True
Else
Reports![rptInventory].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptInventory"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptInventory", A_PREVIEW
DoCmd.Maximize
End Sub

End Code *****
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Duane,

The filter is an "on the fly " when I open my frminventorysearch, it opens
my rptinventory, when in turn, when I choose my filter options, will filter
the report on the fly, I don't save the filter options ... its my option to
search my 10,000 plus records ..



Do you understand?

Duane Hookom said:
I am somewhat surprised that the filter property is retained without a
"save" command in there. I suppose you could set the filter to "" in the On
Close event of the report.

--
Duane Hookom
MS Access MVP


Brook said:
Thanks Duane,

Do you think that I have poor design? B/c it works really well, I have
my
form with 5 different (cbobox) filter options, and I can choose one or
all
of them to filter my report.

its not a big deal if I can't return to the original state of the form
(with no filter), I was just wondering if it was possible and it might be
nice if it could be done.

Thanks,

Brook

Duane Hookom said:
In over 10 years of creating Access reports, I have never set a filter
property of a report like this. I create a filter string/expression prior
to
opening the report and use the strSQL in the DoCmd.OpenReport method
like:

Dim strSQL as String, intCounter As Integer
For intCounter....

Next
DoCmd.OpenReport "rptYourReport", acPreview, , strSQL

Your strSQL ends up as the filter of the report. There is nothing to
clear.

--
Duane Hookom
MS Access MVP


I have a form to filter a my rptinventory:

everything is working fine, I set my properties and set the filter and
the
report filters properly, but what I sould like to do, is on the "Clear"
function it clears my Filter properties, but I would like the clear to
also
clear and return the report to original format with no filters?

Any suggestions?

Here is my coding for my frminventorysearch

Begin Code:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & "
=
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptInventory].Filter = strSQL
Reports![rptInventory].FilterOn = True
Else
Reports![rptInventory].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptInventory"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptInventory", A_PREVIEW
DoCmd.Maximize
End Sub

End Code *****
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I don't understand why you don't just create a "where" clause and use it in
the DoCmd.OpenReport method.

--
Duane Hookom
MS Access MVP


Brook said:
Duane,

The filter is an "on the fly " when I open my frminventorysearch, it opens
my rptinventory, when in turn, when I choose my filter options, will
filter
the report on the fly, I don't save the filter options ... its my option
to
search my 10,000 plus records ..



Do you understand?

Duane Hookom said:
I am somewhat surprised that the filter property is retained without a
"save" command in there. I suppose you could set the filter to "" in the
On
Close event of the report.

--
Duane Hookom
MS Access MVP


Brook said:
Thanks Duane,

Do you think that I have poor design? B/c it works really well, I
have
my
form with 5 different (cbobox) filter options, and I can choose one or
all
of them to filter my report.

its not a big deal if I can't return to the original state of the form
(with no filter), I was just wondering if it was possible and it might
be
nice if it could be done.

Thanks,

Brook

:

In over 10 years of creating Access reports, I have never set a filter
property of a report like this. I create a filter string/expression
prior
to
opening the report and use the strSQL in the DoCmd.OpenReport method
like:

Dim strSQL as String, intCounter As Integer
For intCounter....

Next
DoCmd.OpenReport "rptYourReport", acPreview, , strSQL

Your strSQL ends up as the filter of the report. There is nothing to
clear.

--
Duane Hookom
MS Access MVP


I have a form to filter a my rptinventory:

everything is working fine, I set my properties and set the filter
and
the
report filters properly, but what I sould like to do, is on the
"Clear"
function it clears my Filter properties, but I would like the clear
to
also
clear and return the report to original format with no filters?

Any suggestions?

Here is my coding for my frminventorysearch

Begin Code:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] "
& "
=
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptInventory].Filter = strSQL
Reports![rptInventory].FilterOn = True
Else
Reports![rptInventory].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptInventory"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptInventory", A_PREVIEW
DoCmd.Maximize
End Sub

End Code *****
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

Filter a Report from a Pop-Up Form 1
Form Filter? "Enter Parameter Value" 1
SQL Form Filter Issue 2
PopUp form to filter report 5
Improper coding for my Form Filter 8
Type Mismatch 1
Data type mismatch in criteira expression - HUH? 4
Using a filter to show results on a report 5

Top