"Select All" control in continuous form

[SteveS]

Hi all,

I have a continuous form which users can input the name of an activity and
select or de-select an associated checkbox depending on whether they want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer, which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All" button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the continuous
form. How can I do this (keeping in mind I'm relatively new to Access...)

Thanks in advance!

 

[Carl Rapson]

Hi all,

I have a continuous form which users can input the name of an activity and
select or de-select an associated checkbox depending on whether they want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer, which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the continuous
form. How can I do this (keeping in mind I'm relatively new to Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event) to
update the data in the underlying table, then re-query the form.

Carl Rapson

 

[SteveS]

Hi all,

I have a continuous form which users can input the name of an activity and
select or de-select an associated checkbox depending on whether they want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer, which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the continuous
form. How can I do this (keeping in mind I'm relatively new to Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event) to
update the data in the underlying table, then re-query the form.

Carl Rapson

OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Steve]

The current record in a continuous form is the only record you can change.
All other records you see only display the records in the recordsource. If
you want to change all the records you see in a continuous form, you must
change what is recorded in the form's recordsource and then requery the
form. To accomplish what you want, the code for your button must execute an
update query on the table that contains the field, chkboxTrackResults, and
then requery the form.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

 

[Guy]

Steve,

This is a quick example that should work, although I haven't tested it. Each
time the users clicks the button it will either select or deselect all your
records. Note. the gotcha with this code is that you will undoubtedly need
to set the initial select status of blnSelect to the correct value based on
your current recordset value(s) before the user clicks the button.
Otherwise the first time the user clicks the button it will select all (when
all records may have already been selected). In which case remove the Static
blnSelect, perhaps making it a module level variable that you set somewhere,
the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

Hi all,

I have a continuous form which users can input the name of an activity
and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson

OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Guy]

Steve,

I should also mention that if you currently have a mixture of checked and
unchecked values on your continous form then you can either:

1) Take your pick as to whether all records a Checked or Unchecked when the
button is first clicked
Or
2) Be really tricky and toggle (reverse) the status of each
Checked/Unchecked item depending on it's Current status.

The code I've provided may need modifying in these instances to achieve your
objective, but hopefully this gives you a starting point.

Cheers
Guy

Hi all,

I have a continuous form which users can input the name of an activity
and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson

OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Steve]

Yes, looks like it should work. However, an Update query is much faster than
cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

This is a quick example that should work, although I haven't tested it.
Each time the users clicks the button it will either select or deselect
all your records. Note. the gotcha with this code is that you will
undoubtedly need to set the initial select status of blnSelect to the
correct value based on your current recordset value(s) before the user
clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case remove
the Static blnSelect, perhaps making it a module level variable that you
set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

Hi all,

I have a continuous form which users can input the name of an activity
and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson

OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Guy]

Steve,

There are many ways to skin the cat.

If you believe performance will be a factor, then here is an alternative
solution using a parameterised Update query.

Private Sub cmdSelect_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set db = CurrentDb
Set qdf = db.QueryDefs("qupdSelectAll")
qdf.Parameters("PrimaryKey") = Forms!txtPrimaryKey ' Note. may require
more parameters if a composite key.
qdf.Parameters("Status") = blnSelect
qdf.Execute
Set qdf = Nothing
Me.Refresh
End Sub

As you can see the basic design has changed only the method of updating the
records.

Hope this helps
Guy

Yes, looks like it should work. However, an Update query is much faster
than cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

This is a quick example that should work, although I haven't tested it.
Each time the users clicks the button it will either select or deselect
all your records. Note. the gotcha with this code is that you will
undoubtedly need to set the initial select status of blnSelect to the
correct value based on your current recordset value(s) before the user
clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case remove
the Static blnSelect, perhaps making it a module level variable that you
set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

:

Hi all,

I have a continuous form which users can input the name of an
activity and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Guy]

Typo, sorry meant to say... As you can see the basic design hasn't changed
only the method of updating the
records.

Guy

Steve,

There are many ways to skin the cat.

If you believe performance will be a factor, then here is an alternative
solution using a parameterised Update query.

