473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Can a form filter use a pass through query?

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
16 5083
ADezii
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
Is [EventID] a numeric field as I'd assumed?
Sep 7 '08 #6
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
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
32,556 Expert Mod 16PB
Re-capping a bit here Paul, but does that mean you have it working from the Pass-thru query now?
Sep 10 '08 #15
Yes I have been able to use a pass-thru query to filter a forms records.
Sep 11 '08 #16
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

7
by: rickcheney | last post by:
I just changed my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was clicked a Report was...
2
by: William Wisnieski | last post by:
Hello Everyone Access 2000 Looking for some suggestions and advice on how to proceed with this. I hope its not as complicated as it seems to me right now. I've got an unbound main form...
4
by: DebbieG | last post by:
I have a form based on this query: SELECT Students.LastSerDT, OtherInfo.Served, OtherInfo.HSGradYr, OtherInfo.ActivePart, OtherInfo.Served, Students.SSN, & ", " & & " " & AS Name,...
4
by: sparks | last post by:
We have a database that reads in and formats raw data. We were using queries to format the data per person and outputing reports. The other database has the persons personal information. I changed...
1
by: Keith | last post by:
A2003, Xp Pro. I've designed a form which contains 6 of combos. Three on the left are set up to select fields from a query, three on the right are set up to select values from the corresponding...
2
by: angie | last post by:
I need to figure out how to create a user interface to search a query, but here's the bad part...I need to account for criteria on at least 7 of the fields. Here's what I'm thinking I need to do:...
3
by: melnhed | last post by:
---Report the current filtered records from a Form--- Hello All, I've seen this topic discussed before, but the solution described then doesn't work in my particular case. My Config: ...
16
by: radio1 | last post by:
---Report the current filtered records from a Form (in an ADP project)--- Hello All, I've seen this topic discussed before, but the solution described then doesn't work in my particular case. ...
3
by: emgallagher | last post by:
I have a form which lists studies. People can filter the form based on details about the study, such as the study type. Currently users filter via the right click method. I would like to be...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.