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

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6868-loop-through-continuous-form-changing-yes.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

The code you are using will update only the current record, as you have seen.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
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"

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


"Buddy" wrote:

> 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.

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)


"Buddy" wrote in message
news:4A54E744-5892-46AA-992F-5D0D586359B8@microsoft.com...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
> End If
> Next ctlCurr
>
> DoCmd.PrintOut
>
> On the DoCmd I want to be able to print out a report I have.

Thanks Ofer, your way works much better. Again thanks.

"Ofer" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
>
> Docmd.OpenReport "ReportName"
>
> --
> The next line is only relevant to Microsoft''s web-based interface users.
> If I answered your question, please mark it as an answer. It''s useful to
> know that my answer was helpful
> HTH, good luck
>
>
> "Buddy" wrote:
>
> > I have a continuous form based on a select query. The query selects all
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
> > 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.

Thanks David,

With your code it is doing what I intended. Again, Thanks.

"david epsom dot com dot au" wrote:

> 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)
>
>
> "Buddy" wrote in message
> news:4A54E744-5892-46AA-992F-5D0D586359B8@microsoft.com...
> >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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6868
> > ctlCurr = True
> > End If
> > Next ctlCurr
> >
> > DoCmd.PrintOut
> >
> > On the DoCmd I want to be able to print out a report I have.
>
>
>