Get count of SQL statement in VBA form code?

[Minty]

The self healing properties can be referenced to directly in a query, so say you have one called glEmpId that holds the current user id.
You can use it in a where clause, this will be way more efficient than a look up.

WHERE EmpID = glEmpID

[CJ_London]

I realize that what you would think of as a number, isn't universally so.

plenty of number outside of you neck of the woods which are treated as text - phone numbers for example or numbers that are so long they are outside the normal range of a long.

UK postcodes are alpha numeric (and I think around 32 different formats) as are passport and driving licence numbers. So please don't consider yourself to be facing a unique situation outside the experience of the rest of the world.

My point is rather that storing an invoice number as 'inv1234', store it as 1234 and add inv as and when required.

users will just go "doh" and stare at the form, waiting for something, anything to appear.

pathetic reason - how many websites don't display anything until some sort of filter has been selected (most in my experience). If it is a concern, include a label saying 'enter a filter to display something', or include it in your user manual.

regarding datasets, this is one I use on occasion

https://www.databasetestdata.com/

you can make up your own fields, set ranges, etc

here's another
https://www.onlinedatagenerator.com/

here's a link to a test data generator review site
https://www.softwaretestinghelp.com/...eration-tools/

just google 'test data generator', you will find more links

[twgonder]

I didn't think a dlookup would work, but it did.
Here is how it looks in a query.
I'll port the SQL where to the form.
The big question I still have to research is if the dlookup is going to run n times in a large form and what again the network impact is.

[CJ_London]

Since the dlookup is in the criteria and doesn’t reference fields in the rest of the query, it should only run the once

you could just have included the table in your query without a join and applied the criteria to return just the one record (no join queries are called Cartesian queries)

[twgonder]

Since the dlookup is in the criteria and doesn’t reference fields in the rest of the query, it should only run the once
...

I've seen samples of criteria in MS documentation that reference other fields in a calculation (i.e. field1 > field2 + field3). I'm guessing for that to work properly, the criteria would have to be calculated for each record parsed. That's what makes me wonder about the same dlookup running over and over again. Not a problem if it's all in memory, but if it's network traffic, then it becomes a concern. Hence my question about which side, BE or FE is doing the repetitive calculating work.

[twgonder]

@CJ_London thanks for the generator links. I guess I just didn't use the correct combination of magic words to get better search results.
My point was that any given country has some standards, like the USA for data, and I've learned that when it comes to most fields, you can't count on a number working internationally.

[CJ_London]

if you want to check the way your query is working, look at using jetshowplan. It's not well documented but see this link which also has further links
https://www.devhut.net/ms-access-jet-showplan/

The plan is created when you first run a query. Modify the query and it will be created again. If you make changes to the underlying schema (such as add an index) will not cause the plan to be updated.

Note that what is efficient for a query based on a small dataset is not necessarily efficient for a much large dataset - and visa versa.

there is also the analyse performance option in the developer tab

[CJ_London]

you can't count on a number working internationally.

If you are talking about the decimal separator, easy enough to use a replace and val function

val(replace("" & 123.45),".",",")

[twgonder]

If you are talking about the decimal separator, easy enough to use a replace and val function

val(replace("" & 123.45),".",",")

What I was referring to is a situation like this:
ID for person 1 = 1.234
Id for person 2 = 12.34
Remove the period and you have two people with the same ID.

In my case late yesterday, just by coincidence, I found out that another person has the same exact national ID number that I do.
They issue a cédula to citizens and one to foreigners with a residence VISA.
Mine is technically a C.E. and his is a C.C., but nobody pays attention to the letters because C.E.s are fairly rare compared to C.C.s,
hence I came up with a duplicate "number" which threw the entire appliance purchase into disarray.

Back to the original question:
1) Is there a "secret" system variable(s) after a SQL statement that has the counts?
2) If so, do these change with source and filter changes in a form?
3) Is there a way to bypass the source and filer in a form so that we can use the results from a SQL command?
4) Is there yet a better way to get these counts other than duplicating a query?

And since we already opened the can of worms of limiting the initial search, months ago I saw a command for SQL that would just sample, like maybe the first 100 records. There seems to be two commands (I forget what they are) depending on whose SQL you're using. I tried both but neither worked in my testing. Does anyone know what works in Access to do this?

[twgonder]

¡¡¡ NOTE: problem solved, see post below !!!

Based on all the posts here, I thought I would try a change,
but it's not working after 2 hours of fussing around.
The idea is to start with an empty record set.
I tried setting the record source to null, but the code won't compile for missing variable ID.
After that, I put a text box on the form for ID and removed deleted the table source. That works.
I also wanted to set the comparison for foreign used date to remove the dlookup that might be in each record iteration.

I set the form properties to this in the second try:


