100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
22  10239 
Try:
100 Dim db As DAO.Database, rst As DAO.Recordset
MLH wrote:
>100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x
--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via http://www.accessmonster.com
MLH wrote:
100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
Insert a line, line 150, and try
Msgbox GetCurrentBatchID()
Does it work?
If it does, try
WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
>
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sat, 12 Jul 2008 11:03:49 -0400, MLH <CR**@NorthState.netwrote:
I would write this as:
PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID
FROM qryBatchList WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
Also it can't hurt to be specific about DAO (although that's not
likely the problem here; just good programming practice):
Dim db As DAO.Database
Dim rst As DAO.Recordset
-Tom.
>100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x
OK... trying this ...
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)
.... results were the same. The DAO. thingie didn't rectify problem.
Still tells me some perfectly good FN is undefined.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx
On Sat, 12 Jul 2008 15:11:50 GMT, "ruralguy via AccessMonster.com"
<u12102@uwewrote:
>Try:
100 Dim db As DAO.Database, rst As DAO.Recordset
MLH wrote:
>>100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx
OK. Here's what I did ...
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)
The line #150 thingie worked. MsgBox displayed CORRECT Batch ID.
But the modification to line #140 you suggested did not make the prob
go away. Still pukes saying GetCRLF() <or some other FNis
undefined.
Poop!
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx
>
Insert a line, line 150, and try
Msgbox GetCurrentBatchID()
Does it work?
If it does, try
WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
>>
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x
OK. Will follow your lead on the DAO-dot thingie.
But I've tried this...
PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID FROM
qryBatchList WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
.... and that didn't resolve issue. Maybe I should restart Access 97
and revisit this from a clean launch. I am understanding from each
of you contributing to this thread that ...
db.OpenRecordset("SQL STRING HERE", dbOpenDynaset)
.... is viable and that, fundamentally, the syntax structure is OK.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sat, 12 Jul 2008 08:25:34 -0700, Tom van Stiphout
<no*************@cox.netwrote:
>On Sat, 12 Jul 2008 11:03:49 -0400, MLH <CR**@NorthState.netwrote:
I would write this as:
PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID
FROM qryBatchList WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
Also it can't hurt to be specific about DAO (although that's not
likely the problem here; just good programming practice):
Dim db As DAO.Database
Dim rst As DAO.Recordset
-Tom.
>>100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx
MLH wrote:
OK. Here's what I did ...
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)
The line #150 thingie worked. MsgBox displayed CORRECT Batch ID.
But the modification to line #140 you suggested did not make the prob
go away. Still pukes saying GetCRLF() <or some other FNis
undefined.
Poop!
What about the query qryBatchList? Can you open it? Paste into a code
module and run.
Sub Test
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
Are you by any chance using A97? If so, open a module and compile all
modules as well. You might have something out there, even unrelated,
that's messing things up.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx
>>Insert a line, line 150, and try
Msgbox GetCurrentBatchID()
Does it work?
If it does, try
WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
>>>It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x
Any chance you have some bogus MISSING references?
MLH wrote:
>OK. Here's what I did ...
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)
The line #150 thingie worked. MsgBox displayed CORRECT Batch ID.
But the modification to line #140 you suggested did not make the prob
go away. Still pukes saying GetCRLF() <or some other FNis
undefined.
Poop!
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxx
>>Insert a line, line 150, and try
Msgbox GetCurrentBatchID()
[quoted text clipped - 25 lines]
>>>
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx
--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.
Message posted via http://www.accessmonster.com
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();
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
God! I hope not. But there's always that chance.
Been on this app for years. Just introduced the
new query qryBatchList today and the couple of
FN's it calls are also new 2-liners. They work fine
called from immediate window.
Also got a 91 error
(object variable or with block variable not set)
Hmmm??? that's something new. Lemme explore...
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
Well, rebuilding query from scratch did nothing to resolve issue.
On Sat, 12 Jul 2008 12:01:09 -0400, MLH <CR**@NorthState.netwrote:
>God! I hope not. But there's always that chance.
Been on this app for years. Just introduced the
new query qryBatchList today and the couple of
FN's it calls are also new 2-liners. They work fine
called from immediate window.
Did you check the references?
Wayne Gillespie
Gosford NSW Australia
Shutting Access down, relaunching & retrying your suggestion...
Subsequent runs of TESTER now do NOT result in the error.
So I must be getting somewhere. But runs of my own code
locks Access into an incessant loop of reading data. Pressing
CTRL-ALT-DEL, MsAccess.exe & END PROCESS was my
way out. Hmmmm????
Yes, I did. Strangely enough, one is missing. Here's
what I generally have marked & set...
Visual Basic For Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Microsoft Calendar Control 8.0
But I noticed that Microsoft Calendar Control 8.0 is no longer
an option in the list. Bummer. I don't think that's the problem,
though. Any ideas???
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sat, 12 Jul 2008 18:23:26 GMT, Wayne Gillespie
<be*****@NObestfitSPAMsoftwarePLEASE.com.auwrote :
>On Sat, 12 Jul 2008 12:01:09 -0400, MLH <CR**@NorthState.netwrote:
>>God! I hope not. But there's always that chance.
Been on this app for years. Just introduced the
new query qryBatchList today and the couple of
FN's it calls are also new 2-liners. They work fine
called from immediate window.
Did you check the references?
Wayne Gillespie
Gosford NSW Australia
Wayne, if perchance Microsoft Calendar Control 8.0
gone missing is the problem - how would I get it back?
And, this unusual symptom I'm experiencing, would it
not likely rear it's ugly head elsewhere?
C:\Program Files\Common Files\Microsoft Shared\VBA>dir *.dll
Volume in drive C is HP_PAVILION
Volume Serial Number is 106B-85DC
Directory of C:\Program Files\Common Files\Microsoft Shared\VBA
07/11/1997 01:00 AM 499,712 MRT7ENU.DLL
12/12/1996 01:00 AM 3,661,072 MSO97RT.DLL
07/11/1997 01:00 AM 1,609,728 VBA332.DLL
07/11/1997 01:00 AM 159,744 VBA3EN.DLL
07/11/1997 01:00 AM 466,944 VBACV10.DLL
07/11/1997 01:00 AM 1,044,480 VBACV20.DLL
07/11/1997 01:00 AM 757,760 VBE.DLL
7 File(s) 8,199,440 bytes
0 Dir(s) 2,074,509,312 bytes free
C:\Program Files\Common Files\Microsoft Shared\VBA>
I can see from the above CMD window snippet that VBA332.DLL
is on my box. Is that the library that makes Microsoft Calendar
Control 8.0 available to me?
On Sat, 12 Jul 2008 15:46:51 GMT, "ruralguy via AccessMonster.com"
<u12102@uwewrote:
>Any chance you have some bogus MISSING references?
This one's no longer in the list. I recall having
it in there & placing a checkmark beside it...
Microsoft Calendar Control 8.0
On Sat, 12 Jul 2008 09:37:55 -0700, Salad <oi*@vinegar.comwrote:
>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
gasshole 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?
This may be a silly question, but what is GetCurrentBatchID()
returning? If it is numeric, a number of the suggested sql strings
should be working. If it's returning a string, it needs to have quotes
around it. Something like:
.... where qryBatchList.BatchID='" & GetCurrentBatchID() & "'"
Good luck!
MLH wrote on 7/12/2008 :
100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Damn! That was it. I screwed the pooch. I completely forgot setting it
up as a string. This is one case where the error messages were
directing me everywhere else but toward the problem. Had I gone
back to basics and checked everything from the bottom up, I might
have found it. Many thanks for the 'heads up' on that. I am quite
glad to be operational again.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxx
On Sat, 12 Jul 2008 16:27:02 -0700, JvC <jo******@earthlink.net>
wrote:
>This may be a silly question, but what is GetCurrentBatchID()
returning? If it is numeric, a number of the suggested sql strings
should be working. If it's returning a string, it needs to have quotes
around it. Something like:
... where qryBatchList.BatchID='" & GetCurrentBatchID() & "'"
Good luck!
MLH wrote on 7/12/2008 :
>100 Dim db As Database, rst As 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)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
function. The FN's it pukes on are all known good.
So I tried modifying the saved query so I wouldn't have to
specify a WHERE clause. I rewrote line #160 to look like this:
160 Set rst = db.OpenRecordset("qryBatchList", dbOpenDynaset)
Puked again - this time reporting a DIFFERENT FN was undefined.
Again, the reported FN is a known good working FN I've used
for years.
So I made a table out of the records returned by qryBatchList.
Then I rewrote line #160 to reference the table rather than a
saved query.
160 Set rst = db.OpenRecordset("tblQueryOutput", dbOpenDynaset)
This worked. No error returned saying some FN was undefined.
What is it exactly that I am not understanding here. I thought
db.OpenRecordSet("blah blah blah", dbOpenDynaset) was good syntax
as long as blah blah blah was understandable SQL. I know I"m
missing something. I just don't know what it is.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: MLH |
last post by:
I copied the following code right out of MS Access 2.0
HELP under Index Property Setting example...
Sub Button0_Click ()
Dim MyDB As Database, MyTable As Recordset
Set MyDB =...
|
by: TD |
last post by:
I created a query in Access 2000 that runs perfectly. I then copied the
"sql" version of the same query and set it equal the variable "sql" in the
code below. When I run the code below I get an...
|
by: MLH |
last post by:
A runtime error 13 (type mismatch) occurs running the following line:
Set rst = dbs.OpenRecordset("tblMyTable", dbOpenTable)
What would be the most likely place I would find the source of the...
|
by: royaltiger |
last post by:
I am trying to copy the inventory database in Building Access Applications
by John L Viescas but when i try to run the database i get an error in the
orders form when i click on the allocate...
|
by: Rico |
last post by:
Hello,
I have an application that I'm converting to Access 2003 and SQL Server 2005
Express. The application uses extensive use of DAO and the SEEK method on
indexes. I'm having an issue when...
|
by: MLH |
last post by:
Am having trouble with the filter property setting below.
Would like to filter the listing to car makes beginning
with "D". I'm blowing it on the filter spec somehow???
Sub OpenRecordsetX()
...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |
