Can a form filter use a pass through query?

[pabs1111]

I'm trying to filter the records in a form using a pass through query.

strFilter = strFilter & "( eventid in (select FWIPEvent.eventid from FWIPEvent )) "
Me.Filter = strFilter
Me.FilterOn = IIf(strFilter <> "", True, False)
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

[ADezii]

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.

[NeoPa]

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 :

strFilter = strFilter & "([EventID] In (SELECT Val([EventID]) FROM [FWIPEvent]))"
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")
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 :

SELECT Val(FWIPEvent.EventID) AS EventID
FROM [FWIPEvent]

Use this as :

strFilter = strFilter & "([EventID] In (SELECT [EventID] FROM [qryFWIPEvent]))"

If that doesn't work then I'm really out of ideas :(

[NeoPa]

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 " :)

[pabs1111]

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 :

strFilter = strFilter & "([EventID] In (SELECT Val([EventID]) FROM [FWIPEvent]))"
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")
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 :

SELECT Val(FWIPEvent.EventID) AS EventID
FROM [FWIPEvent]

Use this as :

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

[NeoPa]

Is [EventID] a numeric field as I'd assumed?

[pabs1111]

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

[NeoPa]

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) :

Debug.Print strFilter

[ADezii]

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.

[pabs1111]

Hi All

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

In stead of using

Me.Filter = strFilter
Me.FilterOn = IIf(strFilter <> "", True, False)
Me.Requery
strFilter = ""

use

Me.Filter = strFilter
Me.FilterOn = IIf(strFilter <> "", True, False)
Me.Refresh
strFilter = ""

For some reason the "refresh" works and requery doesn't - weird.

[NeoPa]

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.

[NeoPa]

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.

[ADezii]

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).

[pabs1111]

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

[NeoPa]

Re-capping a bit here Paul, but does that mean you have it working from the Pass-thru query now?