OpenQueryDef Causing Errors

  • Thread starter Thread starter james.eacret
  • Start date Start date
J

james.eacret

Greetings all,

I am using Office 2003 and Windows XP.

I am having various troubles with VBA after upgrading from 97 to 2000.
Recently this forum has proven very helpful. Forgive me, but I must
rely on people's expertise one more time.

The problem is when a report is run it was erroring out. It failed to
recognize OpenQueryDef. I changed this to what Access 2000 recognizes
(QueryDef), but it seems to have caused more errors. Below is the
original code (97) and what I have altered to get it to run...without
success yet.

The 97 Version:

Code:
Private Function HistAvg()

Dim ThisDB As Database, qdfHistAvg As QueryDef, recHistAvg As Recordset
Dim CodeVar As String, datevar As Variant, WellVar As String, strSQL As
String

Set ThisDB = CurrentDb()

On Error GoTo HistAvgEhandler

'Assign the variables from the current controls on the report

datevar = Reports![rptAnnual].[EndDate]
CodeVar = Reports![rptAnnual].[ParamCode]
WellVar = Reports![rptAnnual].[WellNo]

'Assign the query definition - the query definition is
created/destroyed by code in Report OnOpen/OnClose methods

Set qdfHistAvg = ThisDB.OpenQueryDef("ztqselAnnualRep")

strSQL = "SELECT DISTINCTROW
Avg(TrueValue([Anavalue],[ProjectAnavalue])) AS Anaval "
strSQL = strSQL & "FROM [tblAnavalue] "
strSQL = strSQL & "WHERE ((tblAnavalue.WDNRWellNo = '" & WellVar & "')
AND (tblAnavalue.DateCollected<=#" & datevar & "#) AND
(tblAnavalue.WDNRParameterCode ='" & CodeVar & "') AND
((TrueQual([DNRQualifier],[ProjectQualifier]))='=' Or
(TrueQual([DNRQualifier],[ProjectQualifier]))='J'));"

qdfHistAvg.SQL = strSQL

Set recHistAvg = ThisDB.OpenRecordset("ztqselAnnualRep")

HistAvg = recHistAvg.Anaval

'Close the query definition
qdfHistAvg.Close

Exit Function

HistAvgEhandler:

If Err = 3011 Then

Set qdfHistAvg = ThisDB.CreateQueryDef("ztqselAnnualRep")
Resume Next

Else

MsgBox Error & " Error No. " & Err
Exit Function

End If

End Function
I have altered it to this:

Code:
Private Function HistAvg()

Dim ThisDB As Database, qdfHistAvg As DAO.QueryDef, recHistAvg As
DAO.Recordset
Dim CodeVar As String, datevar As Variant, WellVar As String, strSQL As
String

Set ThisDB = CurrentDb

On Error GoTo HistAvgEhandler

'Assign the variables from the current controls on the report

datevar = Reports![rptAnnual].[EndDate]
CodeVar = Reports![rptAnnual].[ParamCode]
WellVar = Reports![rptAnnual].[WellNo]

'Assign the query definition - the query definition is
created/destroyed by code in Report OnOpen/OnClose methods

Set qdfHistAvg = ThisDB.QueryDefs("ztqselAnnualRep")

strSQL = "SELECT DISTINCTROW
Avg(TrueValue([Anavalue],[ProjectAnavalue])) AS Anaval "
strSQL = strSQL & "FROM [tblAnavalue] "
strSQL = strSQL & "WHERE ((tblAnavalue.WDNRWellNo = '" & WellVar & "')
AND (tblAnavalue.DateCollected<=#" & datevar & "#) AND
(tblAnavalue.WDNRParameterCode ='" & CodeVar & "') AND
((TrueQual([DNRQualifier],[ProjectQualifier]))='=' Or
(TrueQual([DNRQualifier],[ProjectQualifier]))='J'));"

qdfHistAvg.SQL = strSQL

Set recHistAvg = ThisDB.OpenRecordset("ztqselAnnualRep")

