Looping Through Records

T

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
 
D

Dirk Goldgar

TeeSee said:
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
 
T

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top