Private Sub cmdSelect_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set db = CurrentDb
Set qdf = db.QueryDefs("qupdSelectAll")
qdf.Parameters("PrimaryKey") = Forms!txtPrimaryKey ' Note. may require
more parameters if a composite key.
qdf.Parameters("Status") = blnSelect
qdf.Execute
Set qdf = Nothing
Me.Refresh
End Sub

As you can see the basic design has changed only the method of updating
the records.

Hope this helps
Guy

Yes, looks like it should work. However, an Update query is much faster
than cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

This is a quick example that should work, although I haven't tested it.
Each time the users clicks the button it will either select or deselect
all your records. Note. the gotcha with this code is that you will
undoubtedly need to set the initial select status of blnSelect to the
correct value based on your current recordset value(s) before the user
clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case remove
the Static blnSelect, perhaps making it a module level variable that you
set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

:

Hi all,

I have a continuous form which users can input the name of an
activity and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within
the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click
event) to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to
get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to
be
something like (need help with the parts in <>, which is most of
it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Guy]

In fact a few typo's in my example...

Depending on the structure of your data you may want to specify:

1) The foreign key (not the primary key)
2) No key to update all records in the table.

Forms!txtPrimary key should be me!txtPrimary key or something/nothing else
(see above)

and also

Set db = Nothing

Before exiting.

Cheers
Guy

Steve,

There are many ways to skin the cat.

If you believe performance will be a factor, then here is an alternative
solution using a parameterised Update query.

Private Sub cmdSelect_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set db = CurrentDb
Set qdf = db.QueryDefs("qupdSelectAll")
qdf.Parameters("PrimaryKey") = Forms!txtPrimaryKey ' Note. may require
more parameters if a composite key.
qdf.Parameters("Status") = blnSelect
qdf.Execute
Set qdf = Nothing
Me.Refresh
End Sub

As you can see the basic design has changed only the method of updating
the records.

Hope this helps
Guy

Yes, looks like it should work. However, an Update query is much faster
than cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

This is a quick example that should work, although I haven't tested it.
Each time the users clicks the button it will either select or deselect
all your records. Note. the gotcha with this code is that you will
undoubtedly need to set the initial select status of blnSelect to the
correct value based on your current recordset value(s) before the user
clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case remove
the Static blnSelect, perhaps making it a module level variable that you
set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

:

Hi all,

I have a continuous form which users can input the name of an
activity and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within
the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click
event) to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to
get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to
be
something like (need help with the parts in <>, which is most of
it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Guy]

Sorry Steve from pcdatasheet.com, got you mixed up with SteveS.

However, ultimately it is up to SteveS to decide which solution is most
appropriate, as I am sure you will agree there are still other solutions to
those we have quickly proposed.

Cheers
Guy

Yes, looks like it should work. However, an Update query is much faster
than cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

This is a quick example that should work, although I haven't tested it.
Each time the users clicks the button it will either select or deselect
all your records. Note. the gotcha with this code is that you will
undoubtedly need to set the initial select status of blnSelect to the
correct value based on your current recordset value(s) before the user
clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case remove
the Static blnSelect, perhaps making it a module level variable that you
set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

:

Hi all,

I have a continuous form which users can input the name of an
activity and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Guy]

Sorry Steve from PC datasheet.com but thought your posting was from SteveS.

Yes, looks like it should work. However, an Update query is much faster
than cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

This is a quick example that should work, although I haven't tested it.
Each time the users clicks the button it will either select or deselect
all your records. Note. the gotcha with this code is that you will
undoubtedly need to set the initial select status of blnSelect to the
correct value based on your current recordset value(s) before the user
clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case remove
the Static blnSelect, perhaps making it a module level variable that you
set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

:

Hi all,

I have a continuous form which users can input the name of an
activity and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Guy]

Hi SteveS,

Apologies if my previous postings appear slightly confusing...

All posting's starting with "Hi Steve" were directed at your good self
"SteveS", and it was only sometime later that I realised Steve from PC
datasheet.com believed they were directed at him. Yes I realise now that I
should have started with "Hi SteveS", I hope you accept that this was an
honest mistake.

