dpdsouza

I am using the RecordCount function to catch the EOF BOF / NO record exist error
using the following statement.

if rs.RecordCount=0 then Exit Sub

But Sometimes I find that the program still gives the EOF BOF/no record error. When I debug the statement I found that VB is counting the records at -1 through there were no records exist.

I had to modified my statement as

if rs.RecordCount <=0 then Exit Sub


I am confused how can VB count the records as -1

PLS HELP.

rgds,
Dennis.

Rezner

You need to specify the cursor position and locktype. Here's an example: If you're using DAO, then you need to move to the end of the recordset before accessing the RecordCount property.

joe77s

or u can use this, when you open your recordset

RS.Open strSQL, adpCon, adOpenKeyset, adLockOptimistic, AdCmdText

if u use adOpenforward, you need to MoveFirst and the MoveLast to know RecordCount

lebb

Sorry, joe77s, that is incorrect. If you MoveLast on an adOpenForwardOnly recordset, you then can no longer access any of the other fields. The RecordCount property of an ADO recordset (unlike DAO) will be accurate for a bookmarkable recordset, without needing to traverse it fully first. Note that server-side cursors and forward-only and dynamic (hope I got that list right) recordsets are not bookmarkable and will always return a RecordCount of -1.

dpdsouza

Hi Rezner, Joe & Icebb

Thanks a lot for taking part in the discussion. I am opening the recordset as follows.

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset
cn.Open "DSN=payroll;uid=;pwd=;"
rs1.Open "select * from acctyear where yr = '" & frmEnterSal.cmbYear.Text & "'", cn, adOpenDynamic, adLockOptimistic
rs.Open "select * from salary where yr = '" & frmEnterSal.cmbYear.Text & "'order by empno", cn, adOpenDynamic, adLockOptimistic
rs2.Open "select * from emp where status ='Active' order by empno", cn, adOpenKeyset, adLockOptimistic
rs3.Open "emp", cn, adOpenKeyset, adLockOptimistic, adCmdTable
While Not rs2.EOF
cmbEmpNo.AddItem rs2!empno
rs2.MoveNext
Wend
DisableButtons "D"
rs1.MoveLast
txtYear.Text = rs1!yr

If rs1.RecordCount = 0 Then Exit Sub
If rs2.RecordCount = 0 Then Exit Sub
If rs.RecordCount = 0 Then Exit Sub
ShowData
End Sub

Please tell me how I should have opened the recordset in the above code.

Regards,
Dennis.

lebb

Before your cn.Open statement, add the line
and change all your adOpenDynamic constants to adOpenStatic.

If you want to check to see if a given recordset is bookmarkable, and thus would return a valid recordcount, you can use
If this returns True, then you will get a valid RecordCount.

reboot

And all of this will be moot if you check for an empty recordset correctly with

If rs.BOF And rs.EOF

instead of

If rs1.RecordCount = 0

dpdsouza

Thanks friends for you valuable suggestions,

rgds,
Dennis