Trying to get a count of specific records to update a form value ... Queries work but VBA is not

[bxdobs]

(Developing for ACCESS 2003)
Attempt 1 using

QRY1

SELECT [a].aCID, Count([a].Name) AS CNT
FROM [Select Distinct cid as aCID, Name, Status, Rank from tbl ]. AS a
WHERE [a].Status = yes and [a].Rank = "x"
GROUP BY [a].aCID
ORDER BY [a].aCID;

nCount = Dlookup("[xCID]","QRY1","xcid = '" & vrCIDval & "'")

nCount equates properly but a move to next record (next CID) generates a VBA runtime Error stating that the previous process is still open???
Is Dlookup leaving objects open?

QRY2

PARAMETERS pcid Text ( 255 );
SELECT Count(tbl.CID) AS Cnt
FROM tbl


WHERE tbl.Status=Yes and cid = pcid and Rank = "x";

using query.execute ... this states execute doesn't support SELECT statements

other then iterating via an ADO and counting the records the long way how do I get the value of Count from either of these Queries in VBA?


Tbl ... ID CID Name Rank Description
01 123 ABC x blah
02 123 ABS y blah
03 124 ADB x blah
04 124 ADX x blah

so expecting CID 123 to return 1 and CID 124 to return 2 ... the Queries work but haven't been able to get the values in VBA from the queries

[orange]

There is no field "status" in your tbl example.
What datatype is CID?
Show us the full design of tbl, and a sample set of records.

It might be better to tell readers what you are trying to do in plain English.

PARAMETERS pcid Long;
SELECT Count(CID) AS Expr1
FROM tbl
WHERE (((tbl.mStatus)="Yes") AND ((tbl.[cid])=[pcid]) AND ((tbl.[mRank])="x"));


Execute method only supports Action query (update, append, delete)

What exactly do you mean

the Queries work but haven't been able to get the values in VBA from the queries

[bxdobs]

The two Queries when run manually, return the correct value ... when I tried to call these Queries from within VBA they produced errors ... my final resolution involved moving the code to a module function and moving the SQL from the Query in to Code which now works as desired

The table test data was given in the first thread ... all fields with the exception of ID (Auto Num) and Status (Boolean) are defined as String ... by default all records are never deleted so the Status field is a common field used to turn off records (so sorry didn't include this as part of the table data as all tables have a Status field)

Function vGetCount() As Variant
Dim dbo As DAO.Database
Dim qso As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT [a].[CID], Count(*) AS CNT " _
& "FROM [Select Distinct CID, Status, Rank from tbl ]. AS a " _
& "WHERE [a].[Status] And [a].[Rank] = 'x' and [a].[CID] = '" & gsCID & "' " _
& "GROUP BY [a].[CID] " _
& "ORDER BY [a].[CID] ;"

Set dbo = CurrentDb
Set qso = dbo.OpenRecordset(sSQL)

If qso.RecordCount > 0 Then
qso.MoveFirst

vGetCount = qso![CNT]
Else
vGetCount = 0
End If

Set qso = Nothing
Set dbo = Nothing

End Function

[June7]

SQL should fail in both query object and code.

Nest subqueries with (), not [].

& "FROM (Select Distinct CID, Status, Rank from tbl) AS a " _

If CID is a number type, remove the apostrophe delimiters.

[bxdobs]

Thanks ... ID and CID are different fields ... ID is auto number ... CID is String ... CID is a string because it requires leading zeros ... as to [] vs (), the [] were already in the Query Builder Query ... not sure if they were copied from an online snippet or if MS automatically threw them in ... I just cut and pasted the SQL content from the Query in to the sSQL string unchanged.

The function as provided, IS working in VBA which is what I needed ... as to why the 2 variations of the same query don't work from VBA, at this point, as I have wasted enough time finding a solution, and having a solution in hand, I am now just resigned to carry on and not try to find a fix for the VBA run-time errors encountered.

[June7]

True, Execute does not support SELECT statement. Execute is for action statements (UPDATE, INSERT, DELETE, MAKE TABLE). SELECT statements in VBA are only useful to set recordset objects.

I really don't see how [] nesting SELECT can work. I just tested and it definitely fails with error "Cannot find input table or query". Brackets [] define object names.

[bxdobs]

Thanks but ...

I really don't see how [] nesting SELECT can work. I just tested and it definitely fails with error "Cannot find input table or query". Brackets [] define object names.

I have both Access 2003 SP3 and Access 2016 ... the Query Process will NOT save on either version using FROM ( Select blah ) ... MS ACCESS SQL/QUERY is expecting [].

Opening the Query in SQL view, modifying the [] braces to () and attempting to save produces "Syntax error in FROM clause"



As stated, all I did was copy the SQL content to a String and used it in a recordset which works fine in both 2003 and 2016 which are the licenses we are currently running

These [] Braces were/are not the issue with my VBA code ... as stated, the Queries work if I manually run them ... and now also work if I use the raw SQL statement within recordset ... what I was having issues with was that VBA was giving me run-time errors when I attempted to use the QUERIES directly in VBA ... the execute doesn't work which has been clarified, but, there was an expectation to be able to use a query without having to embed the SQL statements in code ... that hasn't been resolved and has also previously been stated, being the recordset works, I am moving on, just won't ever attempt to use Select Queries again in Code.

Thanx

[June7]

I am glad it works for you. This is still very confusing how the [] syntax can work and () fail. Never seen that.

[ssanfu]

I created an example of how to use VBA to get the counts.

I tried to save them in A2000 format - one as MDB format and one as ACCDB format.

[bxdobs]

Thank-you for your effort ... appreciate your examples

Interesting ... the only thing that might be different here is I am storing all files as mdb's in 2003 format (32b) regardless of the Access version I am running ... opening your accdb file and replacing the () brackets with [] around the FROM Select confirms all comments above ... but doesn't match the results I get with the 2003 mdb where using () cause a syntax error. This may either be some syntax MS changed between versions OR perhaps is some 97 or 2000 (XP) remnant that came along for the ride (these mdb files were originally created in office 97 and have been converted twice; first to 2000, then to 2003 mdb)

Your first example referencing the Query from VBA, is the one that I originally struggled with both in the format you used as well as attempting to use a dlookup ... dlookup returned the count for the first instance, but it presented a runtime error stating a previous object relating to the dlookup was still open ... dlookup is being used throughout these mdb files with no issues ... dlookup should just open, seek, read and close a Qry|Tbl, so this run-time error was both unexpected and undetermined ... commenting out the dlookup line of code made the run-time error disappear.

Basically have a parent table that contains a unique list of CID's which is open in a subForm of a main form ... scrolling up or down in the subform was supposed to list the count in the Parent Form triggered by a form_current event which calls the parent vGetCount function/Sub ... this trigger event has been working forever updating other metrics on the Parent Form ... this issue only started when adding this new Count routine ... as stated above, I was able to get the SQL string version of code to work (your second example), albeit using the [] braces instead of the () around the FROM Select.

When I get a moment, I will take your example and rebuild it from scratch in 2003 and see if it has the same results or if it matches the existing db's results ... sometimes its easier to separate the trees from the forest

Also interesting, while your MDB file opens in Access 2016 with no problem, Access 2003 won't open the file stating the file format is unrecognizable (are you using 64b?)

There a few more things to note:

1) Access 2016 was installed in 32b mode which was the only way it would properly run MDB files created with 97 | 2000 | 2003