Accordingly apologies Steve from PC datasheet.com if the phrasing of my
postings appeared somewhat teacher to pupil style, and therefore potentially
offensive to you as you obviously have considerable Access knowledge, but
they were directed at SteveS, and I was trying to keep it simple, explaining
not just the code, but the thought processes behind developing an algorithm
to achieve his objectives.

In regard to the solutions proposed... generally it is my experience that
for this type of feature the number of records involved is limited (< 50)
because you wouldn't expect the user to wade through a list of 100's/1000's
of records, making a decision about the status of each and everyone of them
on a regular basis due to the increased margin for user error.

If this is the case then I believe the first solution will be more than
adequate for your needs.

If this is not the case then I would consider rethinking the user interface
to reduce a potentially onerous and error prone user task Perhaps by
limiting and grouping the records in such away that the user can quickly
make a decision about their status and therefore use the Select All/Deselect
All feature.

Just my 2 cents worth.
Guy

In fact a few typo's in my example...

Depending on the structure of your data you may want to specify:

1) The foreign key (not the primary key)
2) No key to update all records in the table.

Forms!txtPrimary key should be me!txtPrimary key or something/nothing else
(see above)

and also

Set db = Nothing

Before exiting.

Cheers
Guy

Steve,

There are many ways to skin the cat.

If you believe performance will be a factor, then here is an alternative
solution using a parameterised Update query.

Private Sub cmdSelect_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set db = CurrentDb
Set qdf = db.QueryDefs("qupdSelectAll")
qdf.Parameters("PrimaryKey") = Forms!txtPrimaryKey ' Note. may require
more parameters if a composite key.
qdf.Parameters("Status") = blnSelect
qdf.Execute
Set qdf = Nothing
Me.Refresh
End Sub

As you can see the basic design has changed only the method of updating
the records.

Hope this helps
Guy

Yes, looks like it should work. However, an Update query is much faster
than cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]




Steve,

This is a quick example that should work, although I haven't tested it.
Each time the users clicks the button it will either select or deselect
all your records. Note. the gotcha with this code is that you will
undoubtedly need to set the initial select status of blnSelect to the
correct value based on your current recordset value(s) before the user
clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case remove
the Static blnSelect, perhaps making it a module level variable that
you set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

:

Hi all,

I have a continuous form which users can input the name of an
activity and
select or de-select an associated checkbox depending on whether
they want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within
the
continuous form.

I've tried the following (rather simplistic) code for a "Select
All"
button,
but it only works on a single record (the last one that was
active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click
event) to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to
get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to
be
something like (need help with the parts in <>, which is most of
it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Steve]

<<..... and therefore potentially offensive to you as you ....>>
No offense was taken! I realized that there was confusion from the start. It
was very courteous of you to offer apologies!!!!

Thanks,

Steve
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Hi SteveS,

Apologies if my previous postings appear slightly confusing...

All posting's starting with "Hi Steve" were directed at your good self
"SteveS", and it was only sometime later that I realised Steve from PC
datasheet.com believed they were directed at him. Yes I realise now that I
should have started with "Hi SteveS", I hope you accept that this was an
honest mistake.

Accordingly apologies Steve from PC datasheet.com if the phrasing of my
postings appeared somewhat teacher to pupil style, and therefore
potentially offensive to you as you obviously have considerable Access
knowledge, but they were directed at SteveS, and I was trying to keep it
simple, explaining not just the code, but the thought processes behind
developing an algorithm to achieve his objectives.

In regard to the solutions proposed... generally it is my experience that
for this type of feature the number of records involved is limited (< 50)
because you wouldn't expect the user to wade through a list of
100's/1000's of records, making a decision about the status of each and
everyone of them on a regular basis due to the increased margin for user
error.

If this is the case then I believe the first solution will be more than
adequate for your needs.

If this is not the case then I would consider rethinking the user
interface to reduce a potentially onerous and error prone user task
Perhaps by limiting and grouping the records in such away that the user
can quickly make a decision about their status and therefore use the
Select All/Deselect All feature.

Just my 2 cents worth.
Guy

