(rs.BOF and rs.EOF = true) but Record Count = 1

[Ryan]

I've got a problem I have't run up against before. I generally test
for an empty recordset using BOF and EOF. Today, for the first time I
ran into a problem where my recordset shows BOF and EOF = true despite
the fact that there is has an actual record (record count = 1). The
count is correct, there should be one record (it shows on the form),
but it is not accessible in the recordset. Does anyone know why this
might be? The code is as follows:

txtSearch is a text box...the code snippet below runs when the user
hits FIND. The forms recordsource uses the search box as part of its
criteria (basically, find records where ID is like txtSearch:

dim RS as recordset

If IsNull(txtSearch) Then
msgbox ("Please enter a search criterium.")
txtSearch.Setfocus
Else
Me.Requery
txtSearch = Null
Set RS = Me.RecordsetClone
msgbox ("RecordSet: " & (RS.BOF And RS.EOF))
msgbox (RS.recordCount)
End If

RESULTS: msgbox1) RecordSet: TRUE
msgbox2) 1

The record will display on the form, but trying to do msgbox(RS(0))
will give an error. What is going on. The table it is searching is a
linked table to a SQL backend, but I don't think this should make a
difference since it is linked.

Thanks,
Ryan

[Allen Browne]

Hopefully you will get replies from people who use SQL Server more than I.

I've never seen this in Access/JET.
Try being explicit about the type of Recordset you are declaring, e.g.:
Dim RS As ADO.Recordset
or whichever is appropriate. With ADO recordsets, you can get a RecordCount
of -1 for undetermined.

Presumably txtSearch is unbound. It is not clear how the query is filtering:
perhaps you refer to Forms!Form1!txtSearch in the criteria of your query.

Is there a possible timing issue, where no records are loaded yet when the
1st MsgBox runs, but the record is loaded after its delay? To test that
idea, combine the 2 into one MsgBox:
strMsg = "BOF: " & RS.BOF & ". EOF: " & RS.EOF & _
". RecordCount: " & RS.RecordCount
MsgBox strMsg

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ryan" <ry************@kp.org> wrote in message
news:b5**************************@posting.google.c om...

I've got a problem I have't run up against before. I generally test
for an empty recordset using BOF and EOF. Today, for the first time I
ran into a problem where my recordset shows BOF and EOF = true despite
the fact that there is has an actual record (record count = 1). The
count is correct, there should be one record (it shows on the form),
but it is not accessible in the recordset. Does anyone know why this
might be? The code is as follows:

txtSearch is a text box...the code snippet below runs when the user
hits FIND. The forms recordsource uses the search box as part of its
criteria (basically, find records where ID is like txtSearch:

dim RS as recordset

If IsNull(txtSearch) Then
msgbox ("Please enter a search criterium.")
txtSearch.Setfocus
Else
Me.Requery
txtSearch = Null
Set RS = Me.RecordsetClone
msgbox ("RecordSet: " & (RS.BOF And RS.EOF))
msgbox (RS.recordCount)
End If

RESULTS: msgbox1) RecordSet: TRUE
msgbox2) 1

The record will display on the form, but trying to do msgbox(RS(0))
will give an error. What is going on. The table it is searching is a
linked table to a SQL backend, but I don't think this should make a
difference since it is linked.

Thanks,
Ryan