-
Apr 4th, 2007, 06:54 AM
#1
Thread Starter
Junior Member
Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
I am getting runtime error 3021 - "Either EOF or BOF is true or the current record has been deleted....." I am using VB6 with Access 2000.
I have 2 combo boxes in a form- One for the client and the other for the projects.
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
Code:
Private Sub Form_Load()
Call SetupADO
Call SetupClients
End sub
Private Sub SetupADO()
Dim adoParm As ADODB.Parameter
'Create connection object and connect to database
Set acnChecklist = New ADODB.Connection
With acnChecklist
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\My Documents\Specifications\Checklist1.mdb;Persist Security Info=False"
.Open
.CursorLocation = adUseClient
End With
' Setup the clients command and record set
Set acmClients = New ADODB.Command
With acmClients
.ActiveConnection = acnChecklist
.CommandType = adCmdText
.CommandText = "SELECT ClientID, ClientName from tblClients"
End With
Set arsClients = Nothing
' Setup the projects command and record set
Set acmProjects = New ADODB.Command
With acmProjects
.ActiveConnection = acnChecklist
.CommandType = adCmdText
.CommandText = "SELECT ProjectID, ProjectName FROM tblProject WHERE ClientID= ?"
Set adoParm = .CreateParameter("ClientID", adNumeric, adParamInput, , 1)
.Parameters.Append adoParm
End With
End sub
'Populate the Clients Combo box
Private Sub SetupClients()
'Execute Query
Set arsClients = New ADODB.Recordset
arsClients.Open acmClients, , adOpenStatic, adLockOptimistic
' Populate combo-box with query results
cboClients.Clear
While Not arsClients.EOF
cboClients.AddItem arsClients!ClientName
arsClients.MoveNext
Wend
cboClients.Text = ""
cboProjects.Text = ""
End sub
' Handle change in client selection
Private Sub cboClients_Click()
Dim strClientName As String
Dim iClientID As Integer
' If nothing selected, then do nothing
If cboClients.Text = "" Then
Exit Sub
End If
strClientName = cboClients.Text
arsClients.Find "ClientName= '" & strClientName & "'", , adSearchForward, adBookmarkFirst
iClientID = arsClients("ClientID")
Call SetupProjects(iClientID)
End Sub
'Populate the projects combo box based on the selected client
Private Sub SetupProjects(ByRef iClientID As Integer)
Call acmProjects.Parameters.Delete("ClientID")
Dim adoParm As ADODB.Parameter
Set adoParm = acmProjects.CreateParameter("ClientID", adNumeric, adParamInput, , iClientID)
Call acmProjects.Parameters.Append(adoParm)
If arsProjects Is Nothing Then
Set arsProjects = New ADODB.Recordset
arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
Else
arsProjects.Requery
End If
cboProjects.Clear
While Not arsProjects.EOF
cboProjects.AddItem arsProjects!ProjectName
arsProjects.MoveNext
Wend
End Sub
Private Sub cboProjects_Click()
Dim strProjectName As String
Dim iProjectID As Long
If cboProjects.Text = "" Then
Exit Sub
End If
strProjectName = cboProjects.Text
If arsProjects Is Nothing Then
Set arsProjects = New ADODB.Recordset
arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
Else
arsProjects.Requery
End If
With arsProjects
If .BOF And .EOF Then
.Requery
.MoveFirst
End If
End With
arsProjects.Find "ProjectName='" & strProjectName & "'", adSearchForward, adBookmarkFirst
iProjectID = arsProjects("ProjectID") ------> Error in this line
End Sub
Hope you guys will bail me out.
-
Apr 4th, 2007, 06:57 AM
#2
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
Welcome to the forums.
If I had a dollar for everytime I've received that error message, I could retire on this very day!
I'm guessing the selected client has no associated projects listed in your database.
-
Apr 4th, 2007, 07:13 AM
#3
Lively Member
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
LOL @ Hack
ruvi,
I have been experiencing this same error a few times lately (so bad Hack didnt have a dollar each time..) while coding quite similar situations.
Sometimes, it's due to something very simple that we don't think about considering.
I don't know if u tried printing out what the line
strProjectName = cboProjects.Text
assigned to your variable strProjectName?
Try msgbox it out to see if this causing the problem.
Else, i'm sure someone more experienced here might help u out.
Good luck
-
Apr 4th, 2007, 09:38 AM
#4
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
I'm willing to bet the problem is in cboProjects_Click with this bit of code:-
Code:
With arsProjects
If .BOF And .EOF Then
.Requery
.MoveFirst
End If
If the current client has no projects BOF and EOF will be true. you then requery the database, they still have no projects so BOF and EOF are still true. When you then issue a MoveFirst it will throw the error you're seeing.
I can't really see why you're requeryingthere anyway as the previous open reqery should have given you all the data you're ever going to get. I think, if BOF and EOF are true you need to handle the fact that there is no project to be found.
-
Apr 5th, 2007, 01:13 AM
#5
Thread Starter
Junior Member
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
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.
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 5th, 2007, 01:44 AM
#6
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
What does arsProjects show if you add a watch on it, prior to executing the problem line?
(is project name as string or referenced through it's Id and thereby a number?)
why can't programmers keep and 31 Oct and 25 dec apart. Why Rating is Useful
for every question you ask provide an answer on another thread.
-
Apr 5th, 2007, 02:44 AM
#7
Thread Starter
Junior Member
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
Dnereb,
A watch on arsProject shows all the records properly,
And Projectname is shown as a string.
Why is the error occuring only when clicking on the first project in the drop down combo?
-
Apr 5th, 2007, 04:01 PM
#8
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
What is acmProjects, just before arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic executes in cboProjects_Click()?
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read.
Please Help Us To Save Ana
-
Apr 6th, 2009, 10:39 AM
#9
Lively Member
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
With arsProjects
If not .BOF And not .EOF Then
.Requery
.MoveFirst
End If
-
Apr 6th, 2009, 02:05 PM
#10
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
Code:
With arsProjects
If not (.BOF And not .EOF) Then
.Requery
.MoveFirst
End If
End With
-
Apr 6th, 2009, 04:21 PM
#11
Lively Member
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
Originally Posted by dee-u
Code:
With arsProjects
If not (.BOF And not .EOF) Then
.Requery
.MoveFirst
End If
End With
and what's the problem with this:
With arsProjects
If not .BOF And not .EOF Then
.Requery
.MoveFirst
End If
-
Apr 6th, 2009, 04:40 PM
#12
Re: Runtime error 3021- Either BOF or EOF is true, Unable to resolve this
Originally Posted by CVDpr
and what's the problem with this:
With arsProjects
If not .BOF And not .EOF Then
.Requery
.MoveFirst
End If
Did I mention anything like a problem with your code?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|