Querydef.Delete returns Error 91

[Biz Enhancer]

Hi All,

I have an issue with the deletion of a query.

The following code breaks at ** and returns Error 91 Object variable or With
block variable not set

Private Sub Form_Timer()
On Error GoTo Errtrp
Dim DBd As DAO.Database
Dim qdf As DAO.QueryDef
Dim Erm As String
Erm = "F329/Tim"

If DLookup("Round", "defaults") = -1 Then
strSQL = "SELECT rout1.ClientID, rout1.RentalID, rout1.AppID,
round(IIf(rout1.Revenuecycle=1,(([Rent]/30)*7),IIf(rout1.Revenuecycle=2,(([Rent]/30)*14),[Rent])))
AS RentA, rout1.Revenuecycle FROM (rout1 INNER JOIN Runit4 ON rout1.RentalID
= Runit4.RentalID) LEFT JOIN rout2 ON Runit4.RentalID = rout2.RentalID WHERE
(((rout2.RentalID) Is Null));"

DBd.QueryDefs.Delete "revcycl1" '**
Set qdf = DBd.CreateQueryDef("revcycl1", strSQL)
End if
Exittrp:
Exit Sub

Errtrp:
Call Ertbl(Err.Number, Err.Description, Erm, Me.Name, True)

GoTo Exittrp
End Sub

This has me completely baffled as the SQL select query returns good data,
the query "revcycl1" exists and has data in it prior to deletion, however
when the code tries to execute the deletion, the error occurs.
I have a similar routine that runs fine and deletes the query "revcycl1" on
cue without problem.

Most appreciative of any help.

Regards,
Nick.

 

[Dirk Goldgar]

Hi All,

I have an issue with the deletion of a query.

The following code breaks at ** and returns Error 91 Object variable
or With block variable not set

Private Sub Form_Timer()
On Error GoTo Errtrp
Dim DBd As DAO.Database
Dim qdf As DAO.QueryDef
Dim Erm As String
Erm = "F329/Tim"

If DLookup("Round", "defaults") = -1 Then
strSQL = "SELECT rout1.ClientID, rout1.RentalID,
rout1.AppID,
round(IIf(rout1.Revenuecycle=1,(([Rent]/30)*7),IIf(rout1.Revenuecycle=2,
(([Rent]/30)*14),[Rent])))
AS RentA, rout1.Revenuecycle FROM (rout1 INNER JOIN Runit4 ON
rout1.RentalID = Runit4.RentalID) LEFT JOIN rout2 ON Runit4.RentalID
= rout2.RentalID WHERE (((rout2.RentalID) Is Null));"

DBd.QueryDefs.Delete "revcycl1" '**
Set qdf = DBd.CreateQueryDef("revcycl1", strSQL)
End if
Exittrp:
Exit Sub

Errtrp:
Call Ertbl(Err.Number, Err.Description, Erm, Me.Name, True)

GoTo Exittrp
End Sub

This has me completely baffled as the SQL select query returns good
data, the query "revcycl1" exists and has data in it prior to
deletion, however when the code tries to execute the deletion, the
error occurs.
I have a similar routine that runs fine and deletes the query
"revcycl1" on cue without problem.

Most appreciative of any help.

Regards,
Nick.

I don't see any line that sets DBd to a database object; e.g.,

Set DBd = CurrentDb

 

[Dirk Goldgar]

DBd.QueryDefs.Delete "revcycl1" '**
Set qdf = DBd.CreateQueryDef("revcycl1", strSQL)

BTW, I don't see any reason to delete the querydef and recreate it. Why
not just set its SQL:

DBd.QueryDefs("revcycle1").SQL = strSQL

? Or is it that you aren't sure whether the querydef already exists or
not? That would be something you could check easily enough.

 

[Biz Enhancer]

Yep that'll do it. Completely missed that one.
Thanks Dirk

Regards,
Nick

Hi All,

I have an issue with the deletion of a query.

The following code breaks at ** and returns Error 91 Object variable
or With block variable not set

Private Sub Form_Timer()
On Error GoTo Errtrp
Dim DBd As DAO.Database
Dim qdf As DAO.QueryDef
Dim Erm As String
Erm = "F329/Tim"

If DLookup("Round", "defaults") = -1 Then
strSQL = "SELECT rout1.ClientID, rout1.RentalID,
rout1.AppID,
round(IIf(rout1.Revenuecycle=1,(([Rent]/30)*7),IIf(rout1.Revenuecycle=2,
(([Rent]/30)*14),[Rent])))
AS RentA, rout1.Revenuecycle FROM (rout1 INNER JOIN Runit4 ON
rout1.RentalID = Runit4.RentalID) LEFT JOIN rout2 ON Runit4.RentalID
= rout2.RentalID WHERE (((rout2.RentalID) Is Null));"

DBd.QueryDefs.Delete "revcycl1" '**
Set qdf = DBd.CreateQueryDef("revcycl1", strSQL)
End if
Exittrp:
Exit Sub

Errtrp:
Call Ertbl(Err.Number, Err.Description, Erm, Me.Name, True)

GoTo Exittrp
End Sub

This has me completely baffled as the SQL select query returns good
data, the query "revcycl1" exists and has data in it prior to
deletion, however when the code tries to execute the deletion, the
error occurs.
I have a similar routine that runs fine and deletes the query
"revcycl1" on cue without problem.

Most appreciative of any help.

Regards,
Nick.

I don't see any line that sets DBd to a database object; e.g.,

Set DBd = CurrentDb


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)