Then I have this code in the form Open procedure:

 Private Sub Form_Open(Cancel As Integer)
  Dim temp1 As Date...
  defSqlStr = "SELECT ID, Nm1, Nm2, Nm3, Nm4, EntityCd, DtBeg, DtFgnUsed, nz(DtInact,Date() +1)>now() AS IsActive FROM tbl_39_Entity; "
  temp1 = Date - DLookup("Pni4", "tblc_38_CntlBe", "ID='39_DtLimit'")     'foreign used in past n days
  defWhereStr = "DtFgnUsed>=" & temp1: WhereStr = ""
  defOrderStr = "Nm1, Nm3": OrderStr = ""
    
  Me.FilterOn = True: Me.OrderByOnLoad = True
  Me.Filter = defWhereStr: Me.OrderBy = defOrderStr
  Me.RecordSource = defSqlStr

I would think this would then force the form to use this SqlStr, which it seems to do,
as if I comment out the line setting defSqlStr no records are displayed.
However, the Filter and Order By don't seem to get applied.
I've checked in the Immediate window, and they are set properly.
I've tried rearranging the order of the Me. lines.

Does this make any sense?
Is this the wrong place to set Filter and Order By?

After post note: I tried setting the Order By in another part of the code. It doesn't work there either. Are we supposed to be able to change the Filter and Order by on the fly in a form? I would have thought so from post #7 if I understood it correctly. Or is it only, and only for form load. Some of the helps aren't clear on that.

[Minty]

Set the recordsource first then turn the filter on last.
Access will (I think from memory) check for a valid filter setting before applying it.

Why do you merge multiple statements into one line? It makes it much harder to read and follow, and has no obvious benefits?

Edit: In fact why not just load the record source with the where clause and make things efficient from the start?

[twgonder]

It's not a solution to the original question, but as for applying filters and sorts in VBA:

There were a few gotchas for the solution.
The order of setting the recordset, filter and order has to be in perfect order.
I had been trying to set the filter before the recordset to avoid parsing the whole file first. That doesn't work.
Also, coming from a place where we don't wrap dates with "#", I got bit in the filter code. It was set, just not functioning.

The final code looks like this (if anyone is following along):

Private Sub Form_Open(Cancel As Integer)
  Dim temp1 As String
   ... 
  defSqlStr = "SELECT ID, Nm1, Nm2, Nm3, Nm4, EntityCd, DtBeg, DtFgnUsed, nz(DtInact,Date() +1)>now() AS IsActive FROM tbl_39_Entity; "
  temp1 = "#" & Date - DLookup("Pni4", "tblc_38_CntlBe", "ID='39_DtLimit'") & "#"     'foreign used in past n days
  defWhereStr = "DtFgnUsed>=" & temp1: WhereStr = ""
  defOrderStr = "Nm1, Nm3": OrderStr = ""
     
  Me.RecordSource = defSqlStr
  Me.Filter = defWhereStr: Me.OrderBy = defOrderStr
  Me.FilterOn = True: Me.OrderByOn = True
...

[twgonder]

Set the recordsource first then turn the filter on last.
Access will (I think from memory) check for a valid filter setting before applying it.

Why do you merge multiple statements into one line? It makes it much harder to read and follow, and has no obvious benefits?

Edit: In fact why not just load the record source with the where clause and make things efficient from the start?

Thanks, before I saw your post I figured it out and posted.
I put logical groups of code together. For me it's easier than reading a page full of short statements and getting lost in it all.
I don't know if putting the where in the record source makes it more efficient. One would then wonder why MS did it this way.
The reason I separate the where is that the search form can then sort and filter based on user input.
The whole point was to avoid redoing the record source and just apply the filter/sort. MS was pretty stupid in their design if this isn't more efficient.

[Minty]

Think about it logically.

You are loading a restricted dataset.
Don't load all of it then filter it if you know the initial filtering - load it with the where clause initially.
Filter further afterwards. This is not inefficient. It's way more efficient.

[CJ_London]

1) Is there a "secret" system variable(s) after a SQL statement that has the counts? - No, for action queries (update/append/delete) there is a recordsaffected property, but not for select queries
2) If so, do these change with source and filter changes in a form? - N/A
3) Is there a way to bypass the source and filer in a form so that we can use the results from a SQL command? - use a recordset as I suggested post #5
4) Is there yet a better way to get these counts other than duplicating a query? - use a recordset as I suggested post #5

as an aside have you heard of Wha3Words? This describes a location anywhere in the world to within a square 3m x 3m

For example this is an address on a street in Bogota
https://w3w.co/cucumber.phantom.frame

This is a location in the middle of the Chingaza National Park

https://w3w.co/familiar.habituated.rediscover

Just thinking, with your international aspirations, potentially this provides you with a unique location ID anywhere in the world.