Strange Error Message in A2K

[Jim Franklin]

Hi,

I have a function Today_ATC which calls upon a value returned from qry_ATC2.
(qry_ATC2 calculates an average of grouped values from qry_ATC1 - I know
this sounds long-winded but I could not see another way.)

Function is:
Dim RST As ADODB.Recordset
Set RST = New ADODB.Recordset
RST.Open "qry_ATC2", CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic ***
' populate recordset
RST.MoveFirst
Today_ATC = RST!Avgofduration
RST.Close


I have a control on a form which uses this function as its countrolsource.
The form recalculates the control at periods using the timer event.

For a while it works fine but I eventually get a message box as follows:
Run Time Error '-2147217900 (80040e14)':

Invalid SQL Statement: expected 'DELETE', 'INSERT', 'PROCEDURE','SELECT' or
'UPDATE'

If I select DEBUG, it takes me to the line shown above (***). In addition, I
have to close the Access file and re-open, otherwise the error appears
immediately I open the form again.

Any ideas anyone? Please!!

Thanks for any help,

Jim F.

 

[Bruce Loving]

make sure you SET RST = Nothing
after the RST.Close

any chance the results of ATC1 could return no rows?

 

[Jim Franklin]

Hi,

Thanks Bruce, I will try this. I haven't done this, so what does setting RST
= Nothing do?

qry_ATC1 does occasionally return no records - this is fine as the function
returns a null value, which is not a problem.

Jim