Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ? | | |
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
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
Try:
100 Dim db As DAO.Database, rst As DAO.Recordset
MLH wrote: Quote:
>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 | | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
MLH wrote: Quote:
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() Quote:
>
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
>
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
On Sat, 12 Jul 2008 11:03:49 -0400, MLH <CRCI@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. Quote:
>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
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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: Quote:
>Try:
>100 Dim db As DAO.Database, rst As DAO.Recordset
>
>MLH wrote: Quote:
>>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
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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 Quote:
>
>Insert a line, line 150, and try
> Msgbox GetCurrentBatchID()
>Does it work?
>
>If it does, try
> WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
> Quote:
>>
>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
>>
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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.spam.tom7744@cox.netwrote: Quote:
>On Sat, 12 Jul 2008 11:03:49 -0400, MLH <CRCI@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.
>
> Quote:
>>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
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
MLH wrote: Quote:
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. Quote:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx
>
>
> Quote:
>>Insert a line, line 150, and try
>> Msgbox GetCurrentBatchID()
>>Does it work?
>>
>>If it does, try
>> WHERE qryBatchList.BatchID=" & GetCurrentBatchID()
>>
>> Quote:
>>>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
>>>
>
>
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
Any chance you have some bogus MISSING references?
MLH wrote: Quote:
>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
> Quote:
>>Insert a line, line 150, and try
>> Msgbox GetCurrentBatchID()
>[quoted text clipped - 25 lines] Quote: Quote:
>>>
>>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 | | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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.
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
Also got a 91 error
(object variable or with block variable not set)
Hmmm??? that's something new. Lemme explore...
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
MLH wrote: Quote:
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. Quote:
>
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
>
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
Well, rebuilding query from scratch did nothing to resolve issue.
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
On Sat, 12 Jul 2008 12:01:09 -0400, MLH <CRCI@NorthState.netwrote: Quote:
>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 | | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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????
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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
<bestfit@NObestfitSPAMsoftwarePLEASE.com.auwrote : Quote:
>On Sat, 12 Jul 2008 12:01:09 -0400, MLH <CRCI@NorthState.netwrote:
> Quote:
>>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
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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?
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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?
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
On Sat, 12 Jul 2008 15:46:51 GMT, "ruralguy via AccessMonster.com"
<u12102@uwewrote: Quote:
>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 | | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
On Sat, 12 Jul 2008 09:37:55 -0700, Salad <oil@vinegar.comwrote: Quote:
>MLH wrote:
> Quote:
>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. Quote:
>
>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. Quote:
>
>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? | | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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 : Quote:
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
| | | | re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
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 <johnvonc@earthlink.net>
wrote: Quote:
>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 : Quote:
>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
>
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
