Filter form based on query result

[John Garvey]

I have a form frmItems, with control source tblItems. The form is searchable
based on control ReleaseDate, which holds the relase date for each item. I
need to eliminate that control from the form because I am eliminating the
field ReleaseDate from tblItems. This is because we now keep a separate
history of the release dates for each item in tblRelDates.

Now frmItems has a new subform (frmItemsSubRelDates) with control source
tblRelDates. I would like to be able to filter the frmItems based on the
current relase date for each item. I am having trouble figuring out how to
do that.

I have qryLatestRelDates that calculates the latest release date for each
item. I created another query based on this, qryLatestRelDatesForItemsForm,
where the ItemID criteria is [Forms]![Items]![Item ID]. On the form I put a
text box with source =DFirst("[RelDate]","[qryLatestRelDatesForItemsForm]")
but I guess I can't filter the form based on this since it's not part of the
form's record source? Can I somehow use the result from a query based on
this to filter the form? I do need to make the form searchable by the relase
date.

Thanks in advance for advice on this topic.

 

[John Garvey]

Thanks. That seems like a good idea and I tried it, but the filter took
forever to run and my whole system bogged down. I also tried it with DLookup
and got the same result. What do you think - any suggestions?
--
John Garvey

hi there
it looks like you should combine the query used to get the DFirst function in
your form's query in some way... but anyhow, to take the short path for now,
add the DFirst function you put on the form to the form's underlying query
instead, then it comes out to the form as a normal field which you can filter
by. something like
[LastDate]=DFirst("[RelDate]","[qryLatestRelDatesForItemsForm]","itemID=" &
[ItemID])

good luck
Erez

I have a form frmItems, with control source tblItems. The form is searchable
based on control ReleaseDate, which holds the relase date for each item. I
need to eliminate that control from the form because I am eliminating the
field ReleaseDate from tblItems. This is because we now keep a separate
history of the release dates for each item in tblRelDates.

Now frmItems has a new subform (frmItemsSubRelDates) with control source
tblRelDates. I would like to be able to filter the frmItems based on the
current relase date for each item. I am having trouble figuring out how to
do that.

I have qryLatestRelDates that calculates the latest release date for each
item. I created another query based on this, qryLatestRelDatesForItemsForm,
where the ItemID criteria is [Forms]![Items]![Item ID]. On the form I put a
text box with source =DFirst("[RelDate]","[qryLatestRelDatesForItemsForm]")
but I guess I can't filter the form based on this since it's not part of the
form's record source? Can I somehow use the result from a query based on
this to filter the form? I do need to make the form searchable by the relase
date.

Thanks in advance for advice on this topic.

 

[John Garvey]

Thanks for the great explanation. I checked and did have the indexes in
place, so I think I'll stick with plan B (yours and mine) where I already
have the release date field in the Items table. It's not "pristine" from a
normalization standpoint, but it makes everything else work so smoothly I
think I'll hang on to it.
--
Thank you again,

John Garvey

hi again
it's true that using dfirst, dlookup and the like inside a query slows the
query down considerably
first thing, make sure this date field is indexed in the source table

second, try building a query that brings only the ID and firstDate (something
like "SELECT Table1.Id, First(Table1.TheDate) AS FirstOfTheDate
FROM Table1
GROUP BY Table1.Id;")
if this query brings results fast, join it with the other query (instead of
the DFirst) by the ID field into a new query and use that query as data
source of the form

and last (if other things didnt make it right) when i encounter situations
like this in my applications, i add a new field to the source table (say it's
clients table, then i add a [FirstOrderDate] field or something like that)
then update it the first time the client has an order, then i have this data
at my (quick) disposal. it's a less-normalized database, but it runs much
faster.


hope that helps
Erez

Thanks. That seems like a good idea and I tried it, but the filter took
forever to run and my whole system bogged down. I also tried it with DLookup
and got the same result. What do you think - any suggestions?

hi there
it looks like you should combine the query used to get the DFirst function in

[quoted text clipped - 29 lines]

Thanks in advance for advice on this topic.