In fact a few typo's in my example...

Depending on the structure of your data you may want to specify:

1) The foreign key (not the primary key)
2) No key to update all records in the table.

Forms!txtPrimary key should be me!txtPrimary key or something/nothing
else (see above)

and also

Set db = Nothing

Before exiting.

Cheers
Guy

Steve,

There are many ways to skin the cat.

If you believe performance will be a factor, then here is an alternative
solution using a parameterised Update query.

Private Sub cmdSelect_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set db = CurrentDb
Set qdf = db.QueryDefs("qupdSelectAll")
qdf.Parameters("PrimaryKey") = Forms!txtPrimaryKey ' Note. may require
more parameters if a composite key.
qdf.Parameters("Status") = blnSelect
qdf.Execute
Set qdf = Nothing
Me.Refresh
End Sub

As you can see the basic design has changed only the method of updating
the records.

Hope this helps
Guy

Yes, looks like it should work. However, an Update query is much faster
than cycling through a recordset!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]




Steve,

This is a quick example that should work, although I haven't tested
it. Each time the users clicks the button it will either select or
deselect all your records. Note. the gotcha with this code is that you
will undoubtedly need to set the initial select status of blnSelect to
the correct value based on your current recordset value(s) before the
user clicks the button.
Otherwise the first time the user clicks the button it will select all
(when all records may have already been selected). In which case
remove the Static blnSelect, perhaps making it a module level variable
that you set somewhere, the Form_Current event may be appropriate.

Private Sub cmdSelect_Click()
Dim rst As New DAO.Recordset
Static blnSelect As Boolean
' Toggle select/deselect all
blnSelect = Not blnSelect
Set rst = Me.RecordsetClone
' Update status
With rst
While Not .EOF
!Status = blnSelect
.Update
.MoveNext
Wend
.Close
End With
Set rst = Nothing
Me.Refresh
End Sub

Hope this helps.

Cheers
Guy

:

Hi all,

I have a continuous form which users can input the name of an
activity and
select or de-select an associated checkbox depending on whether
they want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form
footer, which
will either check or uncheck all the associated checkboxes within
the
continuous form.

I've tried the following (rather simplistic) code for a "Select
All"
button,
but it only works on a single record (the last one that was
active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click
event) to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to
get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to
be
something like (need help with the parts in <>, which is most of
it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Carl Rapson]

Hi all,

I have a continuous form which users can input the name of an activity
and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson

OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

1) The Click event is the event that fires when a command button is clicked.
You can "trap" this event and write your own VBA code to do just about
whatever you want when the event fires. To trap the Click event for a
command button, do the following: select the command button in form Design
view, then open the button's Properties window. Click on the Event tab, then
click in the field next to the On Click event. From the dropdown, select
[Event Procedure], then click on the small button with three dots next to
the field. The VBA code window will open, with the Click event already
started for you.

2) To update every field in the table, add code something like this:

DoCmd.RunSQL "UPDATE [tblActivities] SET chkboxTrackResults=True;"
or
DoCmd.RunSQL "UPDATE [tblActivities] SET chkboxTrackResults=False;"

depending on which command button you're handling.

3) Yes, this would update the settings for every record in the table.

Carl Rapson

 

[SteveS]

Carl,

This is simple and works like a charm. Thank you!

I do have one other question - when it runs I get a standard message saying
that it's going to update x number of records, and it can't be undone. How
can I replace the standard message with one of my own?

Thanks again!
Steve

Hi all,

I have a continuous form which users can input the name of an activity
and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson

OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

1) The Click event is the event that fires when a command button is clicked.
You can "trap" this event and write your own VBA code to do just about
whatever you want when the event fires. To trap the Click event for a
command button, do the following: select the command button in form Design
view, then open the button's Properties window. Click on the Event tab, then
click in the field next to the On Click event. From the dropdown, select
[Event Procedure], then click on the small button with three dots next to
the field. The VBA code window will open, with the Click event already
started for you.

2) To update every field in the table, add code something like this:

