Can a query used as a filter have a sub-query?

[Clddleopard]

I am trying to open a form filtered by a subquery using a macro. The SQL of
the query (which is named Schednotyetapp) I would like to use as a filter is
the following:
SELECT TimeOffRequest.*
FROM TimeOffRequest RIGHT JOIN SchedulerNotYetApprovedsub ON
TimeOffRequest.TORequestID = SchedulerNotYetApprovedsub.TORequestID;
The query returns all of the fields in the form, but when I try to run a
macro to Open the form with Schednotyetapp as the filter, it just doesn't
filter. It returns all of the records. What am I doing wrong?

 

[Arvin Meyer [MVP]]

Save the select statement as a saved query. Use the query as the
recordsource for the form. Then let the macro open the form.

 

[a a r o n _ k e m p f]

Not if you're using Jet.

If you stack query on top of query in Jet, it randomly craps out.

_DEAD_SERIOUS_.

 

[Clddleopard]

Can I set the recordsource for a form programmatically? That is, I want one
form to show different data, so that if I tweak the look of the form, I don't
have to change three different forms. That's why I wanted to use this query
as a filter in the first place.

 

[Clddleopard]

That sucks. Any other ideas then?

Not if you're using Jet.

If you stack query on top of query in Jet, it randomly craps out.

_DEAD_SERIOUS_.

 

[a a r o n . k e m p f]

can you need to pass a filter clause to the form, it sounds much
easier than that.

In SQL Server land (ADP) you have the option to use 'ServerFilter' in
addition to the simple 'Filter' clause
This is just another reason that ADP is superior to Jet; because you
can put a filter so that Susie and only ever see Susies records.. and
you can let the users still filter and sort as they see fit.

Two is better than one in this regard.
And three tiers of Jet databases is never reccomended; I would
reccomend not listening to a lot of people in this group and upsizing
to a real database.

Jet is dead and it has been for a decade.

That is why SQL Server is the worlds most popular database.

-Aaron Kempf

 

[Arvin Meyer [MVP]]

Yes you can, and you can also change it when it's open and requery. You can
also do that for subforms.

The code for a mainform would be:

Me.RecordSource = "Saved Table or Query Name"

or:

Me.RecordSource = "Select * From Whatever Where ID = 4"

and the subform would be:

Me.NameOfSubformControl.Form.Recordsource = "Saved Query Name"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

Not if you're using Jet.

If you stack query on top of query in Jet, it randomly craps out.

_DEAD_SERIOUS_.

 

[Arvin Meyer [MVP]]

That sucks. Any other ideas then?

Please ignore Aaron. He's a troll who wanders the Access newsgroups. If you
have any doubts, please check both of our backgrounds. Mine can be easily
checked at the Microsoft website:

https://mvp.support.microsoft.com/profile/Arvin

 

[a a r o n . k e m p f]

Arvin;

Sorry, dork

But Jet query on top of Jet query.. it uh-- randomly craps out.

That's one of the main reasons I moved to SQL Server a decade ago--
because my time was too valuable to spend on a flaky flaky slow
database
That's one of the main reasons I moved to SQL Server a decade ago--
because my time was too valuable to spend on a flaky flaky slow
database
That's one of the main reasons I moved to SQL Server a decade ago--
because my time was too valuable to spend on a flaky flaky slow
database
That's one of the main reasons I moved to SQL Server a decade ago--
because my time was too valuable to spend on a flaky flaky slow
database

 

[a a r o n . k e m p f]

Meanwhile;
I'm a certified SQL DBA / Developer-- so uh.. ask someone who knows
about a 'pint-sized database' or someone that knows their shit.

Just because a couple of dorks in this group don't know how to spell
SQL-- that doesn't make me less right