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

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 FN> is
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.



xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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



.

Relevant Pages