HistAvg = recHistAvg.Anaval

'Close the query definition
qdfHistAvg.Close

Exit Function
The current problem is on the line

Code:
HistAvg = recHistAvg.AnavalStating "Method or Data not found"

Having little experience updating code, I was hoping for some help, or
a point in the right direction. Thanks!
 
Code:
HistAvg = recHistAvg.AnavalStating "Method or Data not found"
Click to expand...
Shoud read:
Code:
HistAvg = recHistAvg.Anaval
Error:
"Method or Data not found"


When I change this to
HistAvg = [recHistAvg]![Anaval]

The Error is: Item not found in this collection. Error No. 3265

So either this is not the correct syntax, or there is a problem earlier
on and HistAvg is not being populated.
 
Since Access 2000 ADO has been used as the default data access technology
rather than DAO. There is no reason why the latter can not still be used,
however, or indeed for both to be used in one application. To avoid
confusion, though, its now prudent, and often essential, to always
specifically reference the relevant object library when declaring object
variables, so with DAO the declarations in you case would be:

Dim ThisDB As DAO.Database, qdfHistAvg As DAO.QueryDef, recHistAvg As
DAO.Recordset

You should make sure that a reference to the DAO object library exists. Do
this from the Tools|References menu on the VBA menu bar. Make sure that the
reference to the Microsoft DAO Object Library is checked.

To assign a value to the SQL property of a querydef object you do not have
to call the OpenQuerydef method, just refer to the member of the querydefs
collection:

Set qdfHistAvg = ThisDB.Querydefs("ztqselAnnualRep")
strSQL = "SELECT etc"
qdfHistAvg.SQL = strSQL

Ken Sheridan
Stafford, England

[email protected] said:
Greetings all,

I am using Office 2003 and Windows XP.

I am having various troubles with VBA after upgrading from 97 to 2000.
Recently this forum has proven very helpful. Forgive me, but I must
rely on people's expertise one more time.

The problem is when a report is run it was erroring out. It failed to
recognize OpenQueryDef. I changed this to what Access 2000 recognizes
(QueryDef), but it seems to have caused more errors. Below is the
original code (97) and what I have altered to get it to run...without
success yet.

The 97 Version:

Code:
Private Function HistAvg()

Dim ThisDB As Database, qdfHistAvg As QueryDef, recHistAvg As Recordset
Dim CodeVar As String, datevar As Variant, WellVar As String, strSQL As
String

Set ThisDB = CurrentDb()

On Error GoTo HistAvgEhandler

'Assign the variables from the current controls on the report

datevar = Reports![rptAnnual].[EndDate]
CodeVar = Reports![rptAnnual].[ParamCode]
WellVar = Reports![rptAnnual].[WellNo]

'Assign the query definition - the query definition is
created/destroyed by code in Report OnOpen/OnClose methods

Set qdfHistAvg = ThisDB.OpenQueryDef("ztqselAnnualRep")

strSQL = "SELECT DISTINCTROW
Avg(TrueValue([Anavalue],[ProjectAnavalue])) AS Anaval "
strSQL = strSQL & "FROM [tblAnavalue] "
strSQL = strSQL & "WHERE ((tblAnavalue.WDNRWellNo = '" & WellVar & "')
AND (tblAnavalue.DateCollected<=#" & datevar & "#) AND
(tblAnavalue.WDNRParameterCode ='" & CodeVar & "') AND
((TrueQual([DNRQualifier],[ProjectQualifier]))='=' Or
(TrueQual([DNRQualifier],[ProjectQualifier]))='J'));"

qdfHistAvg.SQL = strSQL

Set recHistAvg = ThisDB.OpenRecordset("ztqselAnnualRep")

HistAvg = recHistAvg.Anaval

'Close the query definition
qdfHistAvg.Close

Exit Function

HistAvgEhandler:

If Err = 3011 Then

Set qdfHistAvg = ThisDB.CreateQueryDef("ztqselAnnualRep")
Resume Next

