Docmd.openForm

[Neruda]

Hi, just wanted to clarify one thing.
Using Docmd.openform will always load all Records but they are filtered using something like

strWhere = "ProductID=" & Me!ProductID

In this case the form will show 1 record out of say 260 in total but the form recordset contains 260 records. Correct? this is not great if you only want 1 record. Correct?

The only way to have only 1 record loaded is to have a criteria written on the the query. Correct?


Thanks

[twinnyfo]

Neruda,

I am not sure of the nature of your question. I know that when I filter forms and do a record count on those records, only the filtered records are counted. Obviously, when the Form's record source is a query with only one record, the record count is 1, but if I apply filters which produces only one record, the record count ought also to be 1.

Hope this hepps!

[Neruda]

I kind of assumed that if I open a popup form this way (and i have 300 records in a table)

strWhere = "CategoryID=17"
DoCmd.OpenForm "frmProducts_Edit_Sbf", acNormal, , strWhere, , acWindowNormal

the form recordset would contain and show say 10 records but in reality the popup form loads everything and shows only 10 which is pointless if u really need 10 and only 10 records.

[twinnyfo]

Again,

I'm not sure of the nature of your question. What difference does it make if the Record Source itself contains a thousand records if the form is only filtering 10 records? In terms of overhead, if the query you use includes the criteria to filter only 10 records from thousands of records, the DB engine (as far as I know) goes through the same effort, you are just left with 10 records. So the process is a bit slower getting to the form, but the results are smaller. If you run a form directly from a table, the form may open more quickly, then you filter to get the 10 records.

The result is the same. You are displaying 10 records out of thousands. I'm not sure I understand your statement that this "is pointless if u really need 10 and only 10 records." This is how databases work! This is how filters work. This is how queries work. This is how you limit your total dataset to just a limited number of records so that you can manage them.

Again, I may be missing something here....

Thnaks!

[Neruda]

"is pointless if u really need 10 and only 10 records.

What I was wondering was: would it be efficient to load 5000 records and then filter 1 if u only need one. Shouldn't u load just one and that's it? I suppose if the time taken is the same so thats ok.

I asked this question because I open popup forms using docmd.openform with a filter and assumed the records I see are the only ones in the form recordset but that is wrong.

[twinnyfo]

Your question is actually moot (that is, it doesn't matter).

If you have a table with 5000 records and open a form on that table and filter on one record, the DB still has to do the work of finding that one record and displaying it.

If you write a query that uses criteria to find one record, the DB still has to do the work of finding that one record to display it on a form.

The only way to "just use one record" is to place that record in an empty table and open the form on that table. Many on this forum will now call out a hit on me for even suggesting such a thing--and rightly so! Bottom line here: DON'T DO THAT!

I think you may be trying to find a distinction here when there is none. Whenever you have multiple records in a table, there is no way to open just one record. You access the table and you can either display all records and find the one you need or filter the records based on your criteria. Either way, all records are accessed in some way to evaluate whether they meet your criteria.

I hope this explanation hepps!

[Neruda]

Oh damn, that is so right.., moot and dumb but so helpful. Really appreciate it, Thanks

[twinnyfo]

Neruda,

Please don't look at it as being "dumb." We all have misunderstandings about things as we learn. However, hopefullly this will hepp you understand your craft better in the long run.

Grace and peace to you!

[NeoPa]

I feel I must be missing something because this whole conversation seems to contradict everthing I think I know about Forms & their recordsets.

My expectation is that where a filter is properly applied (and it's hard to see from the existing posts whether this is true or not) then the recordset available to the Form will contain only those filtered records.

@Neruda.
You claim this is not the case so I'd be interested to hear what you have to say to support that contention. If it helps, using positional parameters instead of named ones when dealing with procedures with either a large number of parameters or any Optional ones is a really bad idea. It's easier, I know, but gives rise to so many potential problems - all of which happen only because the code hasn't been developed properly with the named parameters used. I went to the trouble of checking all your parameters were in the correct & appropriate positions, and they were, but seems a waste of everyone's time.

Why is it important that a recordset contains only those items that match the filter?
It's important, actually very important, because that recordset needs to be managed locally within your Access project. Jet/ACE manages the disk file for you and determines which records to include in the recordset. It gets paid the big bucks because that's a hard & technical job (Metaphorically speaking of course.), but it provides you with what you need. Providing you with more even when you've specifically asked for a subset, would be sub-optimal. Not serious in the scenario we're discussing here, but when dealing with larger recordsets it can be crippling. When using other back end types (Servers) this issue is even more important.

I'll wait for your explanation of why you believe more records were contained in the recordset than were being shown on the form.

[Neruda]

NeoPa,
looking at the Northwind and other forms I assumed that, when the record navigation shows " 1 of 300" and on the right the filter says "UnFiltered", the form would always load all the records but showed only the one specified by the "WHERE" clause.
I thought the filter on a form was a tool to manipulate the form recordset *after* the recordset was loaded. I didn't realize that applying a filter would actually requery the form.
I remember counting the records but still didn't believe it was correct. Result? Chased my tail.

Thank you