Speed Up Loop Process

[Ember]

I have a report that OnClose the following code is set to run. Its purpose
is to check to see if the field "Print" is checked or not. If it is, I want
it unchecked. The code works fine, but takes forever as it has 5000 plus
records to loop through and that number will only grow. Is there anyway to
speed the process up?

Thanks!

Private Sub Report_Close()

strTableName = "tblFiles"
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)

With rst
Do Until .EOF
.MoveFirst
.FindFirst "[Print] = -1"
.Edit
.Fields("Print") = 0
.Update
.MoveNext
Loop
End With

rst.Close

Set db = Nothing
Set rst = Nothing

End Sub

 

[Duane Hookom]

Couldn't you run an udpate query like:

UPDATE tblFiles
SET [Print] = 0
WHERE [Print]=-1

 

[Ember]

Could this still be done in the OnClose property of the report?

Couldn't you run an udpate query like:

UPDATE tblFiles
SET [Print] = 0
WHERE [Print]=-1

--
Duane Hookom
MS Access MVP

I have a report that OnClose the following code is set to run. Its purpose
is to check to see if the field "Print" is checked or not. If it is, I
want
it unchecked. The code works fine, but takes forever as it has 5000 plus
records to loop through and that number will only grow. Is there anyway
to
speed the process up?

Thanks!

Private Sub Report_Close()

strTableName = "tblFiles"
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)

With rst
Do Until .EOF
.MoveFirst
.FindFirst "[Print] = -1"
.Edit
.Fields("Print") = 0
.Update
.MoveNext
Loop
End With

rst.Close

Set db = Nothing
Set rst = Nothing

End Sub

 

[Duane Hookom]

Try it.

Dim strSQL as String
strSQL = "UPDATE....."
DoCmd.RunSQL strSQL

After testing, you may want to SetWarnings = False then True.

--
Duane Hookom
MS Access MVP

Could this still be done in the OnClose property of the report?

Couldn't you run an udpate query like:

UPDATE tblFiles
SET [Print] = 0
WHERE [Print]=-1

--
Duane Hookom
MS Access MVP

I have a report that OnClose the following code is set to run. Its
purpose
is to check to see if the field "Print" is checked or not. If it is, I
want
it unchecked. The code works fine, but takes forever as it has 5000
plus
records to loop through and that number will only grow. Is there
anyway
to
speed the process up?

Thanks!

Private Sub Report_Close()

strTableName = "tblFiles"
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)

With rst
Do Until .EOF
.MoveFirst
.FindFirst "[Print] = -1"
.Edit
.Fields("Print") = 0
.Update
.MoveNext
Loop
End With

rst.Close

Set db = Nothing
Set rst = Nothing

End Sub

 

[Ember]

Duane - I have tried your suggestion. Now I am getting "Syntax error in
Update statement." Here is my code.

Private Sub Report_Close()
Dim strSQL As String
strSQL = "Update qryPortCheck"
DoCmd.RunSQL strSQL
End Sub

Thanks!
Ember

Try it.

Dim strSQL as String
strSQL = "UPDATE....."
DoCmd.RunSQL strSQL

After testing, you may want to SetWarnings = False then True.

--
Duane Hookom
MS Access MVP

Could this still be done in the OnClose property of the report?

Couldn't you run an udpate query like:

UPDATE tblFiles
SET [Print] = 0
WHERE [Print]=-1

--
Duane Hookom
MS Access MVP

I have a report that OnClose the following code is set to run. Its
purpose
is to check to see if the field "Print" is checked or not. If it is, I
want
it unchecked. The code works fine, but takes forever as it has 5000
plus
records to loop through and that number will only grow. Is there
anyway
to
speed the process up?

Thanks!

Private Sub Report_Close()

strTableName = "tblFiles"
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)

With rst
Do Until .EOF
.MoveFirst
.FindFirst "[Print] = -1"
.Edit
.Fields("Print") = 0
.Update
.MoveNext
Loop
End With

rst.Close

Set db = Nothing
Set rst = Nothing

End Sub

 

[Ember]

Thanks Duane, but I did more searching and found the following solution which
works great!

Private Sub Report_Close()
CurrentDb().Execute "qryProjCheck", dbFailOnError
End Sub

Ember

Try it.

Dim strSQL as String
strSQL = "UPDATE....."
DoCmd.RunSQL strSQL

After testing, you may want to SetWarnings = False then True.

--
Duane Hookom
MS Access MVP

Could this still be done in the OnClose property of the report?

Couldn't you run an udpate query like:

UPDATE tblFiles
SET [Print] = 0
WHERE [Print]=-1

--
Duane Hookom
MS Access MVP

I have a report that OnClose the following code is set to run. Its
purpose
is to check to see if the field "Print" is checked or not. If it is, I
want
it unchecked. The code works fine, but takes forever as it has 5000
plus
records to loop through and that number will only grow. Is there
anyway
to
speed the process up?

Thanks!

Private Sub Report_Close()

strTableName = "tblFiles"
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)

With rst
Do Until .EOF
.MoveFirst
.FindFirst "[Print] = -1"
.Edit
.Fields("Print") = 0
.Update
.MoveNext
Loop
End With

rst.Close

Set db = Nothing
Set rst = Nothing

End Sub