DAO peculiarity in A97?

[MLH]

I have noticed, in the following code snippet, that if
tblCorrespondence has no records in it, I get an error
91 later during processing. It complains that an object
variable or a With block variable has not been set. I
have checked the code carefully and I don't think this
is the case. I'm wondering if my code in line 231 might
be flawed in light of the fact that qdfType17CorrespRecs
returns no records?

140 With MyDB
170 Set qdfType17CorrespRecs = .CreateQueryDef("", "Select *
FROM tblCorrespondence;")
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
..OpenRecordset(dbOpenSnapshot)
230 ' With rstType17CorrespRecs
231 ' If .BOF = True Then GoTo NoRecs
240 ' .MoveFirst
250 ' Do Until rstType17CorrespRecs.EOF

[Wayne Morgan]

If the recordset has no records, both BOF and EOF will be true at the same
time. So, in line 231, if there are no records then BOF should be true.
However, lines 230 through 250 are currently "commented out", so they aren't
executing.

You aren't showing the entire code. Make sure your End With statements line
up where you expect them to. If you were missing one, you should get a
compile error, but if it is misplaced, you won't get the compile error but
may get the error you're getting.

FYI, line 250 can be shortened to

Do Until .EOF

Which line gives the 91 error?

--
Wayne Morgan
MS Access MVP
"MLH" <CR**@NorthState.net> wrote in message
news:kb********************************@4ax.com...

I have noticed, in the following code snippet, that if
tblCorrespondence has no records in it, I get an error
91 later during processing. It complains that an object
variable or a With block variable has not been set. I
have checked the code carefully and I don't think this
is the case. I'm wondering if my code in line 231 might
be flawed in light of the fact that qdfType17CorrespRecs
returns no records?

140 With MyDB
170 Set qdfType17CorrespRecs = .CreateQueryDef("", "Select *
FROM tblCorrespondence;")
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
.OpenRecordset(dbOpenSnapshot)
230 ' With rstType17CorrespRecs
231 ' If .BOF = True Then GoTo NoRecs
240 ' .MoveFirst
250 ' Do Until rstType17CorrespRecs.EOF

[Allen Browne]

Does this work in place of line 231:
If .RecordCount = 0 Then
Go To NoRecs
End If

There's bunch of things different here than I would do. You don't need a
temp query created, just OpenRecordset on the query statement. And use
dbOpenDynaset (esp. if you plan to MoveFirst.)

Try:
Dim MyDB As DAO.Database
Dim rstType17CorrespRecs As DAO.Recordset
Dim strSql As String

strSql = "Select * FROM tblCorrespondence;"
Set rstType17CorrespRecs = MyDB.OpenRecordset(strSql)
If rstType17CorrespRecs.RecordCount > 0 Then
'do your stuff here.
End If
rstType17CorrespRecs.Close

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.net> wrote in message
news:kb********************************@4ax.com...

I have noticed, in the following code snippet, that if
tblCorrespondence has no records in it, I get an error
91 later during processing. It complains that an object
variable or a With block variable has not been set. I
have checked the code carefully and I don't think this
is the case. I'm wondering if my code in line 231 might
be flawed in light of the fact that qdfType17CorrespRecs
returns no records?

140 With MyDB
170 Set qdfType17CorrespRecs = .CreateQueryDef("", "Select *
FROM tblCorrespondence;")
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
.OpenRecordset(dbOpenSnapshot)
230 ' With rstType17CorrespRecs
231 ' If .BOF = True Then GoTo NoRecs
240 ' .MoveFirst
250 ' Do Until rstType17CorrespRecs.EOF

[jacksonmacd]

I usually use this construct for DAO loops, including A97

Set rs = db.Openrecordset (....)
do while not rs.eof
[...]
rs.movenext
loop

It handles the no-record condition appropriately, without using a
GoTo. Your code uses .BOF to check for no records, which may not be
correct.

On Sat, 31 Dec 2005 09:02:01 -0500, MLH <CR**@NorthState.net> wrote:

I have noticed, in the following code snippet, that if
tblCorrespondence has no records in it, I get an error
91 later during processing. It complains that an object
variable or a With block variable has not been set. I
have checked the code carefully and I don't think this
is the case. I'm wondering if my code in line 231 might
be flawed in light of the fact that qdfType17CorrespRecs
returns no records?

140 With MyDB
170 Set qdfType17CorrespRecs = .CreateQueryDef("", "Select *
FROM tblCorrespondence;")
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
.OpenRecordset(dbOpenSnapshot)
230 ' With rstType17CorrespRecs
231 ' If .BOF = True Then GoTo NoRecs
240 ' .MoveFirst
250 ' Do Until rstType17CorrespRecs.EOF

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

[MLH]

On Sat, 31 Dec 2005 14:53:14 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:

If the recordset has no records, both BOF and EOF will be true at the same
time. So, in line 231, if there are no records then BOF should be true.
However, lines 230 through 250 are currently "commented out", so they aren't
executing. Yes, I'd commented them out to determine the point at which the
erroneous condition arose. I had 5 nested With statements and
the darned error wasn't reporting until execution of line #
650 GoTo CheckITS10DayStandby_Exit
and it wasn't at all clear to me where the REAL problem arose.
You aren't showing the entire code. Make sure your End With statements line
up where you expect them to. If you were missing one, you should get a
compile error, but if it is misplaced, you won't get the compile error but
may get the error you're getting.

FYI, line 250 can be shortened to

Do Until .EOF

Which line gives the 91 error?

Line #650

Here's the entire procedure...

Sub CheckITS10DayStandby()
On Error GoTo CheckITS10DayStandby_Err
MySQL = "SELECT tblCorrespondence.CorrespID,
tblCorrespondence.VehicleJobID, tblCorrespondence.OutDate, "
MySQL = MySQL & "tblCorrespondence.OutType,
tblCorrespondence.OutProcessor, tblCorrespondence.InDate, "
MySQL = MySQL & "tblCorrespondence.InRefDate,
tblCorrespondence.InType, tblCorrespondence.InProcessor, "
MySQL = MySQL & "tblCorrespondence.ToWhom,
tblCorrespondence.CorrespTDStamp, tblCorrespondence.UserID, "
MySQL = MySQL & "tblCorrespondence.Tracked FROM tblCorrespondence
WHERE (((tblCorrespondence.OutDate) "
MySQL = MySQL & "Is Not Null) AND ((tblCorrespondence.OutType)='17')
AND ((tblCorrespondence."
MySQL = MySQL & "OutProcessor) Is Not Null) AND
((tblCorrespondence.InDate) Is Not Null) AND "
MySQL = MySQL & "((tblCorrespondence.InRefDate) Is Not Null) AND
((tblCorrespondence.InType) Is Not Null) "
MySQL = MySQL & "AND ((tblCorrespondence.InProcessor) Is Not Null) AND
((tblCorrespondence.Tracked)=True)) "
MySQL = MySQL & "AND ((tblVehicleJobs.Reclaimed)=False));"

100 Dim MyDB As Database, qdfType17CorrespRecs As QueryDef,
rstType17CorrespRecs As Recordset
110 Dim qdfRRs As QueryDef, rstRRs As Recordset, GottaWait As
Boolean, WaitTime As Byte
120 Set MyDB = CurrentDb ' Set MyDB =
OpenDatabase("Northwind.mdb") is alternate syntax.
121 WaitTime = DLookup("[CertMailResponseWaitTime]", "tblAdmin")
130
140 With MyDB
160 Set qdfType17CorrespRecs = .CreateQueryDef("", MySQL)
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
..OpenRecordset(dbOpenSnapshot)
230 With rstType17CorrespRecs
231 If .BOF = True Then GoTo NoRecs
240 .MoveFirst
250 Do Until rstType17CorrespRecs.EOF
260 CurrentVehicleJobID = !VehicleJobID
270 CurrentCorrespondenceRecordID = !CorrespID
280 GottaWait = False
330 Set qdfRRs = MyDB.CreateQueryDef("", "Select *
From tblReturnReceipts Where
[CorrespID]=GetCurrentCorrespondenceRecordID();")
340 With qdfRRs
350 Set rstRRs = .OpenRecordset(dbOpenSnapshot)
'This'll get about 3-5 records
360 With rstRRs
361 If .BOF = True Then GoTo NoRecs
370 .MoveFirst
380 Do Until rstRRs.EOF
390 If Int(Now()) - !DateSigned <
WaitTime Then GottaWait = True
400 .MoveNext
410 Loop
420 .Close
430 If GottaWait = False Then
440 'First, update the current corresp
rec...
450 Dim SQL2UpdateCorrespRec As String
460 SQL2UpdateCorrespRec = "UPDATE
tblCorrespondence SET tblCorrespondence.Tracked = False WHERE
tblCorrespondence."
462 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "CorrespID=GetCurrentCorrespondenceRecordID(); "
470 'DoCmd.SetWarnings False
480 DoCmd.RunSQL SQL2UpdateCorrespRec
490 DoCmd.SetWarnings True
500 'Next, append an OutType-18 corresp
rec
510 Dim SQL2AppendRec2CorrespTbl As
String
520 SQL2AppendRec2CorrespTbl = "INSERT
INTO tblCorrespondence (VehicleJobID, OutType, ToWhom, UserID) SELECT
"
522 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "GetCurrentVehicleJobID() AS VehicleJobID, '18'
AS OutType, "
524 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "'DMV' AS ToWhom, CurrentUser() AS UserID;"
530 'DoCmd.SetWarnings False
540 DoCmd.RunSQL
SQL2AppendRec2CorrespTbl
550 DoCmd.SetWarnings True
560 End If
570 End With
580 End With
590 .MoveNext
600 Loop
610 .Close
620 End With
630 End With
640 End With
650 GoTo CheckITS10DayStandby_Exit

NoRecs:
rstRRs.Close
rstType17CorrespRecs.Close

CheckITS10DayStandby_Exit:
Exit Sub

CheckITS10DayStandby_Err:
Dim r As String, Z As String, Message3 As String
r = "The following unexpected error occurred in Function
CheckITS10DayStandby(), line #" & CStr(Erl) & ", when called from
frmCron:"
Z = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & Z
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume CheckITS10DayStandby_Exit

End Sub

[Tim Marshall]

MLH wrote:

is the case. I'm wondering if my code in line 231 might
be flawed in light of the fact that qdfType17CorrespRecs
returns no records?

M, I note that you have line 231 commented out. The commenting out
would prevent the action in lieu of an empty recordset (in this case,
goto rec - see comments on this construct, later) from ocurring.

However, assuming you get this error with the comment removed...

Personally, whenever I open a recordset, I always check for .eof
immediately afterward and if true, close the recordset and go on. My
experience has been that I only ever bother with .bof when I'm working
backwards in a recordset.

However, from discussion I've seen on cdma a couple of times, I seem to
recall that some folks who know what they are talking about say they
always check for an empty recordset by checking .bof and .eof, but my
above convention has never failed me _for the stuff *I* do_.

But as I said, I think your code would work with the .BOF if it wasn't
commented out.

Two things.

First, If 231 is not commented out, I wonder if your later code does
something with the recordset anyway even if .bof is true where you test
that? It would be well worth your time to add a breakpoint just before
your line 91 and then step through your code that way to make sure.

Second, you're using goto. Long before I got into VBA (and, in fact,
before VBA ever was, or at least before VBA was commercially available)
it's always been a basic tennant of programming that using goto is bad
practice. I agree with this, though I use it in one exception, and
that's goto Exit_Proc, ie, instead of just putting in a exit
sub/function, I endeavour to have one and only one exit point from my
procedures. Though ideally, a well designed series of if/endifs and
loops is better. It also makes it easier to avoid the sort of problem
you've described if there is a definite progressive structure to your
logic rather than the sort of jumping out of things that use of GoTo
encourages.

Another thing you've done is having a line of code:

Do Until rstType17CorrespRecs.EOF

But you have that within a With rstType17CorrespRecs loop.

Here's what I would do with the code you've presented, removing the
comments, instead of the goto NoRecs label.

What you've done, nesting with/end with, is legal, but I prefer not to
do it myself as I find it can get confusing, but that's just my personal
preference - unlike my adage on avoidance of use of GoTo which is
universal good practice.

With MyDB

Set qdfType17CorrespRecs = .CreateQueryDef("", "Select * " & _
"FROM tblCorrespondence")

'TIM COMMENT - you don't need the semi-colon

With qdfType17CorrespRecs

' Open Recordset from QueryDef.

Set rstType17CorrespRecs = .OpenRecordset(dbOpenSnapshot)

With rstType17CorrespRecs

If .EOF = False Then

.MoveFirst

Do Until .EOF 'See my comment immediately preceeding!

<do stuff>

Loop

Else

'This is where you'd do the stuff with your NoRec Label

End if

End With ' for rstType17CorrespRecs

End With 'for qdfType17CorrespRecs

End With 'for MyDB

There'a another way I do things, especially if I have a lot of Do/Loops,
For/Eaches, If/EndIfs which involves declaring a boolean, booProceed and
things like .EOF or other show stoppers set booProceed to false.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:kb********************************@4ax.com:

140 With MyDB
170 Set qdfType17CorrespRecs = .CreateQueryDef("", "Select
* FROM tblCorrespondence;")
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
.OpenRecordset(dbOpenSnapshot)
230 ' With rstType17CorrespRecs
231 ' If .BOF = True Then GoTo NoRecs
240 ' .MoveFirst
250 ' Do Until rstType17CorrespRecs.EOF

How do you set MyDB?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

Let's suppose that rstType17CorrespRecs .BOF is True.
Will we ever open rstRRs?

Let's suppose rstRRs is not opened.
Will rstRRs.Close generate an error?

Let's suppose rstRRs.Close generates an error.
Will erl report the last line number encountered?
Will that be 650?
Would line 650 have anything to do with the error at all?

[Randy Harris]

"MLH" <CR**@NorthState.net> wrote in message
news:c6********************************@4ax.com...

On Sat, 31 Dec 2005 14:53:14 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:

If the recordset has no records, both BOF and EOF will be true at the sametime. So, in line 231, if there are no records then BOF should be true.
However, lines 230 through 250 are currently "commented out", so they aren'texecuting.

Yes, I'd commented them out to determine the point at which the
erroneous condition arose. I had 5 nested With statements and
the darned error wasn't reporting until execution of line #
650 GoTo CheckITS10DayStandby_Exit
and it wasn't at all clear to me where the REAL problem arose.

You aren't showing the entire code. Make sure your End With statements lineup where you expect them to. If you were missing one, you should get a
compile error, but if it is misplaced, you won't get the compile error butmay get the error you're getting.

FYI, line 250 can be shortened to

Do Until .EOF

Which line gives the 91 error?

Line #650

Here's the entire procedure...

Sub CheckITS10DayStandby()
On Error GoTo CheckITS10DayStandby_Err
MySQL = "SELECT tblCorrespondence.CorrespID,
tblCorrespondence.VehicleJobID, tblCorrespondence.OutDate, "
MySQL = MySQL & "tblCorrespondence.OutType,
tblCorrespondence.OutProcessor, tblCorrespondence.InDate, "
MySQL = MySQL & "tblCorrespondence.InRefDate,
tblCorrespondence.InType, tblCorrespondence.InProcessor, "
MySQL = MySQL & "tblCorrespondence.ToWhom,
tblCorrespondence.CorrespTDStamp, tblCorrespondence.UserID, "
MySQL = MySQL & "tblCorrespondence.Tracked FROM tblCorrespondence
WHERE (((tblCorrespondence.OutDate) "
MySQL = MySQL & "Is Not Null) AND ((tblCorrespondence.OutType)='17')
AND ((tblCorrespondence."
MySQL = MySQL & "OutProcessor) Is Not Null) AND
((tblCorrespondence.InDate) Is Not Null) AND "
MySQL = MySQL & "((tblCorrespondence.InRefDate) Is Not Null) AND
((tblCorrespondence.InType) Is Not Null) "
MySQL = MySQL & "AND ((tblCorrespondence.InProcessor) Is Not Null) AND
((tblCorrespondence.Tracked)=True)) "
MySQL = MySQL & "AND ((tblVehicleJobs.Reclaimed)=False));"

100 Dim MyDB As Database, qdfType17CorrespRecs As QueryDef,
rstType17CorrespRecs As Recordset
110 Dim qdfRRs As QueryDef, rstRRs As Recordset, GottaWait As
Boolean, WaitTime As Byte
120 Set MyDB = CurrentDb ' Set MyDB =
OpenDatabase("Northwind.mdb") is alternate syntax.
121 WaitTime = DLookup("[CertMailResponseWaitTime]", "tblAdmin")
130
140 With MyDB
160 Set qdfType17CorrespRecs = .CreateQueryDef("", MySQL)
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
.OpenRecordset(dbOpenSnapshot)
230 With rstType17CorrespRecs
231 If .BOF = True Then GoTo NoRecs
240 .MoveFirst
250 Do Until rstType17CorrespRecs.EOF
260 CurrentVehicleJobID = !VehicleJobID
270 CurrentCorrespondenceRecordID = !CorrespID
280 GottaWait = False
330 Set qdfRRs = MyDB.CreateQueryDef("", "Select *
From tblReturnReceipts Where
[CorrespID]=GetCurrentCorrespondenceRecordID();")
340 With qdfRRs
350 Set rstRRs = .OpenRecordset(dbOpenSnapshot)
'This'll get about 3-5 records
360 With rstRRs
361 If .BOF = True Then GoTo NoRecs
370 .MoveFirst
380 Do Until rstRRs.EOF
390 If Int(Now()) - !DateSigned <
WaitTime Then GottaWait = True
400 .MoveNext
410 Loop
420 .Close
430 If GottaWait = False Then
440 'First, update the current corresp
rec...
450 Dim SQL2UpdateCorrespRec As String
460 SQL2UpdateCorrespRec = "UPDATE
tblCorrespondence SET tblCorrespondence.Tracked = False WHERE
tblCorrespondence."
462 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "CorrespID=GetCurrentCorrespondenceRecordID(); "
470 'DoCmd.SetWarnings False
480 DoCmd.RunSQL SQL2UpdateCorrespRec
490 DoCmd.SetWarnings True
500 'Next, append an OutType-18 corresp
rec
510 Dim SQL2AppendRec2CorrespTbl As
String
520 SQL2AppendRec2CorrespTbl = "INSERT
INTO tblCorrespondence (VehicleJobID, OutType, ToWhom, UserID) SELECT
"
522 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "GetCurrentVehicleJobID() AS VehicleJobID, '18'
AS OutType, "
524 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "'DMV' AS ToWhom, CurrentUser() AS UserID;"
530 'DoCmd.SetWarnings False
540 DoCmd.RunSQL
SQL2AppendRec2CorrespTbl
550 DoCmd.SetWarnings True
560 End If
570 End With
580 End With
590 .MoveNext
600 Loop
610 .Close
620 End With
630 End With
640 End With
650 GoTo CheckITS10DayStandby_Exit

NoRecs:
rstRRs.Close
rstType17CorrespRecs.Close

CheckITS10DayStandby_Exit:
Exit Sub

CheckITS10DayStandby_Err:
Dim r As String, Z As String, Message3 As String
r = "The following unexpected error occurred in Function
CheckITS10DayStandby(), line #" & CStr(Erl) & ", when called from
frmCron:"
Z = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & Z
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume CheckITS10DayStandby_Exit

End Sub

IMO -

You are making your code much more difficult to debug and maintain by
nesting all of those With statements. There is no good reason. For
example, in line 420 you close the recordset. Why not put an End With
immediately following.

Eeeks, line 330 through 420 inclusive, the entire section can be replaced
with a single DLookUp.

Don't be afraid to insert comments as you code. You'll thank yourself down
the road.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Steve Jorgensen]

If you want to debug your code, first don't make it 100 times harder to manage
and debug than it needs to be.

In general, you want to write your code to avoid deeply nested structures, and
nesting With blocks is especially troublesome. Gotos should be used very
sparingly, and should never be used to play leapfrog over bits of code. The
VBA documentation also specifically warns that using a goto from within a With
block can have unpredictable consequences.

Another thing - why use RunSQL when you're already using DAO for other tasks?
Using MyDB.Execute <sql>, dbFailOnError gives you much better control and runs
faster. DLookup also has performance issues compared with opening recordsets,
but its simplicity justifies its use here.

Finally, I see you are using variables (e.g. MySQL) without declaring them
which means you are either using a global variables or not using Option
Explicit in your modules. Either of these is bad because not using Option
Explicit allows a variable to be used without declaring it even if it's just a
typo, and global variables make it hard to track down how and when a variable
gets it value or avoid overwriting an expected value with a nested procedure
call.

I suggest rewiting the code something like below. The code is untested, of
course, but the structure is simple and straightforward enough to have a hope
of debugging and getting into working condition.
Public Sub CheckITS10DayStandby()
On Error GoTo Err_Catch

Const cstrSQLSelect = _
"SELECT tblCorrespondence.CorrespID, " & _
"tblCorrespondence.VehicleJobID, " & _
"tblCorrespondence.OutDate, " & _
"tblCorrespondence.OutType, " & _
"tblCorrespondence.OutProcessor, " & _
"tblCorrespondence.InDate, " & _
"tblCorrespondence.InRefDate, " & _
"tblCorrespondence.InType, " & _
"tblCorrespondence.InProcessor, " & _
"tblCorrespondence.ToWhom, " & _
"tblCorrespondence.CorrespTDStamp, " & _
"tblCorrespondence.UserID, " & _
"tblCorrespondence.Tracked "

Const cstrSQLFromWhere = _
"FROM tblCorrespondence " & _
"WHERE tblCorrespondence.OutDate Is Not Null AND " & _
"tblCorrespondence.OutType='17' AND " & _
"tblCorrespondence.OutProcessor Is Not Null AND " & _
"tblCorrespondence.InDate Is Not Null AND " & _
"tblCorrespondence.InRefDate Is Not Null AND " & _
"tblCorrespondence.InType Is Not Null AND " & _
"tblCorrespondence.InProcessor Is Not Null AND " & _
"tblCorrespondence.Tracked<>False AND " & _
"tblVehicleJobs.Reclaimed=False;"

Const cstrQuerySQL = cstrSQLSelect & _
cstrSQLFromWhere

Const cstrUpdateCorrespRecSQL = _
"UPDATE tblCorrespondence " & _
"SET tblCorrespondence.Tracked = False " & _
"WHERE tblCorrespondence.CorrespID=" & _
"GetCurrentCorrespondenceRecordID();"

Const cstrAppendRec2CorrespTbl = _
"INSERT INTO tblCorrespondence " & _
"(VehicleJobID, OutType, ToWhom, UserID) " & _
"SELECT GetCurrentVehicleJobID() AS VehicleJobID, " & _
"'18' AS OutType, " & _
"'DMV' AS ToWhom, " & _
"CurrentUser() AS UserID;"
'Now, we can see it looks like a FROM clause is missing.

Dim dbs As DAO.Database
Set dbs = CurrentDB

Dim qdfType17CorrespRecs As DAO.Querydef
Set qdfType17CorrespRecs = dbs.CreateQueryDef("", MySQL)

Dim rstType17CorrespRecs As DAO.Recordset
Set rstType17CorrespRecs = _
qdfType17CorrespRecs.OpenRecordset(dbOpenSnapshot)

Dim blnWait As Boolean

Dim lngWaitTime As Long
lngWaitTime = DLookup("[CertMailResponseWaitTime]", _
"tblAdmin")

'If recordset has no records, will not loop even once.
Do Until rstType17CorrespRecs.EOF

'Where are these variables declared?
CurrentVehicleJobID = !VehicleJobID
CurrentCorrespondenceRecordID = !CorrespID
blnWait = False

'This'll get about 3-5 records
Set rstRRs = dbs.OpenRecordset( _
"Select * " & _
"From tblReturnReceipts " & _
"Where [CorrespID]=GetCurrentCorrespondenceRecordID();", _
dbOpenSnapshot)

If rstRRs.BOF = True Then
rstRRs.Close: Set rstRRs = Nothing
Exit Do
End If

Do Until rstRRs.EOF
If Int(Now()) - !DateSigned < lngWaitTime Then blnWait = True
rstRRs.MoveNext
Loop

rstRRs.Close: Set rstRRs = Nothing

If Not blnWait Then
'First, update the current corresp rec...
dbs.Execute cstrUpdateCorrespRecSQL, dbFailOnError
'Next, append an OutType-18 corresp rec
dbs.Execute cstrAppendRec2CorrespTbl, dbFailOnError
End If

rstType17CorrespRecs.MoveNext

Loop

rstType17CorrespRecs.Close
Set rstType17CorrespRecs = Nothing

Proc_Final:
Exit Sub

Err_Catch:
MsgBox "The following unexpected error occurred " & _
"in Function CheckITS10DayStandby():" & _
vbCrLf & vbCrLf & _
"(" & Err.Description & ")", _
vbExclamation + vbOkOnly, _
"Unexpected Error - " & MyApp$ & ", " & _
"rev. " & MY_VERSION$

Resume Proc_Final

End Sub

On Sat, 31 Dec 2005 14:55:29 -0500, MLH <CR**@NorthState.net> wrote:

On Sat, 31 Dec 2005 14:53:14 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:

If the recordset has no records, both BOF and EOF will be true at the same
time. So, in line 231, if there are no records then BOF should be true.
However, lines 230 through 250 are currently "commented out", so they aren't
executing.

Yes, I'd commented them out to determine the point at which the
erroneous condition arose. I had 5 nested With statements and
the darned error wasn't reporting until execution of line #
650 GoTo CheckITS10DayStandby_Exit
and it wasn't at all clear to me where the REAL problem arose.

You aren't showing the entire code. Make sure your End With statements line
up where you expect them to. If you were missing one, you should get a
compile error, but if it is misplaced, you won't get the compile error but
may get the error you're getting.

FYI, line 250 can be shortened to

Do Until .EOF

Which line gives the 91 error?

Line #650

Here's the entire procedure...

....

[MLH]

On Sat, 31 Dec 2005 14:25:38 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

MLH <CR**@NorthState.net> wrote in
news:kb********************************@4ax.com :

140 With MyDB
170 Set qdfType17CorrespRecs = .CreateQueryDef("", "Select
* FROM tblCorrespondence;")
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
.OpenRecordset(dbOpenSnapshot)
230 ' With rstType17CorrespRecs
231 ' If .BOF = True Then GoTo NoRecs
240 ' .MoveFirst
250 ' Do Until rstType17CorrespRecs.EOF

How do you set MyDB?

100 Dim MyDB As Database, qdfType17CorrespRecs As QueryDef,
rstType17CorrespRecs As Recordset
110 Dim qdfRRs As QueryDef, rstRRs As Recordset, GottaWait As
Boolean, WaitTime As Byte
120 Set MyDB = CurrentDb

[MLH]

On 31 Dec 2005 12:37:24 -0800, "Lyle Fairfield"
<ly***********@aim.com> wrote:

Let's suppose that rstType17CorrespRecs .BOF is True.
Will we ever open rstRRs?

Let's suppose rstRRs is not opened.
Will rstRRs.Close generate an error?

Let's suppose rstRRs.Close generates an error.
Will erl report the last line number encountered?
Will that be 650?
Would line 650 have anything to do with the error at all?

So, my error condition originated with rstRRs not opening
(because it had no records), then later rstRRs.Close being
run against a recset that had not been opened in the first
place. I think that's the point you're making. If I understand
you correctly, would a good work-a-round be a quick dcount
against the dynaset prior to invoking any DAO? Someone
did mention doing a count from within DAO. Would you
prefer one over the other?

[MLH]

On Sat, 31 Dec 2005 22:59:15 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Does this work in place of line 231:
If .RecordCount = 0 Then
Go To NoRecs
End If I think DAO requires you advance through the last record before using
the .RecordCount property. MoveFirst and MoveLast generate an error
if there are no rec's in the recset. I'm thinking the above cannot be
used because of this condition???

There's bunch of things different here than I would do. You don't need a
temp query created, just OpenRecordset on the query statement. And use
dbOpenDynaset (esp. if you plan to MoveFirst.)

Try:
Dim MyDB As DAO.Database
Dim rstType17CorrespRecs As DAO.Recordset
Dim strSql As String

strSql = "Select * FROM tblCorrespondence;"
Set rstType17CorrespRecs = MyDB.OpenRecordset(strSql)
If rstType17CorrespRecs.RecordCount > 0 Then
'do your stuff here.
End If
rstType17CorrespRecs.Close

[Lyle Fairfield]

I would prefer neither.

I suggest you that you follow Allen Browne's advice.

[rkc]

MLH wrote:

On Sat, 31 Dec 2005 22:59:15 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Does this work in place of line 231:
If .RecordCount = 0 Then
Go To NoRecs
End If

I think DAO requires you advance through the last record before using
the .RecordCount property. MoveFirst and MoveLast generate an error
if there are no rec's in the recset. I'm thinking the above cannot be
used because of this condition???

You don't have to use movelast before using recordcount if all you are
trying to determine is if there were any records returned. You do if you
want to know the actual number returned.

You seem to have completely ignored the best advice you have been given
in this thread. That advice was to clean up and simplify your code so
that following the logic isn't virtually impossible.

I would add to that advice by suggesting that you build your procedures
in small steps. Test that the current small step works as expected
before moving on to the next small step. That way you are dealing with
one error at a time and you have it isolated to the current small step.

You really should stop torturing yourself and buy yourself a copy
of Code Complete 2. You will write better code the first day you
begin to read it. It's an investment on par with the revered Access
Developer's Handbook in my opinion.

[Lyle Fairfield]

rkc wrote:

I would add to that advice by suggesting that you build your procedures
in small steps. Test that the current small step works as expected
before moving on to the next small step. That way you are dealing with
one error at a time and you have it isolated to the current small step.

Very Small Steps. The original MySQL string is flawed. It has a
condition:
((tblVehicleJobs.Reclaimed)=False));"
but no other reference to tblVehicleJobs.

This error is amplified by other errors. And the whole mess is
compounded by Goto and Erl. These two abominations (YES LUKE,
"ABOMINATIONS!") make it almost impossible to find out where errors are
occurring. When you look at MySQL you say, "Whoa, we can't ever get to
the closing the recordset that was never opened error, because this
error happens first."

Then we say ... oh ... well, er ... maybe ... something else is
happening:

Perhaps the mySQLstring is saved as a querydef because we can run the
saved query; we simply have to supply a value for the Parameter
[tblVehicleJobs.Reclaimed]. If we try to use the MySQL string directly
we get an "Too few Parameters; Expected 1" error.
This may also explain why there are no records in the Recordset created
by opening this QueryDef for we cannot (in Access 2003/ JET 4.0) use
True or False as the Parameter value; both these return No Records. To
get records we have to use zero (0) in place of False (well how could
JET know this ill-formed parameter expected a boolean anyway, huh?).

I strongly feel this code is best trashed, although its inclusion in
some archive as an example of the dangers of Goto and Erl might be in
order. I suspect the whole thing could be accomplished with a few lines
of SQL, but I'm not entirely sure of that. In any case, starting over
would be my recommendation and as you suggest, proceeding in tiny, tiny
steps.

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:tg********************************@4ax.com:

On Sat, 31 Dec 2005 22:59:15 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Does this work in place of line 231:
If .RecordCount = 0 Then
Go To NoRecs
End If I think DAO requires you advance through the last record before
using the .RecordCount property. . . .

You think wrong.

Again, if you'd but read the frigging help file on the topic
"RecordCount" you'd know that what you have believed is WRONG.
. . . MoveFirst and MoveLast generate an error
if there are no rec's in the recset. I'm thinking the above cannot
be used because of this condition???

Those are completely different commands. They move the current
record pointer, but RecordCount is a property of the recordset.

You *do* understand the different between properties and methods,
right?

For what it's worth, I always check the recordcount to determine if
there are records, since I think it's simpler than checking *two*
properties (BOF, EOF). Secondly, since the piece of information I'm
trying to find out is not the position of the recordset pointer, but
the number of records, it makes more sense to me to check the
property that returns the number of records, as opposed to two
properties that tell you if the recordset pointer is before or after
the end of the recordset.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Wayne Morgan]

Let's try this one step at a time. First, try to code without all of the
GoTo's. It will simplify trying to read your code later. For example, line
231 instead of "If .BOF = True Then GoTo NoRecs" should be "If Not .BOF
Then". If BOF is True then you won't execute the statements inside the
If..Then becuase you have told it to continue only if the opposite of BOF is
True. Therefore, if BOF is False, Not BOF is True and the statements inside
the If...Then will execute.

MySQL = MySQL & "AND ((tblVehicleJobs.Reclaimed)=False));"

There is no mention of this table, tblVehicleJobs, in the FROM clause of the
SQL statement. You need to add this table to the FROM clause and state how
it is joined to tblCorrespondence.

As Allen mentioned, in line 160 you create a temporary QueryDef, but the
only thing you do with it is open a recordset. You could open the recordset
directly by using the SQL statement, you don't need the temporary QueryDef.
This will get rid of one of your With statements. This also applies to line
330.

As far as needing to MoveLast to get the RecordCount. You're correct that to
get an accurate RecordCount you need to MoveLast. But just to see if there
are any records (i.e. RecordCount > 0), this isn't necessary.

For the DoCmd.RunSQL statements, I would recommend a different syntax.
Instead of turning Warings off and on, with the DoCmd.RunSQL inbetween, use
this instead:

MyDB.Execute SQL2UpdateCorrespRec, dbFailOnError

This will execute the query without the prompt, just as setting Warning off
does, but will also abort and return an error if it fails to run
successfully.

In lines 520-524, you're not consistent with your variable name. This should
have been caught by the compiler. At the top of the module where it says
"Option Compare Database" there should also be a line that says "Option
Explicit". If there isn't, you need to add it. Also add it to the top of all
other modules you've created (standard modules, form modules, report
modules). You can have this added automatically to the top of new modules as
they are created if you go to Tools|Options|Editor tab and check the box
"Require Variable Declaration". This will force you to DIM each variable you
use, which you appear to be trying to do anyway, but will give an error for
any variable you fail to DIM. This will, in effect, give you a spell check
because a misspelled variable is probably an undeclared variable.

As a general rule, anything you open you should close and anything you set
you should set to nothing. You are closing your recordsets, but you aren't
setting your object variables to nothing when you're done. You should "clean
up" in the Exit part of your code.

CheckITS10DayStandby_Exit:
On Error Resume Next 'this will be controversial
'These next two lines can be removed from NoRecs:
'since they will be executed here. Doing so here lets
'them execute even if there has been an error.
rstRRs.Close
rstType17CorrespRecs.Close
Set rstRRs = Nothing
Set rstType17CorrespRecs = Nothing
'Set the QDFs to nothing also if you keep them,
'but you should get rid of them, they aren't needed
Set MyDB = Nothing
Exit Sub

In your INSERT SQL statements, it appears you may have some spaces missing
when you do the concatenation. Make sure there is a space inside the quotes
at the end of the previous line or the start of the current line, when
needed. For example, in line 520, the " is immediately after the T in SELECT
and there is no space before the word GetCurrentVehicleJobID in line 522.

--
Wayne Morgan
MS Access MVP
"MLH" <CR**@NorthState.net> wrote in message
news:c6********************************@4ax.com...

On Sat, 31 Dec 2005 14:53:14 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:

If the recordset has no records, both BOF and EOF will be true at the same
time. So, in line 231, if there are no records then BOF should be true.
However, lines 230 through 250 are currently "commented out", so they
aren't
executing.

Yes, I'd commented them out to determine the point at which the
erroneous condition arose. I had 5 nested With statements and
the darned error wasn't reporting until execution of line #
650 GoTo CheckITS10DayStandby_Exit
and it wasn't at all clear to me where the REAL problem arose.

You aren't showing the entire code. Make sure your End With statements
line
up where you expect them to. If you were missing one, you should get a
compile error, but if it is misplaced, you won't get the compile error but
may get the error you're getting.

FYI, line 250 can be shortened to

Do Until .EOF

Which line gives the 91 error?

Line #650

Here's the entire procedure...

Sub CheckITS10DayStandby()
On Error GoTo CheckITS10DayStandby_Err
MySQL = "SELECT tblCorrespondence.CorrespID,
tblCorrespondence.VehicleJobID, tblCorrespondence.OutDate, "
MySQL = MySQL & "tblCorrespondence.OutType,
tblCorrespondence.OutProcessor, tblCorrespondence.InDate, "
MySQL = MySQL & "tblCorrespondence.InRefDate,
tblCorrespondence.InType, tblCorrespondence.InProcessor, "
MySQL = MySQL & "tblCorrespondence.ToWhom,
tblCorrespondence.CorrespTDStamp, tblCorrespondence.UserID, "
MySQL = MySQL & "tblCorrespondence.Tracked FROM tblCorrespondence
WHERE (((tblCorrespondence.OutDate) "
MySQL = MySQL & "Is Not Null) AND ((tblCorrespondence.OutType)='17')
AND ((tblCorrespondence."
MySQL = MySQL & "OutProcessor) Is Not Null) AND
((tblCorrespondence.InDate) Is Not Null) AND "
MySQL = MySQL & "((tblCorrespondence.InRefDate) Is Not Null) AND
((tblCorrespondence.InType) Is Not Null) "
MySQL = MySQL & "AND ((tblCorrespondence.InProcessor) Is Not Null) AND
((tblCorrespondence.Tracked)=True)) "
MySQL = MySQL & "AND ((tblVehicleJobs.Reclaimed)=False));"

100 Dim MyDB As Database, qdfType17CorrespRecs As QueryDef,
rstType17CorrespRecs As Recordset
110 Dim qdfRRs As QueryDef, rstRRs As Recordset, GottaWait As
Boolean, WaitTime As Byte
120 Set MyDB = CurrentDb ' Set MyDB =
OpenDatabase("Northwind.mdb") is alternate syntax.
121 WaitTime = DLookup("[CertMailResponseWaitTime]", "tblAdmin")
130
140 With MyDB
160 Set qdfType17CorrespRecs = .CreateQueryDef("", MySQL)
180 With qdfType17CorrespRecs
210 ' Open Recordset from QueryDef.
220 Set rstType17CorrespRecs =
.OpenRecordset(dbOpenSnapshot)
230 With rstType17CorrespRecs
231 If .BOF = True Then GoTo NoRecs
240 .MoveFirst
250 Do Until rstType17CorrespRecs.EOF
260 CurrentVehicleJobID = !VehicleJobID
270 CurrentCorrespondenceRecordID = !CorrespID
280 GottaWait = False
330 Set qdfRRs = MyDB.CreateQueryDef("", "Select *
From tblReturnReceipts Where
[CorrespID]=GetCurrentCorrespondenceRecordID();")
340 With qdfRRs
350 Set rstRRs = .OpenRecordset(dbOpenSnapshot)
'This'll get about 3-5 records
360 With rstRRs
361 If .BOF = True Then GoTo NoRecs
370 .MoveFirst
380 Do Until rstRRs.EOF
390 If Int(Now()) - !DateSigned <
WaitTime Then GottaWait = True
400 .MoveNext
410 Loop
420 .Close
430 If GottaWait = False Then
440 'First, update the current corresp
rec...
450 Dim SQL2UpdateCorrespRec As String
460 SQL2UpdateCorrespRec = "UPDATE
tblCorrespondence SET tblCorrespondence.Tracked = False WHERE
tblCorrespondence."
462 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "CorrespID=GetCurrentCorrespondenceRecordID(); "
470 'DoCmd.SetWarnings False
480 DoCmd.RunSQL SQL2UpdateCorrespRec
490 DoCmd.SetWarnings True
500 'Next, append an OutType-18 corresp
rec
510 Dim SQL2AppendRec2CorrespTbl As
String
520 SQL2AppendRec2CorrespTbl = "INSERT
INTO tblCorrespondence (VehicleJobID, OutType, ToWhom, UserID) SELECT
"
522 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "GetCurrentVehicleJobID() AS VehicleJobID, '18'
AS OutType, "
524 SQL2UpdateCorrespRec =
SQL2UpdateCorrespRec & "'DMV' AS ToWhom, CurrentUser() AS UserID;"
530 'DoCmd.SetWarnings False
540 DoCmd.RunSQL
SQL2AppendRec2CorrespTbl
550 DoCmd.SetWarnings True
560 End If
570 End With
580 End With
590 .MoveNext
600 Loop
610 .Close
620 End With
630 End With
640 End With
650 GoTo CheckITS10DayStandby_Exit

NoRecs:
rstRRs.Close
rstType17CorrespRecs.Close

CheckITS10DayStandby_Exit:
Exit Sub

CheckITS10DayStandby_Err:
Dim r As String, Z As String, Message3 As String
r = "The following unexpected error occurred in Function
CheckITS10DayStandby(), line #" & CStr(Erl) & ", when called from
frmCron:"
Z = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & Z
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume CheckITS10DayStandby_Exit

End Sub

[David W. Fenton]

"Wayne Morgan" <co***************************@hotmail.com> wrote in
news:fG*******************@newssvr29.news.prodigy. net:

As a general rule, anything you open you should close and anything
you set you should set to nothing. You are closing your
recordsets, but you aren't setting your object variables to
nothing when you're done. You should "clean up" in the Exit part
of your code.

CheckITS10DayStandby_Exit:
On Error Resume Next 'this will be controversial
Controversial? Well, you're welcome to do it this way if you want to
have unreliable code.

I would *never* turn off error handling except for one line at a
time. If I did it for more lines, I would always turn it back on
with On Error Resume 0 after the block in which I want errors
ignored is past. You may *think* that the On Error Resume Next goes
out of scope after the subroutine exits, but I have found that you
cannot rely on this happening.
'These next two lines can be removed from NoRecs:
'since they will be executed here. Doing so here lets
'them execute even if there has been an error.
rstRRs.Close
rstType17CorrespRecs.Close
Set rstRRs = Nothing
Set rstType17CorrespRecs = Nothing
'Set the QDFs to nothing also if you keep them,
'but you should get rid of them, they aren't needed
Set MyDB = Nothing
Exit Sub

I would always do:

If Not (rstRRs Is Nothing) Then
rstRRs.Close
Set rstRRs = Nothing
End If
If Not (rstType17CorrespRecs Is Nothing) Then
rstType17CorrespRecs.Close
Set rstType17CorrespRecs = Nothing
End If
Set MyDB = Nothing

Note that you don't need to close the database variable, since it
was initialized with CurrentDB() and refers to the MDB currently
open in the Access UI, so you can't close it. However, MyDB.Close
will not actually cause an error (unless it hasn't been
initialized).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Larry Linson]

"Lyle Fairfield" <ly***********@aim.com> wrote

This error is amplified by other errors. And the whole
mess is compounded by Goto and Erl. These two
abominations (YES LUKE, "ABOMINATIONS!")
make it almost impossible to find out where errors are
occurring.

I once knew a feller whose wife thought his Uncle Earl was an ABOMINATION. I
never knew anybody who had an Uncle
GoTo... knew one with an Uncle Otto... does that count?

[Wayne Morgan]

The reason I didn't set error handling back on is because, as you mention,
it should go out of scope when the procedure ends, which is the next step
the procedure performs anyway. I understand your comment about it not always
working as it should. I've run into a few of those myself.

As far as only turning it off for one line at a time, if I have a line that
it doesn't really matter if I see the error message then I turn it off. I
don't see a difference if that be one line or ten. If it doesn't matter, it
doesn't matter. Would you, if you had more than one line where you would
turn handling off, turn it back on then off again before executing the next
line if those lines are executed sequentially?

If Not (rstRRs Is Nothing) Then
rstRRs.Close
Set rstRRs = Nothing
End If
I would expect this to still error if the recordset had been closed but the
variable not released. In fact, setting a released variable to Nothing a
second time, as far as I've seen in testing, doesn't do anything good or
bad. However, in the above, if the variable is still set but the recordset
is closed, you're still going to get an error (#3420, Object invalid or no
longer set.) on the rstRRs.Close statement because the If...Then is still
True.

Example:
Having the following in the code:
rst.Close
Debug.Print "rst Is Nothing = " & (rst Is Nothing)
Set rst = Nothing

Will give the following in the debug window:
rst Is Nothing = False
--
Wayne Morgan
MS Access MVP
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Wayne Morgan" <co***************************@hotmail.com> wrote in
news:fG*******************@newssvr29.news.prodigy. net:

As a general rule, anything you open you should close and anything
you set you should set to nothing. You are closing your
recordsets, but you aren't setting your object variables to
nothing when you're done. You should "clean up" in the Exit part
of your code.

CheckITS10DayStandby_Exit:
On Error Resume Next 'this will be controversial

Controversial? Well, you're welcome to do it this way if you want to
have unreliable code.

I would *never* turn off error handling except for one line at a
time. If I did it for more lines, I would always turn it back on
with On Error Resume 0 after the block in which I want errors
ignored is past. You may *think* that the On Error Resume Next goes
out of scope after the subroutine exits, but I have found that you
cannot rely on this happening.

'These next two lines can be removed from NoRecs:
'since they will be executed here. Doing so here lets
'them execute even if there has been an error.
rstRRs.Close
rstType17CorrespRecs.Close
Set rstRRs = Nothing
Set rstType17CorrespRecs = Nothing
'Set the QDFs to nothing also if you keep them,
'but you should get rid of them, they aren't needed
Set MyDB = Nothing
Exit Sub

I would always do:

If Not (rstRRs Is Nothing) Then
rstRRs.Close
Set rstRRs = Nothing
End If
If Not (rstType17CorrespRecs Is Nothing) Then
rstType17CorrespRecs.Close
Set rstType17CorrespRecs = Nothing
End If
Set MyDB = Nothing

Note that you don't need to close the database variable, since it
was initialized with CurrentDB() and refers to the MDB currently
open in the Access UI, so you can't close it. However, MyDB.Close
will not actually cause an error (unless it hasn't been
initialized).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Wayne Morgan" <co***************************@hotmail.com> wrote in
news:n3*******************@newssvr29.news.prodigy. net:

The reason I didn't set error handling back on is because, as you
mention, it should go out of scope when the procedure ends, which
is the next step the procedure performs anyway. I understand your
comment about it not always working as it should. I've run into a
few of those myself.

As far as only turning it off for one line at a time, if I have a
line that it doesn't really matter if I see the error message then
I turn it off. I don't see a difference if that be one line or
ten. If it doesn't matter, it doesn't matter. Would you, if you
had more than one line where you would turn handling off, turn it
back on then off again before executing the next line if those
lines are executed sequentially?

If Not (rstRRs Is Nothing) Then
rstRRs.Close
Set rstRRs = Nothing
End If
I would expect this to still error if the recordset had been
closed but the variable not released. . . .

Eh? Assuming DAO, that can't happen.
. . . In fact, setting a released variable to Nothing a
second time, as far as I've seen in testing, doesn't do anything
good or bad. However, in the above, if the variable is still set
but the recordset is closed, you're still going to get an error
(#3420, Object invalid or no longer set.) on the rstRRs.Close
statement because the If...Then is still True.
Well, how would the recordset get closed?
Example:
Having the following in the code:
rst.Close
Debug.Print "rst Is Nothing = " & (rst Is Nothing)
Set rst = Nothing

Will give the following in the debug window:
rst Is Nothing = False

Er, where would the recordset get closed?

I only close recordsets in the exitRoutine, so there's no possiblity
for a DAO recordset in my code to be Not Nothing and also closed.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Wayne Morgan]

> Er, where would the recordset get closed?

I only close recordsets in the exitRoutine, so there's no possiblity
for a DAO recordset in my code to be Not Nothing and also closed.

But the user who wrote the original routine in this item closed the
recordset as soon as they were done with it, but hadn't set the variable to
Nothing. However, if there had been an error and the error handler was
activated, the Close statement would have been missed, hence the need to
repeat it before leaving the routine but after leaving the error handler.

As you state, you only close them in the exit part of the routine, so,
you're correct, this wouldn't be a repeat or a problem if you had written
the original code.

--
Wayne Morgan
MS Access MVP
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Wayne Morgan" <co***************************@hotmail.com> wrote in
news:n3*******************@newssvr29.news.prodigy. net:

The reason I didn't set error handling back on is because, as you
mention, it should go out of scope when the procedure ends, which
is the next step the procedure performs anyway. I understand your
comment about it not always working as it should. I've run into a
few of those myself.

As far as only turning it off for one line at a time, if I have a
line that it doesn't really matter if I see the error message then
I turn it off. I don't see a difference if that be one line or
ten. If it doesn't matter, it doesn't matter. Would you, if you
had more than one line where you would turn handling off, turn it
back on then off again before executing the next line if those
lines are executed sequentially?

If Not (rstRRs Is Nothing) Then
rstRRs.Close
Set rstRRs = Nothing
End If

I would expect this to still error if the recordset had been
closed but the variable not released. . . .

Eh? Assuming DAO, that can't happen.

. . . In fact, setting a released variable to Nothing a
second time, as far as I've seen in testing, doesn't do anything
good or bad. However, in the above, if the variable is still set
but the recordset is closed, you're still going to get an error
(#3420, Object invalid or no longer set.) on the rstRRs.Close
statement because the If...Then is still True.

Well, how would the recordset get closed?

Example:
Having the following in the code:
rst.Close
Debug.Print "rst Is Nothing = " & (rst Is Nothing)
Set rst = Nothing

Will give the following in the debug window:
rst Is Nothing = False

Er, where would the recordset get closed?

I only close recordsets in the exitRoutine, so there's no possiblity
for a DAO recordset in my code to be Not Nothing and also closed.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Wayne Morgan" <co***************************@hotmail.com> wrote in
news:yG******************@newssvr12.news.prodigy.c om:

Er, where would the recordset get closed?

I only close recordsets in the exitRoutine, so there's no
possiblity for a DAO recordset in my code to be Not Nothing and
also closed.

But the user who wrote the original routine in this item closed
the recordset as soon as they were done with it, but hadn't set
the variable to Nothing. However, if there had been an error and
the error handler was activated, the Close statement would have
been missed, hence the need to repeat it before leaving the
routine but after leaving the error handler.

As you state, you only close them in the exit part of the routine,
so, you're correct, this wouldn't be a repeat or a problem if you
had written the original code.

You make a good point: these recommendations do exist in a vacuum.
If you do things in certain ways, it has an impact on how you do
other things.

While in general I'd prefer to close things as soon as I'm done with
them, I don't do that with recordsets because of the problems it can
lead to with cleanup later on. As with the recommendation to have
only one exit point from a subroutine, I think it's best to have one
cleanup point, as well.

A long time ago I used to declare my variables right where they were
used. Then I realized this became a real pain when trying to figure
out where variables were declared. So, now, I will often declare a
variable inline while writing a subsection of code, then when
finished, move the variable declaration up to the top.

To me, cleaning up objects in the exit routine is a similar kind of
practice, and has the side benefit of preventing exactly the kind of
error you were pointing out.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Terry Kreft]

Having supported a lot of other peoples code over the years this is a pet
peeve of mine. I'm glad you don't do it any more <g>.

I must admit though that the boiler plate error handler I have in MZTools
does declare a variable for use in the error handler and I don't normally
bother moving it to the top of the procedure so in years to come others may
curse me <g>.

--
Terry Kreft

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
<SNIP>

A long time ago I used to declare my variables right where they were
used. Then I realized this became a real pain when trying to figure
out where variables were declared. So, now, I will often declare a
variable inline while writing a subsection of code, then when
finished, move the variable declaration up to the top. <SNIP>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/