Runtime error 3021- Either BOF or EOF is true, Unable to resolve this-VBForums
Results 1 to 12 of 12

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2007
    Posts
    19

    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.

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    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.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3
    Lively Member
    Join Date
    Mar 2007
    Posts
    83

    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

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    3,488

    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.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2007
    Posts
    19

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

  6. #6
    Fanatic Member Dnereb's Avatar
    Join Date
    Aug 2005
    Location
    Netherlands
    Posts
    848

    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.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Apr 2007
    Posts
    19

    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?

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    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

  9. #9
    Lively Member
    Join Date
    Jun 2008
    Posts
    111

    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

  10. #10
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,816

    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
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  11. #11
    Lively Member
    Join Date
    Jun 2008
    Posts
    111

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

    Quote Originally Posted by dee-u View Post
    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

  12. #12
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,816

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

    Quote Originally Posted by CVDpr View Post
    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?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.