By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,089 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,089 IT Pros & Developers. It's quick & easy.

Runtime error 3021- Either BOF or EOF is reached , unable to resolve this

P: 6
I am getting runtime error 3021 - Either EOF or BOF is true or the current record has been deleted.....
I have 2 combo boxes in a form- One for the client and the other for the project.
When the form loads, the client combo box gets populated with all the clients.
When a particular client is clicked, the project combo box gets populated with all the projects corresponding to that client.
So far, so good. Now when I click a project in the project combo box, I get this runtime error.
Been stuck here for 2 days without a solution. Please help.

This is the code I have

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3.     Call SetupADO
  4.     Call SetupClients
  5.  
  6. End sub
  7.  
  8.  
  9. Private Sub SetupADO()
  10.  
  11.     Dim adoParm As ADODB.Parameter
  12. 'Create connection object and connect to database
  13.     Set acnChecklist = New ADODB.Connection
  14.  
  15.     With acnChecklist
  16.         .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\My Documents\Specifications\Checklist1.mdb;Persist Security Info=False"
  17.         .Open
  18.         .CursorLocation = adUseClient
  19.     End With
  20.  
  21.  
  22. ' Setup the clients command and record set
  23.     Set acmClients = New ADODB.Command
  24.     With acmClients
  25.         .ActiveConnection = acnChecklist
  26.         .CommandType = adCmdText
  27.         .CommandText = "SELECT ClientID, ClientName from tblClients"
  28.     End With
  29.  
  30.     Set arsClients = Nothing
  31.  
  32. ' Setup the projects command and record set
  33.     Set acmProjects = New ADODB.Command
  34.     With acmProjects
  35.         .ActiveConnection = acnChecklist
  36.         .CommandType = adCmdText
  37.         .CommandText = "SELECT ProjectID, ProjectName FROM tblProject WHERE ClientID= ?"
  38.         Set adoParm = .CreateParameter("ClientID", adNumeric, adParamInput, , 1)
  39.         .Parameters.Append adoParm
  40.     End With
  41.  
  42. End sub
  43.  
  44. Populate the Clients Combo box
  45. Private Sub SetupClients()
  46.    'Execute Query         
  47.     Set arsClients = New ADODB.Recordset
  48.     arsClients.Open acmClients, , adOpenStatic, adLockOptimistic
  49.  
  50.     ' Populate combo-box with query results
  51.     cboClients.Clear
  52.     While Not arsClients.EOF
  53.         cboClients.AddItem arsClients!ClientName
  54.         arsClients.MoveNext
  55.     Wend
  56.     cboClients.Text = ""
  57.     cboProjects.Text = ""
  58.  
  59. End sub
  60.  
  61. ' Handle change in client selection
  62. Private Sub cboClients_Click()
  63.     Dim strClientName As String
  64.     Dim iClientID As Integer
  65.  
  66.     ' If nothing selected, then do nothing
  67.     If cboClients.Text = "" Then
  68.         Exit Sub
  69.     End If
  70.  
  71.     strClientName = cboClients.Text
  72.  
  73.     arsClients.Find "ClientName= '" & strClientName & "'", , adSearchForward, adBookmarkFirst
  74.     iClientID = arsClients("ClientID")
  75.  
  76.      Call SetupProjects(iClientID)
  77.  
  78.     End Sub
  79.  
  80.  
  81. 'Populate the projects combo box based on the selected client
  82. Private Sub SetupProjects(ByRef iClientID As Integer)
  83.  
  84.     Call acmProjects.Parameters.Delete("ClientID")
  85.     Dim adoParm As ADODB.Parameter
  86.     Set adoParm = acmProjects.CreateParameter("ClientID", adNumeric, adParamInput, , iClientID)
  87.     Call acmProjects.Parameters.Append(adoParm)
  88.  
  89.     If arsProjects Is Nothing Then
  90.         Set arsProjects = New ADODB.Recordset
  91.         arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
  92.     Else
  93.        arsProjects.Requery
  94.     End If
  95.  
  96.     cboProjects.Clear
  97.     While Not arsProjects.EOF
  98.         cboProjects.AddItem arsProjects!ProjectName
  99.         arsProjects.MoveNext
  100.     Wend
  101.  
  102. End Sub
  103.  
  104.  
  105. Private Sub cboProjects_Click()
  106.     Dim strProjectName As String
  107.     Dim iProjectID As Long
  108.  
  109.     If cboProjects.Text = "" Then
  110.         Exit Sub
  111.     End If
  112.     strProjectName = cboProjects.Text
  113.     If arsProjects Is Nothing Then
  114.         Set arsProjects = New ADODB.Recordset
  115.         arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
  116.     Else
  117.         arsProjects.Requery
  118.     End If
  119.     With arsProjects
  120.     If .BOF And .EOF Then
  121.             .Requery
  122.             .MoveFirst
  123.     End If
  124.     End With
  125.  
  126.     arsProjects.Find "ProjectName='" & strProjectName & "'", adSearchForward, adBookmarkFirst
  127.     iProjectID = arsProjects("ProjectID") ------> Error in this line

End Sub

Hope u guys can bail me out.
Apr 4 '07 #1
Share this Question
Share on Google+
7 Replies


vijaydiwakar
100+
P: 579
Dear need not to post entire code here just put the code which have th error
use
Expand|Select|Wrap|Line Numbers
  1. if Rs0.eof or rs0.bof then
  2. 'msgbox "No mor records found"
  3. else
  4. ' put ur code here
  5. end if
where Rs0 is the Recordset
try it
Good Luck
Apr 4 '07 #2

Expert 5K+
P: 8,434
Have you tried stepping through the cboProjects_Click routine, checking the effects on arsProjects at every point?

Also, I find it odd how you are doing the "If BOF and EOF then ReQuery and MoveFirst" just before the end of this code. Just prior to that, you have either created or requeried arsProjects. So if this condition is true, doesn't that mean the dataset is empty? In which case, another requery doesn't seem as though it will help.
Apr 5 '07 #3

Expert 5K+
P: 8,434
Expand|Select|Wrap|Line Numbers
  1. if Rs0.eof or rs0.bof then
  2. 'msgbox "No mor records found"
  3. else
  4. ' put ur code here
  5. end if
Wouldn't this prevent your code ever being executed? Presumably we'd be starting at the beginning, so BOF would be true.
Apr 5 '07 #4

P: 6
Thanks for ur time guys.

Strangely I get the runtime error only while clicking on the first project in the combobox, and not while clicking the second or third or fourth project or any subsequent projects. Why is this so?

Also, after Recordset.requery, I am able to print all the records(projects) in
the recordset. It means that the recordset is not empty, isn't it? Then why is it that I am getting the error only while clicking on the first project in the combo box. Is it due to the parametrized SQL statement that I am using to access the projects table?
Help please...
Apr 5 '07 #5

P: 1
this is the solution to runtime r.eof or r.bof here r is the recordset
Expand|Select|Wrap|Line Numbers
  1. do until r.eof
  2.             if r.eof=true then
  3.                     r.movelast
  4.              end if
  5.       r.movenext
  6. loop
for bof

Expand|Select|Wrap|Line Numbers
  1. do until r.bof
  2.          if r.bof = true then
  3.                  r.movefirst
  4.           end if
  5.  r.moveprevious
  6. loop
by sudheer
May 22 '07 #6

ahmedtharwat19
P: 55
in code below
when i goto new record and use mouse wheel
i have a runtime error 3021

Expand|Select|Wrap|Line Numbers
  1. Public CurrentBookmark As String
  2. Public MouseWheelMoved As Boolean
  3.  
  4. Private Sub Form_Current()
  5. If MouseWheelMoved = True Then
  6. Me.Bookmark = CurrentBookmark
  7. End If
  8. MouseWheelMoved = False
  9. End Sub
  10.  
  11. Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
  12. CurrentBookmark = Me.Bookmark
  13. MouseWheelMoved = True
  14. MsgBox "cannot use mouse wheel"
  15. End Sub
  16.  
  17.  
please help?
Jun 20 '09 #7

ahmedtharwat19
P: 55
hey any one can help
i found th 70% of soulotion
but when goto new record and move up it work but when move down not work
why??
please need help??
Expand|Select|Wrap|Line Numbers
  1. Public CurrentBookmark As String
  2. Public MouseWheelMoved As Boolean
  3.  
  4. Private Sub Form_Current()
  5. on error resume next
  6. If MouseWheelMoved = True Then
  7. Me.Bookmark = CurrentBookmark
  8. End If
  9. MouseWheelMoved = False
  10. End Sub
  11.  
  12. Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
  13. on error resume next
  14. CurrentBookmark = Me.Bookmark
  15. MouseWheelMoved = True
  16. MsgBox "cannot use mouse wheel"
  17. End Sub
Jun 21 '09 #8

Post your reply

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