How can I refresh the fliter attribute

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

I find that reports sometimes get "stuck" with filters that are applied in
VB scripts - probably from irregular exits, or maybe working while in
development mode? Dunno. I would like to refresh the filters before
opening the reports. How can I do that.

Also, I don't understand why the following script wouldn't wipe out that
filter that I find in "properties" on the report, but it doesn't seem to.
If I don't open properties and delete the spurious filter, I get no rows
returned in my report, even thought the following ls_filter has a good
value, and works once the filter property is cleared.


Private Sub Report_Open(Cancel As Integer)
Dim ls_filter As String
ls_filter = "[class_code] = " & "'" &
[Forms]![frmClassAttendanceInput]![txtClass] & "'" _
& " AND [Att_Date] = " & "#" &
[Forms]![frmClassAttendanceInput]![txtDate1] & "#"
DoCmd.ApplyFilter , ls_filter
Me.FilterOn = True
End Sub
 
Change this line:
DoCmd.ApplyFilter , ls_filter

to this line:
Me.Filter = ls_filter
 
OK, now I think I have a variation on this problem. I believe your
suggestion is working when the "stuck" criterion is found, literally, in the
filter property of the form itself. But there is another situation where
the "stuck" criterion is found as part of the Query that is behind a form.
That is, I have to open up the recordsource and then I see the "stuck"
criterion. In this situation, setting the Me.Filter doesn't get rid of the
critierion attached to the query. When I query is used inside of a script,
I "refresh" it with the following routine. I have too forms of the query,
one that is never used except to refresh the one that is used. But I don't
know how to refresh the query behind a form. I has already been opened by
the time I'm able to execute a script. (I think.) Surely I don't have to
have a different query for every form - each one varying only in name, so
they can't get "corrupted?" Or I don't have to have a "hidden" form that
does nothing but refresh the query behind the form about to be opened?

(E.g., I have a QueryScoresBase and a QueryScores, and I execute the
following Set qdfBaseQuery = CurrentDb.QueryDefs(as_BaseQuery)
Set qdfRefreshedQuery = CurrentDb.QueryDefs(as_RefreshedQuery)
li_order = InStr(1, qdfBaseQuery.SQL, "Order By")
ls_select = Left(qdfBaseQuery.SQL, li_order - 1)
ls_sort = Right(qdfBaseQuery.SQL, Len(qdfBaseQuery.SQL) - (li_order -
2))
ls_sql = ls_select & ls_where & ls_sort
qdfRefreshedQuery.SQL = ls_sql
qdfBaseQuery.Close
qdfRefreshedQuery.Close
 
If I'm understanding correctly, you're saying that the "WHERE" expression in
a query that is serving as the recordsource for a form has values already in
it and they're not the ones you want to use for that form? How does that
query get those values and store them -- by a "refresh" routine similar to
what you've posted that is run at some earlier time? or by some other means?

Need to understand how the query gets its filtering values in your process
in order to better identify what might be done to solve this problem for
you.
 
Ken Snell said:
If I'm understanding correctly, you're saying that the "WHERE" expression in
a query that is serving as the recordsource for a form has values already in
it and they're not the ones you want to use for that form? How does that
query get those values and store them -- by a "refresh" routine similar to
what you've posted that is run at some earlier time? or by some other
Click to expand...
means?

Yes, there are times when I do rebuild this very query using code similar to
that below. So that's probably where it happens. If I could "catch" the
recordsource before it was actually put into use, I would copy my
qryScoresBase into qryScores as I do in other situations. I don't want to
actually use the clean qryScoresBase, because I'm afraid I'd corrupt it. If
I don't do anything but set Me.Filter at runtime, can I be confident there's
no way to corrupt the WHERE clause? No matter what kind of messy aborts
occur?
 
You can use the form's OnLoad event to run code that changes the form's
recordsource's query's WHERE string, store it (just as you do in your
current code), and then set the recordsource property to that query. Perhaps
that will solve your situation?
 
Sounds very promising. Certainly the sort of thing I was looking for.
Thanks for this and previous help.
 
Back
Top