Runtime error 3021- Either BOF or EOF is true, Unable to resolve this

[ruvi]

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

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.

[Hack]

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.

[MintFlavor]

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

[FunkyDexter]

I'm willing to bet the problem is in cboProjects_Click with this bit of 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.

[ruvi]

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...

[Dnereb]

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?)

[ruvi]

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?

[Al42]

What is acmProjects, just before arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic executes in cboProjects_Click()?

[CVDpr]

With arsProjects
If not .BOF And not .EOF Then
.Requery
.MoveFirst
End If

[dee-u]

With arsProjects
 If not (.BOF And not .EOF) Then
  .Requery
  .MoveFirst
 End If
End With

[CVDpr]

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

[dee-u]

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?