Invalid SQL statement; sql statements made in query builder

[iheartvba]

Hi
I am getting the following error when i click cmdFFR (See note1): Invalid SQL statement, expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'. I can't figure out why this is. Spelling all seems to be correct.

Public Dimensions are as follow:

Option Compare Database
Option Explicit 
Public cnn As ADODB.Connection
Public rst As New ADODB.Recordset

(Note1)

Private Sub cmdFFR_Click()
Dim intFFR As Byte
Set cnn = CurrentProject.Connection
rst.Open "qryCountYearsPerReceipt", cnn, adOpenKeyset, adLockOptimistic
  With rst
      intAddFFRYears = !CountOfYear
   End With
End Sub

qryCountYearsPe rReceipt (from Private Sub cmdFFR_Click) above is made via Query Builder the code is as follows:

SELECT qryGroupByYearsPerReceipt.ReceiptID, Count(qryGroupByYearsPerReceipt.Year) AS CountOfYear
FROM qryGroupByYearsPerReceipt
GROUP BY qryGroupByYearsPerReceipt.ReceiptID
HAVING (((qryGroupByYearsPerReceipt.ReceiptID)=[Forms]![frmFFR]![cboRcptID]));

qryGroupByYears PerReceipt (which is a query from which qryCountYearsPe rReceipt is made) is also made by the query builder and code is as follows :

SELECT qryGroupByYearsPerReceipt.ReceiptID, Count(qryGroupByYearsPerReceipt.Year) AS CountOfYear
FROM qryGroupByYearsPerReceipt
GROUP BY qryGroupByYearsPerReceipt.ReceiptID
HAVING (((qryGroupByYearsPerReceipt.ReceiptID)=[Forms]![frmFFR]![cboRcptID]));

[NeoPa]

I looked up in the help for (ADODB.Recordse t).Open and it gave the first argument as a Command. It doesn't appear that a QueryDef name (as you've used) is a valid entry for this.

Syntax
recordset.Open Source, ActiveConnectio n, CursorType, LockType, Options
Parameters
Source
Optional. A Variant that evaluates to a valid Command object, an SQL statement, a table name, a stored procedure call, a URL, or the name of a file or Stream object containing a persistently stored Recordset.

[iheartvba]

Oops, Thanks for that I have to admit I did not look up that help file, what reference do you usually look at for your help files.

[NeoPa]

I don't think I understand your question. Reference?

[Stewart Ross]

Hi Neo. I had a look at this post when it first appeared and did some testing of the code. I found that it was valid to provide the name of a stored query in the call, and when I substituted one of my own for iheartvba's query I could access the recordset fields just as if I was opening any other recordset.

As the queries involved otherwise seem fine, I wondered if this one is a case of the known Access bug when referring to form fields within queries? JET can fail to recognise such comparisons to form control values.

This could be ruled in or out by removing the form field references, storing the resultant queries under different names, and running the VBA code again on the queries without the form field references.

-Stewart

I looked up in the help for (ADODB.Recordse t).Open and it gave the first argument as a Command. It doesn't appear that a QueryDef name (as you've used) is a valid entry for this.

[NeoPa]

Well spotted Stewart. Sounds like an idea :)