Recordset EOF is True, but there are records.

[D.W. Schingenga]

I am opening a recordset and want to run through the records to check for a specific value. Problem is I open the recordset (use ADO), Move to the first record (display the record to see if there is something in it) then go into the loop with DO UNTIL rst.EOF..., however this loop is not ran, because rst.EOF is TRUE!?

Although there is one record, still EOF is TRUE. I have not any idea what can cause this or how to resolve.

Appreciate you help.

Dirk

 

[Mile-O]

Be careful with which loop structure you choose.

Do While Not rst.EOF
    ' code
Loop

 

[D.W. Schingenga]

Thanks for your anwers, however does not work either. It seems that if this recordset has one record the EOF is true. However if the recordset has two or more records, the loop runs, however the first record is not checked in my equation, only from record two and further is visible.

Another strange thing here: After opening the recordset I start with If Not (rst.BOF and rst.EOF) Then ..., to check if the recordset is not empty. I know there is one record in it, however rst.BOF is False but rst.EOF is TRUE.

Hope this helps to find a solution.

Dirk

 

[Mile-O]

To check if there's not records you should use:

If rs.BOF And rs.EOF Then

Can you post your complete code?

 

[modest]

    rs.MoveLast
    rs.MoveFirst
    
    If rs.BOF And rs.EOF
        'if no records were returned do this (normally a goto)
    End if

    While Not rs.EOF
        'do stuff
        rs.MoveNext
    Loop

.MoveLast ==> ensures all records are loaded before continuing
.MoveFirst ==> points to the first record of the set
.MoveNext ==> increments to the next record of the recordset

 

[D.W. Schingenga]

I use this Sub to fill a listbox. But only those records must be in, which are not chosen already (present in the DocKeuze recordset). I thought that this was a nice solution. The problem is that the Dbase is SQL Server, and this Dbase does not know the NOT IN command, otherwise I could do it with one SELECT statement.

I hope you 'll find the problem with the EOF is True.

Thanks in advance.

Dirk.

Sub VulLijstBeschikbareDoc()
'*********************************************************
'Date   : 22 juni 2005
'Input  :
'Output :
'Comment: Deze procedure vult de lijst met beschikbare documenten.
'*********************************************************
    Dim rstDoc As ADODB.Recordset
    Dim rstDocKeuze As ADODB.Recordset
    Dim sSQLDoc, sSQLDocKeuze As String
    Dim i As Integer
    
    i = 0
    Set rstDoc = New ADODB.Recordset
    Set rstDocKeuze = New ADODB.Recordset
    
    sSQLDocKeuze = "SELECT tbl_IA_Doc_Keuzes.strKeuze FROM tbl_IA_Doc_Keuzes WHERE (tbl_IA_Doc_Keuzes.Volgnummer = " & Me.txtVolgnummer & ") AND (tbl_IA_Doc_Keuzes.Vertegenwoordiger = " & Me.Vertegenwoordiger & ") ORDER BY tbl_IA_Doc_Keuzes.strKeuze "
    rstDocKeuze.Open sSQLDocKeuze, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    sSQLDoc = "SELECT tbl_ER_Documentatie.strDocumentatie, tbl_ER_Documentatie.lngVolgorde FROM tbl_ER_Documentatie ORDER BY tbl_ER_Documentatie.strDocumentatie"
    rstDoc.Open sSQLDoc, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    
    rstDoc.MoveFirst
        
    If Not (rstDocKeuze.BOF And rstDocKeuze.EOF) Then
        MsgBox "The IF was TRUE, compare recordsets"
        Do While Not rstDoc.EOF
                rstDocKeuze.MoveFirst
                'MsgBox (rstDocKeuze!strKeuze)
                Do While Not rstDocKeuze.EOF
                    If rstDoc![strDocumentatie] = rstDocKeuze![strKeuze] Then
                        i = 1
                    End If
                    rstDocKeuze.MoveNext
                Loop
                If i = 0 Then
                    Me.lstBeschikbareDoc.AddItem rstDoc![strDocumentatie]
                Else
                    i = 0
                End If
                rstDoc.MoveNext
        Loop
    Else
        MsgBox "The IF was FALSE, show complete list"
        Do While Not rstDoc.EOF
            Me.lstBeschikbareDoc.AddItem rstDoc![strDocumentatie]
            rstDoc.MoveNext
        Loop
    End If
    
    rstDoc.Close
    rstDocKeuze.Close
    Set rstDoc = Nothing
    Set rstDocKeuze = Nothing
    
End Sub

 

[namliam]

Greetz from amsterdam

You should be able to use a Outerjoin construction on this instead of using the code. I am not exactly "up to speed" on SQLServer, but I know it does do that.

Do you know what an outerjoin is?