Get count of SQL statement in VBA form code?

[twgonder]

I've got a search form, it's continuous.
As it's data source it has a default SQL statement.


Based on what the user is searching for (i.e. FirstName "Bob") a new SQL replaces the data source in the form.
I'm not using the Navigation Buttons because the search box (at the bottom) confuses people, even if it did work. (Besides, that's what my form does, search.)
A status label within the form is multilingual, so I need to build the caption to display using translations.

I'm looking for the way of seeing how many records were selected from a population of n by the SQL data source.
I want to add those two "variables" to the status label caption that I build.
Something like: Status: 59 records selected from 10.948 (it's "." for the thousands separator in Spanish and many other languages)
Are these two record counts available in a form's VBA?
Is there a number format that displays in the style of the realm from within VBA?

Thanks

[Micron]

Seems odd to load a search form with all records, then repeatedly requery it as criteria is applied. If you must, then perhaps get a recordsetclone.recordcount at the beginning (i.e. all records from the underlying table or query), then again when the search is all done. Why would you not use a standard search form that loads all the criteria then open this continuous form using the criteria chosen.

I would think that the numbers ought to be represented as they should because Access is using symbols/formats as determined from the user's regional settings in Windows. Then again, I don't have the multi-regional experience so can't be sure.

[orange]

What is the default recordsource of the form?
I would think
DCount("*","Forms Recordsource") would identify total records involved--your n of "n".

or adapting your search recordsource
eg.

SELECT count(Employee.EmpId)
FROM Employee

where Employee represents your recordsource

and to get count of found records

SELECT count(Employee.EmpId)
FROM Employee
where empId >8

empId >8 represents your search criteria

Can you show some sample data sources and criteria?

[twgonder]

...
Can you show some sample data sources and criteria?

Maybe two pictures are worth a thousand words?
(I had to cut off some of the sensitive information, but it should give a good idea what's going on)

[CJ_London]

As far as your question is concerned, you have answers to your question and there is another one below, but I have to question your method. By returning all the data initially, the damage in terms of efficiency is already done - you might as well use a filter rather than applying a new recordset - unless you are concerned that between opening the form and another user making a change to the data which would affect the returned results.

Further, the use of the initial * is also inefficient as it negates the use of indexing - better to train users to use it when required

With regards DtInact, a better formula would be

nz(Dtinact,Date())>now()

which (as per per your formula) will exclude all records where dtInact is todays date or earlier

With regards my suggestion

I would use the recordset recordcount property to determine number of records

you might use code like

dim rs as dao.recordset
set rs=currentdb.openrecordset(sqlstr)
rs.movelast
recsReturned=rs.recordcount
set me.recordset=rs

[twgonder]

As far as your question is concerned, you have answers to your question and there is another one below, but I have to question your method....

dim rs as dao.recordset
set rs=currentdb.openrecordset(sqlstr)
rs.movelast
recsReturned=rs.recordcount
set me.recordset=rs

These are all good points, I gratefully accept the experience you provide.
For the most part, my efforts so far have been in getting a working model close to the idea I want. Then refine it.
I have these questions in mind, like what will happen when the table has a million of more Entities?
For development, I have just a few records, an admitted limitation for proper perspective.
I've asked in the past, what Access does with large queries; Is it all at once or piecemeal?
Since it's a "blackbox", it's hard for me, now, to anticipate what will happen with large data sets in each particular case (form, report, query, etc.)

The initial version seen in my example gets everything.
I've been toying with how to limit it.
Do I add a field called DtAct (date last active) and have every app that might touch the Entity in some way update this field?
Then I can select for the initial pass just those in the past specified period (week, month, year depends on the user and their volume)?
Just one idea. Or maybe just limit it to the first 100 records? Or maybe no records until a search criterion is entered?
But once a user specifies a search criterion, then we have to parse the entire table, which with hundreds of thousands of records could be taxing too.
As the old saying goes, the devil is in the details.

There is a triple state check box for the IsActive, which works off the DtInact field. I'll play with your formula for that. I'm always happy to learn simpler/better expressions.
Note: This is what I got to work: nz(DtInact,Date() +1)>now() however that will drive someone nuts in the future trying to figure out the logic (if they don't have an inactive date then make them inactive tomorrow, which never arrives).

As for using the filter option, I'm open to playing with that, I just haven't done it yet, by applying it to an existing record source, that might be limited based on the above discussion of limiting the initial load. It gets pretty complicated fast as then I have to build a source, maybe twice and then have the filter on top of a second pass. Everything now is fast as it all sits in memory on a development machine, but I know it's something I'll have to deal with in a split and networked environment. I haven't yet seen a good working example that I can emulate.

For this question though, I was just focusing on how to get the counts into the status label. But hey, thanks for taking the heat off of me (for question creep) by addressing the elephant in the room.

If I have DAO code in addition to the form source and filter, it gets even more complicated making sure the code you showed is always in synch with the form source and filters, unless there is a better way to bypass the form source and apply the DAO record set directly to the form source at load and each iteration of the search criteria (I kinda like that idea if it's possible).

[Minty]

To echo CJ's comments - when you are working with data from a networked/server connection the best method is to load an empty form, get some search criteria from the user, then either tell them they'll get 10,000 results and ask them to refine things further, or load the more restricted data set. Also unless you need editing capabilities always use a Snapshot recordset, it's quicker.

If you need to filter further then rebuild the rowsource and apply.
It should still be more efficient than filtering a large dataset.

[CJ_London]

what will happen when the table has a million of more Entities?

As you have it at the moment, form will take a long time to load and - it may appear to load quickly because the form will display the first few records, but it will continue to load the rest and the form won't be operational until all records are loaded. Easy test - temporarily display the record navigation bar at the bottom - time how long it takes to display the number of records. There are number websites which will create dummy data for you, not too difficult to create 1m records for testing purposes. As far as the initial '*' is concerned, if indexing is negated, then the query has to do a sequential search of all records - which will take time

I've asked in the past, what Access does with large queries; Is it all at once or piecemeal?

I presume y6ou mean ACE since Access doesn't actually execute queries - so same as any other rdbms - In the sense you can't stop it , all at once. If you have query X and then create a new query Y that uses query X, Query X will execute every time you execute query Y

I've been toying with how to limit it.

pretty sure we've been here before. Have your form return no records on open - I use something like this

SELECT * FROM myTable WHERE False

Since it's a "blackbox", it's hard for me, now, to anticipate what will happen with large data sets in each particular case (form, report, query, etc.)

Not sure why you think it's a black box, it's very simple:

ensure all fields regularly used for linking, sorting and filtering are indexed. The possible exceptions are those fields with a limited range of values such as booleans or days of the week. If the field is likely to have a lot of nulls - modify the index to ignore nulls.
where possible, use numbers rather than text for indexed fields (they take up less space so more can be processed at a time - a 10 digit number takes 4 bytes, a 10 digit text will take 22 bytes) - disk reads (an OS parameter) 4096 bytes at a time, so simplistically in the case of 10 digits the OS will return 1024 numbers or 186 text values so for text will need to do 5 reads against 1 for numbers.
Avoid domain functions

pretty sure I've sent you these links before
https://www.access-programmers.co.uk/forums/threads/addressing-performance-issues.291269/
https://www.access-programmers.co.uk/forums/threads/why-indexing-is-important-for-good-performance.291268/

[twgonder]

As far as your question is concerned, you have answers to your question and there is another one below, but I have to question your method. By returning all the data initially, the damage in terms of efficiency is already done - you might as well use a filter rather than applying a new recordset - unless you are concerned that between opening the form and another user making a change to the data which would affect the returned results....
[/CODE]

So, I decided to use a field I already had (and had forgotten about) for the initial data set of the form. The field is DtFgnUsed and is the date a foreign table used this Entity record.

But now I have a big ??? as to how to use it.
I have a control table designed for the back end, and in there I added a record 39_DtLimit, and in that is a field Pni4 which contains an integer number of days.
So, in the initial filter I want to say something like: WHERE DtFgnUsed >= Date() - Pni4 (meaning it's a recently used Entity).
Can one just put a dlookup in for pni4 or is there a better way with an expressions to get a particular value from a specific record from a specific table?
This would go into the initial form filter, which can then be replaced with another search criterion later.

As a related question, if the form source is set to the entire table, and the filter is set as above and there is an index to the DtFgnUsed, would a parse of the entire table be avoided?

If the db is split, will the backend pass just the data the form requests (via filter), or will the entire table be passed for the front-end to deal with?

[twgonder]

... Also unless you need editing capabilities always use a Snapshot recordset, it's quicker.

If you need to filter further then rebuild the rowsource and apply.
It should still be more efficient than filtering a large dataset.

There is no need to edit the data from Entity, it's purpose is to just return an ID to a textbox, combo box or another form as it's filter.
When you say "rebuild the rowsource" are you saying don't adjust the form filters and instead redo the Form Record Source (as I was doing before)?
I just asked a question related to this in post #9.

[Minty]

As CJ says if you already have the records loaded there is no point reloading them, just filter them.
If the overarching criteria change then reload.

If you have a "date last looked at" and you'll use it all over the place make it a global property that can be referred to anywhere like a global variable.
Have a look here : https://www.devhut.net/self-healing-object-variables/

[CJ_London]

As a related question, if the form source is set to the entire table, and the filter is set as above and there is an index to the DtFgnUsed, would a parse of the entire table be avoided?

should be avoided - I never bring through an entire table except perhaps for lookups or tables which will only ever have a few records (such as a list of countries)

Can one just put a dlookup in for pni4 or is there a better way with an expressions to get a particular value from a specific record from a specific table?

don't understand the question - where would the dlookup be used?

[twgonder]

...where possible, use numbers rather than text for indexed fields ...

pretty sure I've sent you these links before
https://www.access-programmers.co.uk/forums/threads/addressing-performance-issues.291269/
https://www.access-programmers.co.uk/forums/threads/why-indexing-is-important-for-good-performance.291268/

Unfortunately, as I get more and more involved in data from situations outside the USA, I realize that what you would think of as a number, isn't universally so.
Some countries add letters to their "zip codes". Some state Ids like driver's license number have a letter. Some national Ids are in theory all numbers, where they place a "." or "-" on a document for formatting, when removed, creates duplicate pure numbers (how much time did I lose after discovering that?). Some addresses don't even have a number as location reference on a street! Aggghhhhh

My Google search provided only one adequate pay-per-view source for large quantity of people data for testing, and it was missing a lot of things I need, like birthdate and nationality. If you've used one that you think is complete enough for international testing, I would appreciate a link.

You may have sent the links given before, I'll look at them and see if they look familiar.
Thanks

[twgonder]

...
pretty sure we've been here before. Have your form return no records on open - I use something like this
SELECT * FROM myTable WHERE False
...

With sophisticated users that's a possibility, but in my experience, users will just go "doh" and stare at the form, waiting for something, anything to appear. For me it's best to show something to get them started. Even Name 1 may confuse some, so actually seeing a first name is a good prompt. But yeah, I don't want to wait minutes and minutes for "something" to load and for the form to work.

[twgonder]

... If you have a "date last looked at" and you'll use it all over the place make it a global property that can be referred to anywhere like a global variable.
Have a look here : https://www.devhut.net/self-healing-object-variables/

I'll take a look. I have control tables for the backend and frontend. There are hundreds of little constants stored in the fields that control a myriad of things.
Another example is a minimum and maximum for things like dates and currency, that limit what can be entered in each application module. I.E. payroll has different limits for pay than say an order entry system would allow for a big product or service sales. They can be tailored for each client, or even each backend node in a network.

So, I'm looking for a simple way to extract these control constants into expressions and WHEREs, without having to write a true/false function for each one.