2) my development machine has both licenses installed (and they don't like to be installed together, don't play at all together, or like to be open concurrently)

3) anytime I open an MDB file on my machine I need to be aware of which Access version was last open or the file could be opened in the wrong version so I tend to use the open with option to open any file

4) anytime I open 2016 after 2003 or 2003 after 2016, the appropriate office installer automatically kicks in to modify background/supporting metrics

[isladogs]

I haven't followed this thread in detail but picked up on the last points you mentioned:

There a few more things to note:

1) Access 2016 was installed in 32b mode which was the only way it would properly run MDB files created with 97 | 2000 | 2003

2) my development machine has both licenses installed (and they don't like to be installed together, don't play at all together, or like to be open concurrently)

3) anytime I open an MDB file on my machine I need to be aware of which Access version was last open or the file could be opened in the wrong version so I tend to use the open with option to open any file

4) anytime I open 2016 after 2003 or 2003 after 2016, the appropriate office installer automatically kicks in to modify background/supporting metrics

Installing two versions of Access on the same machine is difficult as you've described
I have 2010 & 2016 on the same machine but its best not to open the same database in both versions as it can badly mess up version specific VBA references
I would recommend you create a virtual machine & move one of the MS Office versions to that - this will solve all configuration issues

[ssanfu]

Also interesting, while your MDB file opens in Access 2016 with no problem, Access 2003 won't open the file stating the file format is unrecognizable (are you using 64b?)

I tried to use A2010 to create a A2000 file. didn't work so well. Sorry....


I don't think I stated it outside of the dB, but "Name" and "Description" are reserved words in Access and shouldn't be used as object names.
This is why I used "MyName" and "MyDescription" as field names.


When I created "qry1", I removed all brackets/braces after I pasted in the SQL from your Post #1. (BTW....Brackets = []..... Braces = {} )

----------------------------------------------------------------------------------------------------------

I almost never use the Dlookup function. I can only remember 2 times in the last 5 years....

Take a look at this example....... (A2010 format)
Unfortunately, if you change the values in the form header, you have to click the button to have the count values update.
Have to run right now, but as an example of what might be done, it might suffice......

[bxdobs]

Braces vs Brackets ... Potaato vs Potahto ... interesting ... please excuse my misuse of the Term(s) ... Bits n Bytes have pretty much been my first language for 30+ years, making most say, English must be my second language ... having written code in many comp languages from assembler to enterprise systems since the late 70s may make my use of TERMS seem assuredly either dated or incorrect ... doesn't the B in BEDMAS mean Braces? ... regardless, in my defense, I have always understood the term "Braces" to refer to; {} Curly Braces, () Round Braces, or [] Square Braces ... MS Access's VBA Syntax has ALWAYS been troubling with respect to the when and or what type of Brace to use ... I have yet to find a concise (easy to follow) description for Braces/Brackets/Other which also includes when to use . vs ! ... I tend to either copy online examples or use a hit and miss approach which can be really frustrating. Even though I have written VBA code (in Excel and Access) for over 15 years, this coding has conservatively amounted to less then 5% of my workload ... so ... you can well imagine I truly appreciate these Forums

[bxdobs]

Thank-you everyone for all the feedback ... I took some time to create a test from scratch in Access 2003 Professional and the FROM [ Select syntax is coming directly from the Query builder ... entering in to Design Mode SQL View and entering the above mentioned Query, MS enforces the [] syntax in the Query after the query is saved ... I have included my test file below

Note: with deploying Win10Pro (64b)/Access 2016 (32b), we did run in to some issues with older 2000 or 2003 32b MDB files, mostly relating to missing objects, this test file might not run in new versions of ACCESS unless the above mentioned SQL statement FROM [ Select is changed to FROM ( SELECT and or the file is converted to the Version of Access being used

db1.zip

The file includes the frame work being used with the counting query

There is a minor anomaly with tabctl in that I haven't found a safe/proper way to call Application.RunCommand acCmdSelectRecord ' selects the entire row