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

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?

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.

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.




The CMFNBackYet() function call is simply...
100 PVariant = DLookup("[InDate]", "tblCorrespondence",
"[VehicleJobID]=GetCurrentVehicleJobID() AND [InType]='05'")
'InType-05 is a CMFN
200 If Not IsNull(PVariant) Then CMFNBackYet = True


And the SoldYet() function call is simply...
100 PVariant = DLookup("[VSaleID]", "tblVehicleJobs",
"[VehicleJobID]=GetCurrentVehicleJobID()")
200 If Not IsNull(PVariant) Then SoldYet = True

.

Relevant Pages

  • Re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
    ... Sub Tester() ... Dim r As Recordset ... FROM tblBatches INNER JOIN (tblClusters INNER JOIN ((tblAdmin INNER ... And the SoldYet() function call is simply... ...
    (comp.databases.ms-access)
  • Re: determining if this is the first new record in a subform
    ... open the Immediate Window to see what the bad SQL statement looks like. ... sorts of errors in the subform... ... Dim rs As DAO.Recordset ... > tblProjectTypeStep.ProjectTypeID (Primary Key - Text) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Scroll through records and get match
    ... Doug Steele, Microsoft Access MVP ... "User-defined type not defined" at Dim dbs As DAO.Database. ... that I posted is a select query. ... cannot use it to open an SQL statement that you generate via code. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Too few parameters, expected n when executing SQL from VBA
    ... desired SQL statement, either, so I suggest a completely different approach ... Public Sub TestQuery(ByVal strFieldName As String) ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Scroll through records and get match
    ... "User-defined type not defined" at Dim dbs As DAO.Database. ... "Invalid argument" Set rst = dbs.OpenRecordset ... that I posted is a select query. ... cannot use it to open an SQL statement that you generate via code. ...
    (microsoft.public.access.modulesdaovba)