Access to SQL error

[dalet]

I'm converting an Access database to SQL and can't get this Module to run. It won't go past the Loop and if I take out Loop I get an "End With with With" error.

Function ClearPrintBook()
'Make all Printing set to = False This first part works!
Dim rst As Recordset
Dim db As Database
Set db = CurrentDb
Set rst = db.OpenRecordset("Sheet1", dbOpenDynaset, dbSeeChanges)
With rst
Do While Not .EOF
.Edit
!Printed = False
.Update
.MoveNext
Loop
End With


rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Function

[dcrake]

 
Function ClearPrintBook()
'Make all Printing set to = False This first part works!
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Sheet1", dbOpenDynaset, dbSeeChanges)

If Not rst.EOF And Not rst.BOF Then
  Do While rst.EOF
     rst.Edit
     rst("Printed") = False
     rst.Update
     rst.MoveNext
  Loop

  rst.Close
End If
Set rst = Nothing
End Function

Or you could simply do

 
DoCmd.SetWarnings False
DoCmd.RunSQL "Update Sheet1 Set Printed = False"
DoCmd.SetWarnings True

David

[dalet]

Thank you very much for the help. Now if I could understand how that fixed my problem I would be a happy man.

[dcrake]

Which option did you go for in the end?

[dalet]

Went with the first one.

[dcrake]

The major difference in the code, apart from being more asthetic, is the Do While line.

You had
Do While Not .EOF

I changed it to

Do While Rs.EOF

In your line you were saying do while not true, which is False and as you were not at the end of file .EOF = False so therefore it went straight to the End With line

David