Do Until Loop crashed Access

[mcupito]

I'm attempting to update a field in the table from the Max Date field in another table using VBA code. I attempted to simply use an Update query, however, I read that JET won't allow my query that gets the Max date to be utilized in an Update query. Hence, this is the solution I came up with.

When I try to run it to test the data, it only updated the first row, then crashed Access. Any ideas?

            Set db = CurrentDb
            Set rs = db.OpenRecordset("SELECT * FROM PayrollRequestTbl")
            
            If rs.BOF And rs.EOF Then
            Else
            Do Until rs.EOF
                navDate = rs!MCR_RecentNAV
                navDate = DMax("NAV_Date", "NAV_Tbl")
        
                rs.Edit
                rs!MCR_RecentNAV = navDate
                rs.Update
            Loop
            rs.Close
            Set rs = Nothing

[zmbd]

Take a careful look at your loop....
you never move to the next record.

[Jim Doherty]

Your code is staying on the first line of your recordset and remaining in an infinite loop

you need to insert.....

rs.MoveNext

as a line before the LOOP command so that the recordset can be traversed. At the moment it is not doing that.

[mcupito]

That's it! I wasn't sure if I needed the rs.MoveNext or not. Thanks, all!

[Rabbit]

You should be able to use an update query. Have you tried it?

[mcupito]

Hey, Rabbit. I did try. It wouldn't let me because I needed to bring in a query that get the Max(Date) field, which apparently isn't supported by JET.

[Rabbit]

Can you post the SQL you tried?