Re: Loading recordset at runtime

"BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
news:OTqDmZ5DKHA.1376@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the reply, Albert. I guess I didn't make it clear that in some
cases the recordset will be modified after the form is opened. After
opening with the default recordset it may be necessary to look for all
Accounting records from Jan. 1 to the present, then to look for all
Maintenance records for the past two years, and so forth.

I do use the Where condition for reports.

Ah, ok, then use the forms "filter" option. The same text/examples I gave
will work for the filter.

eg:

me.Filter = "FinalApproval Is Null"
me.FilterOn = True

In fact your question kind of nicely points out when to use "where" clause
(at form/report open time),and when to use the filter option (ie: after the
form or report that is already open). We often see questions here as when it
better to use one or the other feature. "Where" clause ONLY works on the
open form/report..but the filter option continue to functions AFTER the
form/report is opened.

However, my personal preference is to build the sql string in code and just
stuff it into the forms record source. It not clear as to what your question
is in regards to using a query vs that of the base table?

In both cases (table/query) we are taking about using code to add additional
criteria to that sql, so you might as well use the base table since I see no
advantages to using a query (the only exception here is if the "base" sql
was very complex and messy. Then I do save it as a query and in code use the
"text" property of the query object to pull out the sql and then add-on my
sql.

In fact taking sql and modifying it (adding criteria) is better from a
performance point of view. Using sql in-line means the query optimizer is
forced to re-compile that string each time. This resulting in BETTER
performance then a query with parameters. (note that in your question I
don't believe you were asking about using a query with parameters, but I am
just giving an performance tip that it better to avoid a saved query with
parameters as compared to in-line sql in vba code).

Since you don't know what parameters you going to be using ahead of time,
then a query with parameters makes no sense here (unless you want to build
2-3 different queries). So, therefore using a query in place of the base
table also yields you nothing I can think of here.

So, either use the above filter suggestion (it designed for forms ALREADY
open), or just use the base table + criteria in an sql string. There is
little if any advantage I can see to using a query here.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


.

Relevant Pages

  • Re: Help with Microsoft help on linking Access to Outlook
    ... in the criteria cell in the graphical query builder. ... use as the source in the routine. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ...
    (microsoft.public.access.formscoding)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... and have the query point to the combo box to get its value. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ... Set MyRS = MyDB.OpenRecordset" ...
    (microsoft.public.access.formscoding)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... should build the SQL, using a query, then paste it into your code. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ... Set MyRS = MyDB.OpenRecordset" ...
    (microsoft.public.access.formscoding)
  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)
  • RE: need similar solution to "cascading combos"...
    ... that) as the basis of a query. ... Is there a way in code to access the subform's recordset in a query. ... This will cause Combo2 to filter it's data based on the value in Combo1 ... boxes, I used several unbound combo boxes for Customer Name, Product Name, ...
    (microsoft.public.access.forms)
Loading