By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Can a form filter use a pass through query?

P: 13
I'm trying to filter the records in a form using a pass through query.
Expand|Select|Wrap|Line Numbers
  1. strFilter = strFilter & "( eventid in (select FWIPEvent.eventid from FWIPEvent )) "
  2. Me.Filter = strFilter
  3. Me.FilterOn = IIf(strFilter <> "", True, False)
  4. Me.Requery
The FWIPEvent is the pass through query. I use a pass through query as it is a complex query to select specific events and hence runs much faster as a pass through query.

However, it does not work, in that though the code executes, all records are returned.

If I create the query in Access using linked tables then it works correctly.

Any suggestions?

Thanks for the help

Paul
Sep 6 '08 #1
Share this Question
Share on Google+
16 Replies


ADezii
Expert 5K+
P: 8,627
The Filter property is essentially a String expression consisting of a WHERE clause, but without the WHERE Keyword. I do not think that it can be used in the context which you describe.
Sep 6 '08 #2

NeoPa
Expert Mod 15k+
P: 31,429
I like your thinking Paul :)

I'm assuming that running the [FWIPEvent] query generally produces the same results as the replacement query that used the linked tables?

I've not used Pass-Thrus much, but if I remember correctly, they can have a tendancy to convert results to strings.

Try the following to see if it yields results :
Expand|Select|Wrap|Line Numbers
  1. strFilter = strFilter & "([EventID] In (SELECT Val([EventID]) FROM [FWIPEvent]))"
  2. Me.Filter = strFilter
  3. Me.FilterOn = (strFilter > "")
  4. Me.Requery
I'm assuming the strFilter value at the start already ends in " AND "?

If this doesn't work, last chance saloon, create another, standard, query (named [qryFWIPEvent]) which is the equivalent of :
Expand|Select|Wrap|Line Numbers
  1. SELECT Val(FWIPEvent.EventID) AS EventID
  2. FROM [FWIPEvent]
Use this as :
Expand|Select|Wrap|Line Numbers
  1. strFilter = strFilter & "([EventID] In (SELECT [EventID] FROM [qryFWIPEvent]))"
If that doesn't work then I'm really out of ideas :(
Sep 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,429
The Filter property is essentially a String expression consisting of a WHERE clause, but without the WHERE Keyword. I do not think that it can be used in the context which you describe.
That is quite correct ADezii, but Paul has used a string expression that simply uses some more advanced SQL in it. It is a perfectly valid WHERE clause without the "WHERE " :)
Sep 6 '08 #4

P: 13
I like your thinking Paul :)

I'm assuming that running the [FWIPEvent] query generally produces the same results as the replacement query that used the linked tables?

I've not used Pass-Thrus much, but if I remember correctly, they can have a tendancy to convert results to strings.

Try the following to see if it yields results :
Expand|Select|Wrap|Line Numbers
  1. strFilter = strFilter & "([EventID] In (SELECT Val([EventID]) FROM [FWIPEvent]))"
  2. Me.Filter = strFilter
  3. Me.FilterOn = (strFilter > "")
  4. Me.Requery
I'm assuming the strFilter value at the start already ends in " AND "?

If this doesn't work, last chance saloon, create another, standard, query (named [qryFWIPEvent]) which is the equivalent of :
Expand|Select|Wrap|Line Numbers
  1. SELECT Val(FWIPEvent.EventID) AS EventID
  2. FROM [FWIPEvent]
Use this as :
Expand|Select|Wrap|Line Numbers
  1. strFilter = strFilter & "([EventID] In (SELECT [EventID] FROM [qryFWIPEvent]))"
