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

ExpandCollapse

Guest

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

ExpandCollapse

Guest

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"

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

ExpandCollapse

Guest

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" <Buddy@discussions.microsoft.com> 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
> End If
> Next ctlCurr
>
> DoCmd.PrintOut
>
> On the DoCmd I want to be able to print out a report I have.

ExpandCollapse

Guest

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

ExpandCollapse

Guest

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" <Buddy@discussions.microsoft.com> 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
> > End If
> > Next ctlCurr
> >
> > DoCmd.PrintOut
> >
> > On the DoCmd I want to be able to print out a report I have.
>
>
>

Share This Page

The first-ever tribute to the brave 130,000 Indian heroes set to be unveiled on Sunday. A unique memorial is set to be unveiled in the heart of the Midlands in memory of the tens of thousands of Sikh soldiers who fought side by side with British soldiers 100 years ago in the Great War....

About Us

Our community has been around for many years and pride ourselves on offering unbiased, critical discussion among people of all different backgrounds. We are working every day to make sure our community is one of the best.

Like us on Facebook

Support SPN

The management works very hard to make sure the community is running the best software, best designs, and all the other bells and whistles. We'd really appreciate your support!