How do I Avoid Error 3021: No Current Record

[psdsen]

Please help me for the following program problem.
When I Click Next button, at the last record, it Shows --Run time error '3021': Either BOF or EOF......

Option Explicit
Dim strsql As String
Dim con_data As ADODB.Connection
Dim rs_Data As ADODB.Recordset

Private Sub Form_Load()
  Set con_data = New ADODB.Connection
  Set rs_Data = New ADODB.Recordset
  con_data.Provider = "Microsoft.jet.oledb.4.0;Data Source=" & App.Path & "\salary97.mdb"
  con_data.Open
  strsql = ("select * from employee")
  rs_Data.Open strsql, con_data, adOpenDynamic, adLockOptimistic
End Sub

Private Sub cmdnext_Click()
  If rs_Data.EOF Then
    rs_Data.MoveLast
  Else
    rs_Data.MoveNext
  End If
  txtnm.Text = rs_Data!ename
End Sub

[Stewart Ross]

The error message is quite correct (although you have not posted all of it, just the start of it!). When you are already at the end of file position (in your case when rs_Data.EOF is true) you have gone past all active records, so you cannot execute a move to the last record.

If you really want the last record to be the one displayed at end of file you will need to execute rs_Data.MoveFir st immediately before executing rs_Data.MoveLas t. However, if there are no records at all in the table the MoveFirst will also fail with the same error message. This is where testing for both EOF and BOF being true at the same time comes in - both conditions are true simultaneously if the recordset is empty.

-Stewart

[NeoPa]

Please read When Posting (VBA or SQL) Code before posting again.

[ADezii]

This actually a little tricky since this Type of Recordset does not support the AbsolutePositio n Property, and will also not give a valid RecordCount. What you can do is to Trap specific Errors, such as 3021, then move the Pointer to the Last Record and Update the Text Box:

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext
rs_Data.MoveNext
Me!txtnm = rs_Data!ename
  
Exit_cmdNext:
  Exit Sub
  
Err_cmdNext:
  Select Case Err.Number
    Case 3021, 2113
      rs_Data.MoveLast
        Me!txtnm = txtnm.Text = rs_Data!ename
    Case Else
      MsgBox Err.Description, vbExclamation, "Error in cmdNext_Click()"
  End Select
      Resume Exit_cmdNext
End Sub

[psdsen]

@Stewart
Its all for test purpose only. Please write the error free program.
My Q2: how to add item in CBONAME combobox from ENAME field using loop.

[Stewart Ross]

None of us who volunteer on Bytes can simply write a program for you - you will not learn for yourself if we do. We are happy to advise and support you with specific issues, so please feel free to post again if we can be of more assistance.

Please post new questions in a new thread. You need to provide suitable detail, and NeoPa's guidance in the sticky thread at the top of the forum will assist you with this.

-Stewart