Problems with recordset.movenext

[Richard_1]

Hi,

I can't loop through a recordset with the code used below.
The first message box says there are 4 records in the recordset.
The second message box shows all 4 records in rows at once instead of one by one. I mean it don't show me the values in 4 messageboxes.
Then I get the error message "Either bof or eof is true or the current record has been deleted. requested operation requires a current record".
What is the problem? What I want is save each value in the recordset in a variable. How can I achieve that? Can anyone help a beginning VBA user?

Richard
--------------------------------
Dim rst As New ADODB.Recordset
Dim NumRecords, CurrentRecord, var

str = "SELECT tblCosts.Price FROM tblCosts WHERE (((tblCosts.Include)=-1) AND ((tblCosts.Itemno)='" & txtItem.Value & "'));"

rst.CursorLocation = adUseServer
rst.Open str, CurrentProject.Connection, 3, 3
rst.MoveFirst
NumRecords = rst.RecordCount
MsgBox NumRecords & " records."
CurrentRecord = rst.GetString
MsgBox "Current: " & CurrentRecord
Do While Not rst.BOF
var = rst.getstring
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

 

[Rabbie]

Try looking up getsring in VBA Help. You will see it returns the whole recordset not each record.

As your second msgbox is not inside your loop it will only be displayed once.

It also looks like you have the wrong test on your While as what you have written will stop as soon as you leave the beginning of the recordset.

 

[honda882000]

I am not 100% sure if this is what you are trying to do, but if you are looping down on the recordset, try replacing Do While Not rst.BOF with Do Until rst.EOF.

 

[jardiamj]

Yep yep!!
I think Honda is right. Do Until rst.EOF should do it.

 

[Richard_1]

Thank you for the replies. I've change the code as you sugested. See below. I don't get the error message anymore. But the code stops before the loop, while there are more than record in the recordset, because the first messagebox returns: "4 records".
I'm wondering if rst.getstring only returns the whole recordset, how can I let the recordset return each record and put these values in a variables?

Richard
----------------------------------
Dim rst As New ADODB.Recordset
Dim NumRecords, CurrentRecord

str = "SELECT tblCosts.Fcty FROM tblCosts WHERE (((tblCosts.Include)=-1) AND ((tblCosts.Itemno)='" & txtItem.Value & "'));"
rst.CursorLocation = adUseServer
rst.Open str, CurrentProject.Connection, 3, 3
rst.MoveFirst
NumRecords = rst.RecordCount
MsgBox NumRecords & " records."
CurrentRecord = rst.GetString
MsgBox "Current: " & CurrentRecord

Do While Not rst.EOF
MsgBox "Test"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

 

[honda882000]

To reference a specific field in the current records, you can use rst!field_name. So for example, if your field name is Fcty, then try using rst!Fcty. I think I am still confused on what you are trying to do though. To what variable are you trying to assign the record field to? Shouldn't you include the variable assignment inside the loop? For example:

Sub test()

    Dim rst As New ADODB.Recordset
    Dim NumRecords, CurrentRecord
    
    Str = "SELECT tblCosts.Fcty FROM tblCosts WHERE (((tblCosts.Include)=-1) AND ((tblCosts.Itemno)='" & txtItem.Value & "'));"
    
    With rst
        .CursorLocation = adUseServer
        .Open Str, CurrentProject.Connection, 3, 3
        .MoveFirst
        NumRecords = .RecordCount
    End With
    
    MsgBox NumRecords & " records."
    
    'Loop
    '====================
    Do While Not rst.EOF
        
        'Print message for each record:
        CurrentRecord = rst!Fcty
        MsgBox "Current: " & CurrentRecord
        
        rst.MoveNext
    Loop
    
    'Clean Variables:
    '===================
    rst.Close
    Set rst = Nothing
    
End Sub

 

[Richard_1]

Yes Honda, you're right. It's working now. I was too busy to test the loop with messageboxes that I didn't spend time to assign the values to variables. Many thanks to you and Rabbie!