Re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?

On Sat, 12 Jul 2008 09:37:55 -0700, Salad <oil@xxxxxxxxxxx> wrote:

MLH wrote:

Did it.

Option Compare Database
Option Explicit

Sub Tester()
Dim s As String
Dim r As Recordset
s = "Select * from qryBatchList"
Set r = CurrentDb.OpenRecordset(s, dbOpenDynaset)
MsgBox "Opened"
r.Close
Set r = Nothing
End Sub

Saved global module as Module4.

Debug window... call tester()
Error was a FN (known good & working) reported as undefined.


Here's qryBatchList SQL...
SELECT tblBatches.BatchID, [VColor] & " " & [VehicleYear] & " " &
[VehicleMake] & " [" & [SerialNum] & "]" AS Vehicle,
CMFNBackYet([tblVehicleJobs]![VehicleJobID],"qryBatchList") AS
ReadyFor906, tblBatches.[906Filed],
SoldYet([tblVehicleJobs]![VehicleJobID],"qryBatchList") AS
ReadyFor908, tblBatches.[908Filed], tblBatches.BDescr,
tblClusters.ClusterName
FROM tblBatches INNER JOIN (tblClusters INNER JOIN ((tblAdmin INNER
JOIN tblVehicleJobs ON tblAdmin.TowCoID = tblVehicleJobs.TowCoID)
INNER JOIN tblGottaGo2Court ON tblVehicleJobs.VehicleJobID =
tblGottaGo2Court.VehicleJobID) ON tblClusters.ClusterID =
tblAdmin.ClusterID) ON tblBatches.BatchID = tblGottaGo2Court.BatchID
WHERE tblBatches.BatchID=GetCurrentBatchID();

You have CMFNBackYet([tblVehicleJobs]![VehicleJobID],"qryBatchList") AS...

Why the argument/parameter "qryBatchList"? in the above line?
Good Q...
Dunno if it's considered good practice or not, but it saves my
g*** more often than I'll admit. I generally pass calling
procedure names or other calling object names to called sub's
'n FN's. For example, in the case of this FN...

Function AllCarsNbatchReady4906(CallingProcedure As String) As Boolean
'***************************************************************************
' Accepts name of calling procedure. Expects CurrentBatchID to
' have been set prior to calling this function. FN looks at all cars
' in current batch and returns True if all R ready 4 the tri-doc batch
' filing 2B made.
'
' FN created 7/12/2008
'***********-***************************************************************
On Error GoTo AllCarsNbatchReady4906_Err
Dim PString As String

100 Dim db As DAO.Database, rst As DAO.Recordset
120 Set db = CurrentDb
140 PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID
FROM qryBatchList WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
160 Set rst = db.OpenRecordset(PString, dbOpenDynaset)
180 AllCarsNbatchReady4906 = True
200 Do Until rst.EOF
220 AllCarsNbatchReady4906 = AllCarsNbatchReady4906 And
rst!ReadyFor906
240 Loop

AllCarsNbatchReady4906_Exit:
Exit Function

AllCarsNbatchReady4906_Err:
Dim r As String, z As String, Message3 As String
r = "The following unexpected error occurred in AppSpecific's
Function AllCarsNbatchReady4906(), line #" & Trim$(CStr(Erl))
z = ", when called from " & CallingProcedure & ":" & vbNewLine &
vbNewLine & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & z
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume AllCarsNbatchReady4906_Exit

End Function

.... The argument name "CallingProcedure" lets me display the query
name in the error message box if the procedure err's at runtime. Some-
times I call the same FN's from different places (different objects,
different procedures). Displaying the names of calling objects in
error messagboxes helps me fix stuff when it's broke.




What I'd do is start chopping the columns from functions, 1 by 1, and
then running Test, until you find the function that fails. The problem
is not in your syntax, it's in your functions or how you call the functions.
Well, I did that. Good suggestion. But provided no hint as to the root
of the problem. The saved query "qryBatchList" is the only thing
calling functions. That runs PERFECTLY launched from the database
window. I can't MAKE it fail from there. They only fail when the SQL
is launched from within a DAO setting.




BTW, you have a where statement that looks the same as the where
statement in your SQL statement. Why not remove that from your SQL
statement that's failing.
Yes, I see what you are referring to. The posted copy of qryBatchList
had a Where clause in it placing a criteria on the [BatchID] field
limiting the records displayed to those matching the CurrentBatchID
GV. That's an oops. The REAL qryBatchList has no such criteria. I
had just placed it there temporarily in a desparate effort to debug
this prob. I forgot to remove it. I've removed it now, though. Other
than being unnecessarily redundant, though, it wouldn't have been
responsible for any part of the problem I'm having. It shouldn't,
should it??? I mean, 10 nested queries, each pulling out red cars
from a list - all of them specifying red shouldn't cause anything
other than a sense of reassurance that the cars finally pulled out
of the very, very, very last query in the chain are gonna be really,
really, really red. Right?


.