Compile Error when Recordset Declared Correctly

[CevinMoses]

I am receiving an error when I click the button that runs the code below.

"Compile error: Method or data member not found"

Here's some background. I had originally planned on using two different
recordsets within this procedure, which I declared them as rsBOL and rsPS. I
wrote the code for the first recordset, formatting and concatonating data
from it with no problem.

When I started to write the section that would deal with the second
recordset, I realized that I had made a mistake. When I wrote the first
section, I had fallen back on old habits and had called my recordset "rs"
instead of "rsBOL", which I had intended. When I went to correct my code to
the following:

Set rsBOL = db.OpenRecordset(stSQLBOL)

I got the compile error for the ".ShipDate" of the following line:

If IsNull(rs.ShipDate) Then

So, after going back and forth with it, I changed the declaration to be the
same as the code:

Dim rs As DAO.Recordset

I got the same compile error.

Here's my question: Why does the code work fine when the name for the
recordset is declared as "rsBOL", but set as "rs", but when I declare it to
be the same as the "Set" line, it fails?

Here's the section of the code that is relevant:

Private Sub EDI_Click()
On Error GoTo Err_EDI_Click

Dim stBOL as String
Dim stSQLBOL As String
Dim rsBOL As DAO.Recordset
Dim rsPS As DAO.Recordset
Dim db As DAO.Database
Dim stDate As String 'Format the date section of ShipDate
into yymmdd

Me.Dirty = False

stBOL = Me.ShippedOnBOL

stSQLBOL = "Select * FROM qEDI_BOL WHERE ShippedOnBOL = " & stBOL

Set db = CurrentDb
Set rs = db.OpenRecordset(stSQLBOL)

rs.MoveFirst

If IsNull(rs.ShipDate) Then
ShipDateResult = MsgBox("Ship Date is required", , "Incomplete
Information")
Exit Sub
Else: Debug.Print " Found Ship Date " & rs.ShipDate
End If

stDate = Format(rs.ShipDate, "yymmdd")
Debug.Print " Shipdate formatted to '" & stDate & "'"

Exit Sub

[Douglas J. Steele]

To refer to a field in the recordset, use either

If IsNull(rs!ShipDate) Then

or

If IsNull(rs.Fields("ShipDate")) Then

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"CevinMoses" <Cevin...@discussions.microsoft.com> wrote in message
news:075265A9-B8A2-4BB7...@microsoft.com...