473,569 Members | 2,876 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ


72 New Member
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?

Jan 6 '21 #1
9 3584
3,653 Recognized Expert Moderator Specialist

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!
Jan 6 '21 #2
72 New Member
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_Ed it_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.
Jan 6 '21 #3
3,653 Recognized Expert Moderator Specialist

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....

Jan 6 '21 #4
72 New Member
"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.
Jan 6 '21 #5
3,653 Recognized Expert Moderator Specialist
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!
Jan 6 '21 #6
72 New Member
Oh damn, that is so right.., moot and dumb but so helpful. Really appreciate it, Thanks
Jan 6 '21 #7
3,653 Recognized Expert Moderator Specialist

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

Grace and peace to you!
Jan 6 '21 #8
32,564 Recognized Expert Moderator MVP
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.

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.
Jan 7 '21 #9
72 New Member
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
Feb 5 '21 #10

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

Similar topics

by: Aravind | last post by:
Hi folks. I have 2 forms, frmMain and frmHistory. frmHistory has a field called Return. frmMain has a command button called "Due Today" (which will henceforth be called as DT), which opens frmHistory and filters it to display records where the value of Return is Null
by: beowulfs | last post by:
Here's what I've got: I've got a form with combo boxes. you can select already existing company names or type in new ones. if you type in a new one, it prompts you to double click the combo box if you want to add a new company. when you double click it brings up the new company form so you can enter the rest of the information besides...
by: Lyn | last post by:
Hi, I have a Search input form which collects from the user a person's name. I am using LIKE with a "%" suffix in the SQL so that the user does not have to type in the full name. When they hit the Search button, a query is run to search the Person table for a match. This produces a recordset (I am using ADO). If the RecordCount is zero,...
by: Thelma Lubkin | last post by:
formA determines some_where and some_value and issues docmd.close ,Me docmd.openform "formB", , ,some_where, , ,some_value formB receives the correct some_where and some_value After completing its work formB issues docmd.close ,Me docmd.openform "formA"
by: John Welch | last post by:
I have a command button with the following code: DoCmd.OpenForm "frmSearchAssignments", , , "SearchAssignmentID = 1" (SearchAssignmentID is the PK, auto number) When it runs, the form opens but shows all records, rather than going to the one I want. If I look at the form in design view, it shows 'SearchAssignmentID=1' (without quotes) as the...
by: Mike | last post by:
I am trying to open a search results form based on the input from a prompt form. I am using the following code: --- Begin Code --- Private Sub btnSearch_Click() 'Dim Variable and assign data Dim srce As String, fstnme As String, lstnme As String, bidnum As String srce = Me.Source_Control If Not IsNull(Me.Text18) Then
by: rturpyn | last post by:
I want to simply open a form, I don't want to limit the records returned. I'm using the code.... DoCmd.OpenForm "frm_GSG_Access_Rights", acFormDS, , When I enter something in the where ("Elist_Caption = 'GSG Model'") it does return my form with only 1 record. What am I missing? Thanks, -Turp
by: gavm360 | last post by:
Hello, im trying to open a form from an dialog box form: the button on the dialog box has this on the onclick event: DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" & Me!txtCCODE & "#" the form I'm trying to open is "frmcases_union" and im trying to filter it by the "MCH_CASECODE" which i type into
by: RAG2007 | last post by:
Hi all, Have a question I can't get around. I've done this before and for some reason can't get it to work this time. I'm opening a form through docmd.openform, and trying to get it to open an blank form to allow me to add a new record. Problem is, every time I run the method, it opens up the first record in the database, rather than give me...
by: silen | last post by:
i am using Access2000. Currently i had a main form call "frmDownload" and sub form "fsubAdmmission". Under the fsubAdmmission, i do have another sub form "fsubHospital". Once i link my application to database A, when "frmDownload" loaded, it does successfully create "fsubAdmmission" object and i am able to assign...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.