467,092 Members | 1,225 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,092 developers. It's quick & easy.

Docmd.openForm

64KB
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
1 Week Ago #1

✓ answered by 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!

  • viewed: 1485
Share:
8 Replies
twinnyfo
Expert Mod 2GB
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!
1 Week Ago #2
64KB
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.
1 Week Ago #3
twinnyfo
Expert Mod 2GB
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!
1 Week Ago #4
64KB
"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.
1 Week Ago #5
twinnyfo
Expert Mod 2GB
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!
1 Week Ago #6
64KB
Oh damn, that is so right.., moot and dumb but so helpful. Really appreciate it, Thanks
1 Week Ago #7
twinnyfo
Expert Mod 2GB
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!
1 Week Ago #8
NeoPa
Expert Mod 16PB
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.
1 Week Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Aravind | last post: by
15 posts views Thread by Thelma Lubkin | last post: by
2 posts views Thread by Mike | last post: by
2 posts views Thread by rturpyn@gmail.com | last post: by
3 posts views Thread by gavm360@yahoo.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.