Repost - Updating a field in filtered recordset

  • Thread starter Thread starter Noel
  • Start date Start date
N

Noel

Hi. I posted this in Queries but had no replies. Any
ideas here?

One of my users suggested this. Can anyone tell me if
its possible?

I have a form with a Check Box which is used to determine
which records are picked up by a Mail Merge document. The
users simply go through the records and tick the ones
they want to Merge.

This is fine for a small number of records but heres the
question. Assume the user filtered the recordset to show,
say, all records appropriate to a certain County. Say
this produces 200 records. Rather than the user having to
go through all 200 records ticking the boxes, would it be
possible to have a command button that calls up some sort
of process that automatically ticks the Check Box in each
of the records in the filtered set?

Is this possible and if so, how can it be done? Thanks,
Noel
 
Noel said:
Hi. I posted this in Queries but had no replies. Any
ideas here?

One of my users suggested this. Can anyone tell me if
its possible?

I have a form with a Check Box which is used to determine
which records are picked up by a Mail Merge document. The
users simply go through the records and tick the ones
they want to Merge.

This is fine for a small number of records but heres the
question. Assume the user filtered the recordset to show,
say, all records appropriate to a certain County. Say
this produces 200 records. Rather than the user having to
go through all 200 records ticking the boxes, would it be
possible to have a command button that calls up some sort
of process that automatically ticks the Check Box in each
of the records in the filtered set?

Is this possible and if so, how can it be done? Thanks,
Noel
Click to expand...

How about (air code):

'---- start of example code ----
Private Sub cmdSelectAll_Click()

With Me.RecordsetClone
If .RecordCount = 0 Then Exit Sub
.MoveFirst
Do Until .EOF
.Edit
!YourCheckboxField = True
.Update
.MoveNext
Loop
End With

End Sub
'---- end of example code ----
 
Thanks Dirk - that looks great. Ill have a try using this
on a sample mdb at home tomorrow. What does air code
mean? Thanks again, Noel
 
noel said:
Thanks Dirk - that looks great. Ill have a try using this
on a sample mdb at home tomorrow. What does air code
mean? Thanks again, Noel
Click to expand...

"Air code" means "completely untested code -- I just pulled this out of
the air, and didn't even check to make sure I got the syntax right".
You were warned. :-)
 
Hi again Dirk. Thanks for the clarification. Ive now tried
your code. Ive had to remove the ! before the Check Box
name to get it to work but now, although it doesnt bring
up any error messages, its only ticking the first record
in the filtered set. Can you see why this is? Before I
removed the ! I was getting the message Item Not Found in
This Collection. If it matters, my test mdb is Access 97
but my main mdb in work will be Access 2002. Thanks, Noel
 
Noel said:
Hi again Dirk. Thanks for the clarification. Ive now tried
your code. Ive had to remove the ! before the Check Box
name to get it to work but now, although it doesnt bring
up any error messages, its only ticking the first record
in the filtered set. Can you see why this is? Before I
removed the ! I was getting the message Item Not Found in
This Collection. If it matters, my test mdb is Access 97
but my main mdb in work will be Access 2002. Thanks, Noel
Click to expand...

It was a mistake to remove the bang (!) from in front of the name of the
field name. The error you were getting is because that was not the
correct field name, but the bang is required. To correct the problem,
you must put the bang back, but look at the controlsource of the check
box control on the form to identify the correct field name, then use
that field name in code instead of my example, "YourCheckboxField".
 
Ah I see. I was removing the bang and using the name of
the check box, rather than its control source. Ill give
that a go and get back to you in a while. Thanks for
sticking with me on this. Noel
 
Hi again Dirk. That got it - you were right all along.
Thanks again . It does exactly what I want. Cheers, Noel
 
Back
Top