Object Variable or with block variable not set

[Vec]

The code below gives me an error:
"Object Variable or with block variable not set"

and highlights:
Set rsTemp = Data1.Database.OpenRecordset("SELECT SUM(ADD6) AS TOTALPRICE FROM LOG WHERE ORDERNUM > 0", dbOpenDynamic)

Here is the code that causes the error:

Data1.DatabaseName = "data.mdb"
Dim sql As String
Dim dbfields As String
dbfields = "Add6"

sql = ("SELECT " & dbfields & " FROM log WHERE OrderNum > 0&quot

Data1.RecordSource = sql

Dim rsTemp As Recordset
Set rsTemp = Data1.Database.OpenRecordset("SELECT SUM(ADD6) AS TOTALPRICE FROM LOG WHERE ORDERNUM > 0", dbOpenDynamic)

lbl1.Caption = "$" + CStr(rsTemp!totalprice)

rsTemp.Close
Set rsTemp = Nothing


Am I missing a reference or something? -

Looking Toward The Future

http://www.webfusiondesign.com

 

[Foada]

I am not sure, but it appears that you are trying to use DAO. You will need to add a reference to the DAO library. Anything is possible, the problem is I only have one lifetime.

 

[mmilan]

Vec - this is third forum I've found about this problem - the main forum being "How to add up these numbers" - or something similar...

Just a suggestion, but try to leave ONE problem in ONE forum... The main advantage of doing this is that respondents can view all comments that have already been made, and will therefore be that much closer to giving you an answer that works...

And foada, I supplied the dodgy line of code... I was trying to get at the DAO Database object behind the DAO control, and connect a recordset through that... It's not something I had ever attempted to do before, and likely not something I will try again as I have given up on controls, but should that approach work?

mmilan

 

[Foada]

mmilan,

I don't know which thread to post to!! I did not check into your code a whole lot nor am I a DOA guru, I just noticed that it looked like DOA and suggested making the reference. I try to stick to using ADO which I would post some code if I knew which thread to look at? Anything is possible, the problem is I only have one lifetime.

 

[AdaHacker]

Your problem is that you're supplying the DatabaseName and RecordSource at run-time. According to the documentation, the data control is initialized before the form it is on is loaded. When you supply the initialization at run-time, the data control is initialized with nothing, so you need to refresh it before it can be used. That's why you were getting the "object not set" error: the data control was initialized with no data, so the database property was set to nothing. If you just do the following, it should work:

Dim rsTemp As Recordset
Data1.Refresh '<<<New code
Set rsTemp = Data1.Database.OpenRecordset(&quot;SELECT SUM(ADD6) AS TOTALPRICE FROM LOG WHERE ORDERNUM > 0&quot;, dbOpenDynamic)