[AccessD] OpenRecordset Error

I can't see it either but what I do in cases like this is put strSQL into a
text box on my form, then copy the contest of the text box to the SQL view
of a new query and go to design view.  If it makes it that far, I run it.
Lots of times it will tell me what the real error is.

The error 91 would seem to be pointing to db as not being set.  Is the
reference to DAO set correctly?

The next thing I'd do is Dim db2 as dao.Databaser and set db2 = CurrentDb.
Then use db2 to set rst2 and see if it makes a difference.

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Friday, December 09, 2011 2:07 AM
To: access Developers discussion and problem solving
Subject: [AccessD] OpenRecordset Error

It is getting late.  Can anyone see why I am getting error 91 Object
variable or With block variable not set) when the "Set rst2" line is run?  I
have checked the value of strSQL and it looks ok.  I have put the sql into a
query and it returns the expected result.  Could it be because OpenRecordset
doesn't like group by queries?

     Dim db As DAO.Database, rst2 As DAO.Recordset, strSQL As String

     Set db = CurrentDb()

     strSQL = "SELECT Sum(UnitAmt) AS TotalAmt,
tblTenantInvoiceMeter.TenantInvoiceIDNo "
     strSQL = strSQL & "FROM tblTenantInvoiceMeter INNER JOIN
tblTenantInvoiceMeterTran ON tblTenantInvoiceMeter.TenantInvoiceMeterID =
tblTenantInvoiceMeterTran.TenantInvoiceMeterIDNo "
     strSQL = strSQL & "GROUP BY
tblTenantInvoiceMeter.TenantInvoiceIDNo HAVING
tblTenantInvoiceMeter.TenantInvoiceIDNo = 9"
     Set rst2 = db.OpenRecordset(strSQL)

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com