OpenRecordset with select query not looping

[AndyRob1973]

Hello,

I am able to loop through a table called 'tblStudents' with the code below. Using OpenRecordset("tblStudents"). This works ok.



However ultimately, I want to use a query OpenRecordset("SELECT * FROM tblStudents") like below, but this doesn't loop through in the same way as above. I have seen it done this way in examples online. The reason is that I want to add criteria to the query once i get it working.






I've tried declaring a variable sqlstring as string and using OpenRecordset(sqlstring), but this doesn't work either.



Could anyone help?

Thankyou.

[Minty]

You probably need to specify an method of opening the record set, try

Set rs = OpenRecordset("SELECT * FROM tblStudents" , dbOpenDynaset)

Also go back to using your Sql string variable, it's much easier to read and debug.

And I would declare the type of recordset;

Dim  rs  As DAO.Recordset

[CJ_London]

what does 'but this doesn't loop through in the same way as above' mean

and why loop through your recordset when you can update specific record with an update query?

for looping you might be better using .eof.

For the future please copy/paste your code and use the code tags to preserve indentation. I'm not going to waste time retyping your code - so my example is

while not rs.EOF
    'do something
    rs.movenext
wend

[Welshgasman]

Do not bother using record counts, just use EOF.

[Welshgasman]

I would also just use a query where last name = 'Ramos' ?

[kd2017]

Agreed with CJ. I could be wrong but if i remember correctly I don't think a recorset's record count property is reliable until one has actually moved to the end of the recordset.

To expand on CJ's code here is my template (typing this from memory without any intellisense, sorry in advance for typos)

On Error GoTo ErrHandler
    
    Dim db as DAO.Database
    Dim rs as DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("MY_QUERY", dbOpenSnapshot)    'Use dbOpenDynaset if you may need to edit the values within the recordset

    If not (rs.BOF and rs.EOF) Then    'Make sure the recordset has records otherwise we'll get errors if we try to move through the recordset
        rs.MoveFirst    'this line may not be necessary but couldn't hurt
        Do While not rs.EOF   'begin looping through the recordset
            'Do Work Here
                
            rs.MoveNext    'Don't forget to move to the next record in the recordset or you'll get stuck in an infinite loop
        Loop
    Else
        'Code to run if the recordset is empty goes here

    End If
    rs.close

ExitHandler:
    Set db = Nothing    ' Clean up all of the objects that we previously set
    Set rs = Nothing

    Exit Sub

ErrHandler:
    MsgBox err.description, , "Error #" & err.Number
    Resume ExitHandler

Please review http://allenbrowne.com/ser-29.html

[CJ_London]

but if i remember correctly I don't think a recorset's record count property is reliable until one has actually moved to the end of the recordset.

True - perhaps that is what the OP meant by 'this doesn't loop through in the same way '

[AndyRob1973]

You probably need to specify an method of opening the record set, try

Set rs = OpenRecordset("SELECT * FROM tblStudents" , dbOpenDynaset)

Also go back to using your Sql string variable, it's much easier to read and debug.

And I would declare the type of recordset;

Dim  rs  As DAO.Recordset

Thank you Minty - i shall try that.

[AndyRob1973]

what does 'but this doesn't loop through in the same way as above' mean

and why loop through your recordset when you can update specific record with an update query?

for looping you might be better using .eof.


For the future please copy/paste your code and use the code tags to preserve indentation. I'm not going to waste time retyping your code - so my example is

while not rs.EOF
    'do something
    rs.movenext
wend

Thank you CJ_London. What I was trying to say is that when i put a select statement it doesn't seem to loop through the records at all, whereas when i just put the table name it does.

At the moment I'm practicing with looping through recordsets so yes in this example i could just do an update. But ultimately I want to update a field in one record based on a field in the previous record.

Also thank you for your point on pasting code. I'll take that on board.

[AndyRob1973]

I would also just use a query where last name = 'Ramos' ?

Thanks Welshgasman. Normally i would use an update query but i'm trying to get to grips and practicing with looping through recordsets before moving onto a job where I'll be using a value in a field in a previous row to update a row in the current record. I'll try your EOF suggestion too.

[AndyRob1973]

Agreed with CJ. I could be wrong but if i remember correctly I don't think a recorset's record count property is reliable until one has actually moved to the end of the recordset.

To expand on CJ's code here is my template (typing this from memory without any intellisense, sorry in advance for typos)

On Error GoTo ErrHandler
    
    Dim db as DAO.Database
    Dim rs as DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("MY_QUERY", dbOpenSnapshot)    'Use dbOpenDynaset if you may need to edit the values within the recordset

    If not (rs.BOF and rs.EOF) Then    'Make sure the recordset has records otherwise we'll get errors if we try to move through the recordset
        rs.MoveFirst    'this line may not be necessary but couldn't hurt
        Do While not rs.EOF   'begin looping through the recordset
            'Do Work Here
                
            rs.MoveNext    'Don't forget to move to the next record in the recordset or you'll get stuck in an infinite loop
        Loop
    Else
        'Code to run if the recordset is empty goes here

    End If
    rs.close

ExitHandler:
    Set db = Nothing    ' Clean up all of the objects that we previously set
    Set rs = Nothing

    Exit Sub

ErrHandler:
    MsgBox err.description, , "Error #" & err.Number
    Resume ExitHandler

Please review http://allenbrowne.com/ser-29.html

Thankyou kd2017

[CJ_London]

have you checked out post #6? Edit - crossed in posting

But ultimately I want to update a field in one record based on a field in the previous record.

You are aware there is no concept of Previous, Next, First or Last in databases unless you specify an order?

Sounds suspiciously like you are attempted to store a calculated value such as a running sum which is not recommended - all sorts of potential issues can arise. Might help if you explained what you are trying to do and why - we may be able to suggest a better way

[AndyRob1973]

have you checked out post #6? Edit - crossed in posting

You are aware there is no concept of Previous, Next, First or Last in databases unless you specify an order?

Sounds suspiciously like you are attempted to store a calculated value such as a running sum which is not recommended - all sorts of potential issues can arise. Might help if you explained what you are trying to do and why - we may be able to suggest a better way

I’ve attached an access dbase with a table in that I’m hoping helps to illustrate what I’m trying to do.




Each person on each course has a series of appointments. I would like to be able to put the date in for their first appointment and then the rest of the dates calculate using the ‘Days to next appointment’. Obviously this calculation needs to stop at their last appointment and a new calculation starts for the next person eg below.






I am aware that access doesn’t have a concept of order, so I was hoping that would be taken care of with an order by in the select statement in the OpenRecordset.

Also, I think that there needs to be a loop for each distinct Course/Name combination, within a loop that goes through all the records.
My previous attempts and posts were me trying to start simply and get to grips with the concept and then move on.
Does this make sense?

Thankyou

Andy

[NoellaG]

Hi,

this seems an attempt to search the slowest and most difficult way to update/manage data in Access. I assume you have a table with courses, one with students and then a third with appointments. Why not, on the form where you manage the appointments, put a button "new appointment" that looks up the previous appointment of that person and uses the days-to_next appointment to automatically calculate the date for the next appointment and creates that new appointment for that person?