Empty recordset with DAO

  • Thread starter Thread starter mikebres
  • Start date Start date
M

mikebres

I have a module that is supposed to open a dynaset from a table based
on a parameter. However I get an empty recordset. Here is the section
of the code where the problem occurs:


Dim db As DAO.Database
Dim rstLetter As DAO.Recordset, rstLO As DAO.Recordset
.....
Set db = CurrentDb
Set rstLetter = db.OpenRecordset("SELECT Data.facility, Data.oper,
Data.ScanDT, Data.postnet,Data.planetcode, Data.machine, Data.idtag
FROM Data
WHERE Data.planetcode='" & MyPC & "' ORDER BY Data.ScanDT")
Set rstLO = db.OpenRecordset("tblLetterObj­ect", dbOpenDynaset)


'fill the variables
With rstLetter
.MoveFirst


The variable MYPC is passed to the subroutine. I copied the select
statement into a query and tried it, and it worked fine. So I'm at a
loss. Does anybody have an idea what I'm doing
wrong?


Thanks
Mike
 
Mike,

Assuming the Set rstLetter = etc expression is all in one line in your
code (or you would need to connect subsequent lines), the most likely
reason is variable MyPC isn't assigned the value you believe. To check,
precede the Set rstLetter = etc line with:
Debug.Print MyPC
and check the value in the immediate window.
Note also that your code assumes planetcode is a text fiel. Is this
correct? if notm remove the single quotes around the MyPC variable in
the WHERE clause.

HTH,
Nikos
 
Thank you. You got me looking at the variable and I found the problem.
It was getting the MyPC with an extra space on the end. Which of
course didn't match anything in the data. Curiously, the Trim
functions didn't remove the extra space. I had to use a left function
to get it to the right length.

Thanks
Mike
 
Back
Top