Else

MsgBox Error & " Error No. " & Err
Exit Function

End If

End Function
I have altered it to this:

Code:
Private Function HistAvg()

Dim ThisDB As Database, qdfHistAvg As DAO.QueryDef, recHistAvg As
DAO.Recordset
Dim CodeVar As String, datevar As Variant, WellVar As String, strSQL As
String

Set ThisDB = CurrentDb

On Error GoTo HistAvgEhandler

'Assign the variables from the current controls on the report

datevar = Reports![rptAnnual].[EndDate]
CodeVar = Reports![rptAnnual].[ParamCode]
WellVar = Reports![rptAnnual].[WellNo]

'Assign the query definition - the query definition is
created/destroyed by code in Report OnOpen/OnClose methods

Set qdfHistAvg = ThisDB.QueryDefs("ztqselAnnualRep")

strSQL = "SELECT DISTINCTROW
Avg(TrueValue([Anavalue],[ProjectAnavalue])) AS Anaval "
strSQL = strSQL & "FROM [tblAnavalue] "
strSQL = strSQL & "WHERE ((tblAnavalue.WDNRWellNo = '" & WellVar & "')
AND (tblAnavalue.DateCollected<=#" & datevar & "#) AND
(tblAnavalue.WDNRParameterCode ='" & CodeVar & "') AND
((TrueQual([DNRQualifier],[ProjectQualifier]))='=' Or
(TrueQual([DNRQualifier],[ProjectQualifier]))='J'));"

qdfHistAvg.SQL = strSQL

Set recHistAvg = ThisDB.OpenRecordset("ztqselAnnualRep")

HistAvg = recHistAvg.Anaval

'Close the query definition
qdfHistAvg.Close

Exit Function
The current problem is on the line

Code:
HistAvg = recHistAvg.AnavalStating "Method or Data not found"

Having little experience updating code, I was hoping for some help, or
a point in the right direction. Thanks!
Click to expand...
 
It definitely should be HistAvg = recHistAvg!Anaval, nor recHistAvg.Anaval.
The error you're usually implies a typo, but assuming you copied-and-pasted,
I don't see the typo.

See whether changing

Set recHistAvg = ThisDB.OpenRecordset("ztqselAnnualRep")

to

Set recHistAvg = qdfHistAvg.OpenRecordset()

makes any differences (I'm thinking perhaps the SQL hasn't actually been
saved before you open the recordset)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Code:
HistAvg = recHistAvg.AnavalStating "Method or Data not found"
Click to expand...
Shoud read:
Code:
HistAvg = recHistAvg.Anaval
Error:
"Method or Data not found"


When I change this to
HistAvg = [recHistAvg]![Anaval]

The Error is: Item not found in this collection. Error No. 3265

So either this is not the correct syntax, or there is a problem earlier
on and HistAvg is not being populated.
Click to expand...
 
Dim ThisDB As DAO.Database, qdfHistAvg As DAO.QueryDef, recHistAvg As
DAO.Recordset
Click to expand...
Ok, Changed to this.
You should make sure that a reference to the DAO object library exists. Do
this from the Tools|References menu on the VBA menu bar. Make sure that the
reference to the Microsoft DAO Object Library is checked.
Click to expand...
Microsoft DAO Object Library 3.6 is checked.

I added a breakpoint to the code to see exactly where the problem is
occurring.
It seems that it is going straight to HistAvgEhandler. Does this mean
that for some reason ThisDB is not being set to CurrentDb?

Set ThisDB = CurrentDb

On Error GoTo HistAvgEhandler <--- Breakpoint
 
It can't be the Set ThisDB = CurrentDb line which is raising the error as
this is before the On Error Statement so you'd get a system generated error
message if the error was on that line. You should set the breakpoint early
in the code and step into it line by line, not on the error handler as,
wherever, the error occurs the code will break on the error handler. What
you want is to see at which line it branches to the error handler.

Ken Sheridan
Stafford, England
 
Back
Top