Looping Through Records

[TeeSee]

I'm Using Sandra Daigles code to loop through and make a change to all
records in a table. The field in the table "Select" is a Yes/No data
type. It changes only the current record in my form. Here is the code.
Any suggestion welcomed.

Private Sub cmdDeSelect_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
'Change this to your SQL or to the name of a saved query
Set rst = db.OpenRecordset("Select * from tblCDMRvalues;")
With rst
' Very important to avoid errors if no records are returned
If Not (.EOF And .BOF) Then
'movefirst isn't strictly necessary - but just in case
.MoveFirst
Do Until .EOF
'do something with record
Me.Select.Value = 0
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing

End Sub

 

[Dirk Goldgar]

I'm Using Sandra Daigles code to loop through and make a change to all
records in a table. The field in the table "Select" is a Yes/No data
type. It changes only the current record in my form. Here is the code.
Any suggestion welcomed.

Private Sub cmdDeSelect_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
'Change this to your SQL or to the name of a saved query
Set rst = db.OpenRecordset("Select * from tblCDMRvalues;")
With rst
' Very important to avoid errors if no records are returned
If Not (.EOF And .BOF) Then
'movefirst isn't strictly necessary - but just in case
.MoveFirst
Do Until .EOF
'do something with record
Me.Select.Value = 0
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing

End Sub

The line ...

Me.Select.Value = 0

.... is acting on the current form record, not on the recordset at all. It
should probably be this:

.Edit
!Select = 0
.Update

However, if all you want to do is set the value of Select to 0 (or False)
for all records in the table, it would be much more efficient to drop the
recordset code altogether and just execute an update query on the table,
like this:

CurrentDb.Execute _
UPDATE tblCDMRvalues SET Select = 0", _
dbFailOnError

 

[TeeSee]

The line ...


... is acting on the current form record, not on the recordset at all.  It
should probably be this:

            .Edit
            !Select = 0
            .Update

However, if all you want to do is set the value of Select to 0 (or False)
for all records in the table, it would be much more efficient to drop the
recordset code altogether and just execute an update query on the table,
like this:

    CurrentDb.Execute _
        UPDATE tblCDMRvalues SET Select = 0", _
        dbFailOnError

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Thanks for the response.

.Edit

!Select = 0
.Update

Does the trick! I would like to use the "Currentdb.Execute" command
but I don't know where and or how to put that into my form. Would you
please explain for me?

Thanks again

 

[tedmi]

Place a command button on your form, with an appropriate caption.
Put the CurrentDB.Execute statement in the Click Event of the command button.