Checkbox in header of continuous forms

[Rich]

Hi All,

I have a form that displays a set of records from a parameter form.
Once I have this subset of records, I want to include a checkbox in the
form header to check or uncheck all records displayed for a field that
is a checkbox in each record.

I tried an update query, but it only gets the selected record. This
seems fairly easy (as MS uses this in hotmail to select/deselect
messages to delete or move), but can't seem to quite get my head around it.

Any help is greatly appreciated.

Thanks,
RJC

 

[Ken Sheridan]

The update query should be restricted by referencing the same parameters as
the form's underlying query, so you'll need to keep the dialogue form open
(it can be hidden), and update the relevant column to the value of the
unbound checkbox in your bound form by referencing it as the 'update to'
parameter.

Call the update query in the unbound check box's AfterUpdate event
procedure. You should ensure than there are no records currently locked by
your form by putting the following in the check box's AfterUpdate event
procedure:

Me.Dirty = False

before calling the update query, though in a multi-user environment another
user could have locked one of the records of course.

The bound form should automatically refresh, but if not you can explicitly
do so with:

Me.Refresh

after calling the update query.

Ken Sheridan
Stafford, England

 

[Rich]

Ken, Thanks for the reply...

My form records are filtered by a Criteria string that I create (via
code) and applied to the form when opened. I didn't see that option
when using the Open.Query command otherwise I would have used it.

Running the update Query (via the on click event of the header checkbox)
only updates the current record on the form (the one that has focus), so
it is getting to that point, but not to any other records.

I did get around it (although not the best way) by using the With
Me.Recordset command and moving the value of the header checkbox into
the record checkbox to update it.

This does what I wanted, but it isn't as "smooth" as the update query
would be. I'm not dealing with a lot of records, so it doesn't impact
the speed that much, but I am still curious as how to do this the other way.

Thanks,
Rich.

 

[Ken Sheridan]

In that case execute the SQL statement in code rather than calling a saved
update query. You can concatenate the string into the SQL statement's WHERE
clause. You can either call the RunSQL method of the DoCmd object, or the
Execute method in DAO or ADO.

Ken Sheridan
Stafford, England

 

[Rich]

I will give it a try. Thanks for the assistance.

Rich.