Re: DAO peculiarity in A97?


"MLH" <CRCI@xxxxxxxxxxxxxx> wrote in message
news:c6ndr19936umir8pp4n01id8kmejuh36at@xxxxxxxxxx
> On Sat, 31 Dec 2005 14:53:14 GMT, "Wayne Morgan"
> <comprev_gothroughthenewsgroup@xxxxxxxxxxx> 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
>

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.


.

Relevant Pages