Loop through a continuous form changing Yes/Know Ctls. to Yes.

B

Buddy

I have a continuous form based on a select query. The query selects all
records based on a yes/no field. I want to put a button that will first print
out a saved report and then change the value of each record yes/no field to
yes.

I have tried the following but it only changes the first record:


For Each ctlCurr In Me.Controls
If TypeOf ctlCurr Is CheckBox Then
ctlCurr = True
End If
Next ctlCurr

DoCmd.PrintOut

On the DoCmd I want to be able to print out a report I have.
 
O

Ofer

The code you are using will update only the current record, as you have seen.
To update all the records use an update query

Docmd.RunSql "UPDATE TableName SET TableName.FieldName = True"

You probably need to add a criteria to the SQL, so you wont update the all
table.

And then open the report using

Docmd.OpenReport "ReportName"
 
D

david epsom dot com dot au

One way is to SAVE the current record,
use an update query to change the records,
then REQUERY to show the new values.

You have to be a bit careful doing this, because
if you update a record both from the form and
from a query, Access won't know which value to
use, and will ask you if you want to save of
discard the value you have typed, or will fail
on the update query.

The other way to do it involves more code and
is much slower, but does not have any locking
problems:

set rs = me.recordsetclone

if not rs.eof then rs.movefirst
while not rs.eof
rs.edit
rs!yesnofield = true
rs.update
rs.movenext
wend

(david)
 
Top