Problems with ApplyFilter

  • Thread starter Thread starter Bill MItchell
  • Start date Start date
B

Bill MItchell

Hi,

I have created a DISTINCT query based upon various search
paramaters. The query is returning a list of "Contact
ID" numbers from my table "MAIN: Contact"

I want my continuous list form to refresh and show only
these records. I can do this by setting
the .RecordSource property of my form to my new query
like so:

Forms![CONTACT:]![CONTACT: List].form.RecordSource
= "REC: Search | Contact (All)"

This works, however, because my query is DISTINCT (it has
to be for various reasons), I cannot then edit any of my
records. This is a problem.

It seems the best solution would be to use the
ApplyFilter procedure to show me only the records that
equate to the "Contact ID" field in my query.

Problem is, I have no idea how to do this.

What I need is just the simple code to say, Ok, I have
this query named "REC: Search | Contact (All)". I want
to use this as a filter and show only those records in
that query on my continuous form. I need to be able to
edit those records even though my query is DISTINCT.

I suppose I just want to put a button on my form that I
can click and it will make all this happen.

If anyone can help me would be greatly appreciated.
 
First, I going to suggest that you avoid using system delimiters in object
names. Stuff like ":" or the pipe character | is just asking for trouble. If
any developer of mine brought me names like that, they likely would be
terminated on the spot.

I don't even allow spaces in field names, or object names. It is pain to
code, and systems like sql server etc don't even allow spaces in field
names. The use of special characters in the object names often will cause
problems. If you ever need to migrate this system to sql server, you are
selling out your future by not adopting some coding standards and avoiding
things like spaces, and delimiter characters in your names.

Ok, enough of the above. You can simply change the distinct query to only
return the "id".

You then create a nice non distinct query that has the correct fields and
sorting for the continues form (and is a updateable query).

You then go:

Dim strMySql As String

strMySql = "select * from qryAnsers where id in (select id from
DistinctQuery)"

Me.RecordSource = strMySql

In your case we get:

Dim strMySql As String

strMySql = "select * from qryAnsers where id in (select id from ["REC:
Search | Contact (All)]"

Me![CONTACT:]![CONTACT: List].form.RecordSource = strMySql
 
Me![CONTACT:]![CONTACT: List].form.RecordSource = strMySql

That should be:

Me![CONTACT: List].form.RecordSource = strMySql

The above assumes the code is running in the form.
 
Al,

Thanks but you kind of lost me there.

When you write - strMySql = "select * from qryAnsers
where id in (select id from DistinctQuery)"

I've never seen the '(select id from DistinctQuery)'
portion used before within the "In" clause like that.

Lets say my DistinctQuery is returning 500 records with
distinct id's. I want my form to list the records for
these 500 id's in an updateable query once I am done.

What exactly would be the phraseology used? How does the
entire list of 500 distinct id's get into the "In" clause
there?

Thanks in advance.

Bill
 
It is perfectly legal to do the following

select * from tblEdit where id in (3,4,5)

It is also perfectly legal to do:

select * from tblEdit where id in (select id from tblTest)

So you can feed the condition with another select, and YES, that select can
return multiple values for the condition.

so, I used the name DistinctQuery, but you would replace that with your
actual query name that you created and used (the one that is NOT updatable).

What exactly would be the phraseology used? How does the
entire list of 500 distinct id's get into the "In" clause
there?
Click to expand...

Hum, not sure what it is called!! I think it just called a sub-query, or a
sub-select. You can also use a query for doing lookups. Lets pretend we have
a combo box of colors, but we actually store the id

tblColors
id Color
1 Red
2 Blue
3 Green

tblPeople
Name FavorateColor
Albert 1
David 3

To find all the people with favourite color of red, you can go

select Name, FavorateColor where FavorColor = (select id from tblColors
where Color = "Red")

In the above, I thus did NOT need to actually know the actual color id for
the lookup.

You can also fetch the lookup value using a sub-query also.

select Name,(select color from tblColors where tblPeople.id = tblColors.id)
as FavColor
from tblPeople

so, for lots of expressions, you can use a query. And, when using a the "in"
clause, you can test against more then one value.

And, in reading my original response to you, I was a harsh on my comments
about using special characters in your query name. My apologies.
 
Back
Top