If that doesn't work then I'm really out of ideas :(
Thanks NeoPa, but unfortunately neither option worked.

The query works perfectly (if slow) against a linked table and yes the first strFilter does contain an "AND".

I'll have to write a query against a linked table and suffer with the performance. Its weird - would of thought that it would of worked but not.

Thanks for the help

Paul
Sep 7 '08 #5

NeoPa
Expert Mod 15k+
P: 31,429
Is [EventID] a numeric field as I'd assumed?
Sep 7 '08 #6

P: 13
Is [EventID] a numeric field as I'd assumed?
Yes it is - I have tried using INT too but this doesn't work either.

The interesting thing is that if I use
strFilter = ([EventRoom] = "Sparc" AND code above )
it doesn't even filter on the EventRoom. If I don't use the code above then the data is filtered by the Event Room.

Cheers

Paul
Sep 7 '08 #7

NeoPa
Expert Mod 15k+
P: 31,429
OK Paul.

That last comment intrigues me (and reminds me that your response to my " AND " question was 'Yes it is "AND"'. Not quite the same.

What I suggest you do is add a line just before you use the filter to display the strFilter value to the Immediate Pane where you can copy it into here (Ctrl-G for the Immediate Pane) :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strFilter
Sep 7 '08 #8

ADezii
Expert 5K+
P: 8,627
Forgive me if I appear to be over simplifying matters, but something just came to mind.
  1. You are applying a Filter to the Form, turning ON the FilterOn Property (True), then Requerying the Form. Won't the Requery action negate the Filter and revert to the original Record Source for the Form? This will appear like your Filter is not working, but in fact it is, but it's getting turned OFF again once the Requery occurs.
  2. Once you perform the Filtering actions, you should not, and there is no need to, Requery the Form, is there?
Again, I apologize if I over simplify, and it's probably something I've overlooked, but if I am incorrect, please let me know. If all else fails, I do have a solution, but I must warn you that it is off-the-wall, and I'm not sure how effective it will be.
Sep 8 '08 #9

P: 13
Hi All

Just to let you know I've found a solution but no idea why it works.

In stead of using
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = strFilter
  2. Me.FilterOn = IIf(strFilter <> "", True, False)
  3. Me.Requery
  4. strFilter = ""
use
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = strFilter
  2. Me.FilterOn = IIf(strFilter <> "", True, False)
  3. Me.Refresh
  4. strFilter = ""
For some reason the "refresh" works and requery doesn't - weird.
Sep 10 '08 #10

NeoPa
Expert Mod 15k+
P: 31,429
Paul,

I've no idea how you've got those results :S

I've tried it with Me.Requery and it works absolutely fine for me.
Sep 10 '08 #11

NeoPa
Expert Mod 15k+
P: 31,429
Forgive me if I appear to be over simplifying matters, but something just came to mind.
  1. You are applying a Filter to the Form, turning ON the FilterOn Property (True), then Requerying the Form. Won't the Requery action negate the Filter and revert to the original Record Source for the Form? This will appear like your Filter is not working, but in fact it is, but it's getting turned OFF again once the Requery occurs.
  2. Once you perform the Filtering actions, you should not, and there is no need to, Requery the Form, is there?
Again, I apologize if I over simplify, and it's probably something I've overlooked, but if I am incorrect, please let me know. If all else fails, I do have a solution, but I must warn you that it is off-the-wall, and I'm not sure how effective it will be.
As far as I can see ADezii, this understanding is not quite correct.

Changing the filter does seem to have the effect without the .Requery, but the .Requery doesn't reset the filter properties.
Sep 10 '08 #12

ADezii
Expert 5K+
P: 8,627
As far as I can see ADezii, this understanding is not quite correct.

Changing the filter does seem to have the effect without the .Requery, but the .Requery doesn't reset the filter properties.
Thanks NePa, wouldn't be the first time my understanding was incorrect and I'm sure it won't be the last! (LOL).
Sep 10 '08 #13

P: 13
Paul,

I've no idea how you've got those results :S

I've tried it with Me.Requery and it works absolutely fine for me.
I really don't understand it - maybe there is something wrong with my form settings - I'm a newbie for Access. When I use me.requery it sets the form filter in the form properties to the correct statement (after execution) - but does not apply it i.e. the records are the same as before - no filtering. When I use refresh it sets the form filter in the form properties and then applies it - i.e. the records are filtered correctly. I really don't understand the difference between the two operations.

Thanks for the help and problem for me is now resolved. Much better performance ;o)

Paul
Sep 10 '08 #14

NeoPa
Expert Mod 15k+
P: 31,429
Re-capping a bit here Paul, but does that mean you have it working from the Pass-thru query now?
Sep 10 '08 #15

P: 13
Yes I have been able to use a pass-thru query to filter a forms records.
Sep 11 '08 #16

NeoPa
Expert Mod 15k+
P: 31,429
Oh, that's great news. I'm very pleased for you - and now we know it's possible if we get a similar question too. It's good when we learn things too ;)
Sep 11 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.