DoCmd.RunSQL "UPDATE [tblActivities] SET chkboxTrackResults=True;"
or
DoCmd.RunSQL "UPDATE [tblActivities] SET chkboxTrackResults=False;"

depending on which command button you're handling.

3) Yes, this would update the settings for every record in the table.

Carl Rapson

 

[SteveS]

Guy and Steve (the other one),

Thanks so much for the input - imagine my surprise when I looked this
morning and there were 15 posts to my message (I haven't been able to check
it for several days, so apologies for my delay).

Ultimately since my programming experience is minimal, at best, I went with
Carl's rather straightforward suggestion, mostly in the interest of time. But
both of your input is very appreciated and if I have any issues I will
revisit it.

Thanks again,
SteveS

Steve,

I should also mention that if you currently have a mixture of checked and
unchecked values on your continous form then you can either:

1) Take your pick as to whether all records a Checked or Unchecked when the
button is first clicked
Or
2) Be really tricky and toggle (reverse) the status of each
Checked/Unchecked item depending on it's Current status.

The code I've provided may need modifying in these instances to achieve your
objective, but hopefully this gives you a starting point.

Cheers
Guy

Hi all,

I have a continuous form which users can input the name of an activity
and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson

OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to get
into SQL view from the properties of my control? (Or from wherever in the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to be
something like (need help with the parts in <>, which is most of it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

 

[Carl Rapson]

You can remove that message by turning off the Confirm Record changes option
(and maybe the Confirm Action Queries option as well). I don't think there's
any way to trap that message, however. If you really want to show your own
message, I would recommend first opening a recordset based on the table and
using the recordset's Count property to display how many records there are
to be changed. After the user responds to your message, go ahead and execute
the UPDATE statement.

Carl Rapson

Carl,

This is simple and works like a charm. Thank you!

I do have one other question - when it runs I get a standard message
saying
that it's going to update x number of records, and it can't be undone. How
can I replace the standard message with one of my own?

Thanks again!
Steve

:

Hi all,

I have a continuous form which users can input the name of an
activity
and
select or de-select an associated checkbox depending on whether they
want
that activity counted in a separate report or not. I would like to
add
"Select All" and "Deselect All" command buttons in the form footer,
which
will either check or uncheck all the associated checkboxes within
the
continuous form.

I've tried the following (rather simplistic) code for a "Select All"
button,
but it only works on a single record (the last one that was active).

Private Sub cmdSelectAll_Click()
Me.[chkboxTrackResults].Value = True
End Sub

I need to get this to work in all the records displayed on the
continuous
form. How can I do this (keeping in mind I'm relatively new to
Access...)

Thanks in advance!

One thing you could try is to use an SQL statement (in the Click
event)
to
update the data in the underlying table, then re-query the form.

Carl Rapson



OK, but I will need some help with this approach. (Like I said, am
relatively new to Access, and am self-taught. Also, I've only done
minimal
work with SQL statements for queries, not with forms...)

1) Is this the same as the 'build event', or if not where do I go to
get
into SQL view from the properties of my control? (Or from wherever in
the
design view of my form)

2) My table is called tblAcitivities. So assume my code would need to
be
something like (need help with the parts in <>, which is most of
it...):

<what would the 'on click' code be?>
SELECT chkboxTrackResults FROM tblActivities
<what would the code be for "enable = true"?>
<code for re-query of form>

3) Would this update the settings for every record though?

Thanks!

1) The Click event is the event that fires when a command button is
clicked.
You can "trap" this event and write your own VBA code to do just about
whatever you want when the event fires. To trap the Click event for a
command button, do the following: select the command button in form
Design
view, then open the button's Properties window. Click on the Event tab,
then
click in the field next to the On Click event. From the dropdown, select
[Event Procedure], then click on the small button with three dots next to
the field. The VBA code window will open, with the Click event already
started for you.

2) To update every field in the table, add code something like this:

DoCmd.RunSQL "UPDATE [tblActivities] SET chkboxTrackResults=True;"
or
DoCmd.RunSQL "UPDATE [tblActivities] SET chkboxTrackResults=False;"

depending on which command button you're handling.

3) Yes, this would update the settings for every record in the table.

Carl Rapson