Re: DAO peculiarity in A97?

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" <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
>


.

Relevant Pages

  • Re: Help Getting .RecordCount from Stored Proc
    ... I am using VB 6 to execute a stored proc, ... I created a connection, command, and recordset with ADO. ... > Dim cnn1 As ADODB.Connection ... > Dim cmdProvider As ADODB.Command ...
    (microsoft.public.vb.database.ado)
  • Re: recordset not opening even though query analyzer returns resul
    ... I'm convinced that VB can execute this complex SP. ... > only way i could get an earlier version of it to return in a recordset. ... > table into a cursor and add up th evalues of the ccjs based on values in ... >> execute a query the SQL engine returns a resultset. ...
    (microsoft.public.vb.database.ado)
  • Re: UPDATE query in Access 2003 raising error
    ... The only reason I questioned the recordset is that you could conceivably ... the SQL looks fine to me. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: How to display a query in a list box using a record set?
    ... jim Bob wrote: ... I need to execute ... > Dim rs As Recordset ... > ways of opening/creating a recordset. ...
    (comp.databases.ms-access)
  • Re: ADO cloned recordset doesnt return field names
    ... It had been awhile since I worked with ADO. ... need to specifically loop through the recordset and use ".Name" to get at the ... >> I am using the following function to execute an ADO query. ... > Dim rs As ADODB.Recordset ...
    (microsoft.public.excel.programming)