472,955 Members | 2,299 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,955 software developers and data experts.

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

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()
  3.     Call SetupADO
  4.     Call SetupClients
  6. End sub
  9. Private Sub SetupADO()
  11.     Dim adoParm As ADODB.Parameter
  12. 'Create connection object and connect to database
  13.     Set acnChecklist = New ADODB.Connection
  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
  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
  30.     Set arsClients = Nothing
  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
  42. End sub
  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
  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 = ""
  59. End sub
  61. ' Handle change in client selection
  62. Private Sub cboClients_Click()
  63.     Dim strClientName As String
  64.     Dim iClientID As Integer
  66.     ' If nothing selected, then do nothing
  67.     If cboClients.Text = "" Then
  68.         Exit Sub
  69.     End If
  71.     strClientName = cboClients.Text
  73.     arsClients.Find "ClientName= '" & strClientName & "'", , adSearchForward, adBookmarkFirst
  74.     iClientID = arsClients("ClientID")
  76.      Call SetupProjects(iClientID)
  78.     End Sub
  81. 'Populate the projects combo box based on the selected client
  82. Private Sub SetupProjects(ByRef iClientID As Integer)
  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)
  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
  96.     cboProjects.Clear
  97.     While Not arsProjects.EOF
  98.         cboProjects.AddItem arsProjects!ProjectName
  99.         arsProjects.MoveNext
  100.     Wend
  102. End Sub
  105. Private Sub cboProjects_Click()
  106.     Dim strProjectName As String
  107.     Dim iProjectID As Long
  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
  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
7 31331
579 512MB
Dear need not to post entire code here just put the code which have th error
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
8,435 Expert 8TB
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
8,435 Expert 8TB
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
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
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
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
  4. Private Sub Form_Current()
  5. If MouseWheelMoved = True Then
  6. Me.Bookmark = CurrentBookmark
  7. End If
  8. MouseWheelMoved = False
  9. End Sub
  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
please help?
Jun 20 '09 #7
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
please need help??
Expand|Select|Wrap|Line Numbers
  1. Public CurrentBookmark As String
  2. Public MouseWheelMoved As Boolean
  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
  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

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

Similar topics

by: Polly | last post by:
I'm trying to write the results of a query, a name, ID number, and date out to a notepad .txt file to print on a "legacy" printer. I get the output from the first 2 "write" lines over the...
by: Jamey | last post by:
I perused old posts for an answer to this for at least an hour, and I've found a work-around, but no definitive answer. Synopsis of the problem: On NotInList or ctl.Requery commands where a...
by: jwa6 | last post by:
I get an runtime error w/ this code If Me!Obj = 5100 Then runtime error 13 type mismatch what is this testing for? IE: 5100 jim
by: Aleksandar | last post by:
Hi, I need to convert set of Java classes exported from IBM Modeling environment to C# for implementation. When I invoke JCLA conversion from File->Open->Convert in Visual Studio 2005 it starts...
by: darrel | last post by:
Hi there, can someone tell me what is wrong with my code am getting a run time error 3021: Here my code: If rs.State = adStateOpen Then rs.Close rs.Open "Select * from where ID like...
by: jmarcrum | last post by:
i need some help i have a table with 5 city divisions 1 = D1 2 = D2 3 = D3 4 = D4 and
by: bssandeshbs | last post by:
I am developing a Address Book Database Project using Visual Basic 6... When i click the delete button the data does'nt get deleted in the FrontEnd ..But it gets deleted in Database when we see in...
by: rudivs | last post by:
I would like to do data validation in the BeforeUpdate procedure, but Access gives me a runtime error when I try to do this: An example of what I am trying to do is as follows: Private Sub...
by: Ferwayne Yalung | last post by:
The code works when I logged in as a admin, teacher or student.But when i try an unregistered username, runtime error 3021 (either BOF or EOF is true, or the current record has been deleted....
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.