469,267 Members | 1,069 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

How do I Avoid Error 3021: No Current Record

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
  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
  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
5 24544
Stewart Ross
2,545 Expert Mod 2GB
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.

Oct 22 '11 #2
32,171 Expert Mod 16PB
Please read When Posting (VBA or SQL) Code before posting again.
Oct 22 '11 #3
8,800 Expert 8TB
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
  6. Exit_cmdNext:
  7.   Exit Sub
  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
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
Stewart Ross
2,545 Expert Mod 2GB
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.

Oct 23 '11 #6

Post your reply

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

Similar topics

1 post views Thread by Colin Graham | last post: by
9 posts views Thread by Robert Wing | last post: by
2 posts views Thread by D-Zyl | last post: by
1 post views Thread by ekawj | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.