422,530 Members | 1,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,530 IT Pros & Developers. It's quick & easy.

How do I Avoid Error 3021: No Current Record

P: 2
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......

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim strsql As String
  3. Dim con_data As ADODB.Connection
  4. Dim rs_Data As ADODB.Recordset
  5.  
  6. Private Sub Form_Load()
  7.   Set con_data = New ADODB.Connection
  8.   Set rs_Data = New ADODB.Recordset
  9.   con_data.Provider = "Microsoft.jet.oledb.4.0;Data Source=" & App.Path & "\salary97.mdb"
  10.   con_data.Open
  11.   strsql = ("select * from employee")
  12.   rs_Data.Open strsql, con_data, adOpenDynamic, adLockOptimistic
  13. End Sub
  14.  
  15. Private Sub cmdnext_Click()
  16.   If rs_Data.EOF Then
  17.     rs_Data.MoveLast
  18.   Else
  19.     rs_Data.MoveNext
  20.   End If
  21.   txtnm.Text = rs_Data!ename
  22. End Sub
Oct 22 '11 #1
Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
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.MoveFirst immediately before executing rs_Data.MoveLast. 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
Oct 22 '11 #2

NeoPa
Expert Mod 15k+
P: 30,680
Please read When Posting (VBA or SQL) Code before posting again.
Oct 22 '11 #3

ADezii
Expert 5K+
P: 8,584
This actually a little tricky since this Type of Recordset does not support the AbsolutePosition 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdNext_Click()
  2. On Error GoTo Err_cmdNext
  3. rs_Data.MoveNext
  4. Me!txtnm = rs_Data!ename
  5.  
  6. Exit_cmdNext:
  7.   Exit Sub
  8.  
  9. Err_cmdNext:
  10.   Select Case Err.Number
  11.     Case 3021, 2113
  12.       rs_Data.MoveLast
  13.         Me!txtnm = txtnm.Text = rs_Data!ename
  14.     Case Else
  15.       MsgBox Err.Description, vbExclamation, "Error in cmdNext_Click()"
  16.   End Select
  17.       Resume Exit_cmdNext
  18. End Sub
Oct 22 '11 #4

P: 2
@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.
Oct 23 '11 #5

Expert Mod 2.5K+
P: 2,545
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
Oct 23 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.