Continuous Forms Group Selection Counter

  • Thread starter Thread starter rpboll
  • Start date Start date
R

rpboll

I am looking for the best way to accomplish the following Continuous
(bound) Forms concept.

Given: There are Five Groups A, B, C, D, E

Each group has eight members: a, b, c, d, e, f, g, h

Each member is seen as a row in a continuous form like:

A a
A b
A c
..
..
..
A h
B a
B b
..
..
..

Each row (member) has a selection checkbox and a lable

The behavior that I am trying to accois -- when you click on a
checkbox
for any group, the count for that group is reflected in it's lable.

Using Group D as an example, if you check member D b (and it is the
only member of that group selected) then D b's lable shows "1" -- if
you then select member D g the label updates to "2" and so on until
your reach "4". If you de-select member D b it's label value
disappears and the value for Group D is refreshed with the total
number of checks for Group D.

When you exceed "4" the number briefly turns into a message like "All
4 Selected" and then back to a "4".

Thanks for any suggestions,

RBolling
 
rpboll said:
I am looking for the best way to accomplish the following Continuous
(bound) Forms concept.

Given: There are Five Groups A, B, C, D, E

Each group has eight members: a, b, c, d, e, f, g, h

Each member is seen as a row in a continuous form like:

A a
A b
A c
.
.
.
A h
B a
B b
.
.
.

Each row (member) has a selection checkbox and a lable

The behavior that I am trying to accois -- when you click on a
checkbox
for any group, the count for that group is reflected in it's lable.

Using Group D as an example, if you check member D b (and it is the
only member of that group selected) then D b's lable shows "1" -- if
you then select member D g the label updates to "2" and so on until
your reach "4". If you de-select member D b it's label value
disappears and the value for Group D is refreshed with the total
number of checks for Group D.

When you exceed "4" the number briefly turns into a message like "All
4 Selected" and then back to a "4".
Click to expand...


You can use a text box for the label. Set the text box's
control source expression to:
=DCount("*","table","check AND Group = '" & Group & "'")

Use the check box's AfterUpdate event to see if too many are
checked:

If checkboxcontrol And DCount("*","table", _
"check AND group = '" & group & "'")=4 Then
MsgBox "Too many"
checkboxcontrol = False
End If

While that may do at least most of what you asked for, I
don't think it will be very practical. The DCount
expression will be evaluated frequetly and you will probably
cause significant hesitations in the form's behavior.
 
rpboll said:
I am looking for the best way to accomplish the following Continuous
(bound) Forms concept.
Click to expand...
Given: There are Five Groups A, B, C, D, E
Click to expand...
Each group has eight members: a, b, c, d, e, f, g, h
Click to expand...
Each member is seen as a row in a continuous form like:
Click to expand...
A a
A b
A c
.
.
.
A h
B a
B b
.
.
.
Click to expand...
Each row (member) has a selection checkbox and a lable
Click to expand...
The behavior that I am trying to accois -- when you click on a
checkbox
for any group, the count for that group is reflected in it's lable.
Click to expand...
Using Group D as an example, if you check member D b (and it is the
only member of that group selected) then D b's lable shows "1" -- if
you then select member D g the label updates to "2" and so on until
your reach "4". If you de-select member D b it's label value
disappears and the value for Group D is refreshed with the total
number of checks for Group D.
Click to expand...
When you exceed "4" the number briefly turns into a message like "All
4 Selected" and then back to a "4".
Click to expand...

You can use a text box for the label. Set the text box's
control source expression to:
=DCount("*","table","check AND Group = '" & Group & "'")

Use the check box's AfterUpdate event to see if too many are
checked:

If checkboxcontrol And DCount("*","table", _
"check AND group = '" & group & "'")=4 Then
MsgBox "Too many"
checkboxcontrol = False
End If

While that may do at least most of what you asked for, I
don't think it will be very practical. The DCount
expression will be evaluated frequetly and you will probably
cause significant hesitations in the form's behavior.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks! This works well. I am using a conditional format ILO the
after update check to avoid the hesitations you alluded to. For an
imediate though it needs a forms.requery. Unfortunately this causes
the control to loose focus. Do you know of a way to keep focus on the
updated checkbox when updating the control and using forms.requery?
This is a pretty awsome method.

RBolling
 
rpboll said:
Thanks! This works well. I am using a conditional format ILO the
after update check to avoid the hesitations you alluded to. For an
imediate though it needs a forms.requery. Unfortunately this causes
the control to loose focus. Do you know of a way to keep focus on the
updated checkbox when updating the control and using forms.requery?
This is a pretty awsome method.
Click to expand...


What is "a conditional format ILO"?

Are you sure you need a Requery? Maybe a Recalc will be
sufficient. If not, see if Refresh can deal with it.

If you must use a Requery, then the activities of other
users can change the set of records in the form, so you will
need to search for the record that was current before the
Requery:

varKey = Me.[pk field]
Me.Requery

'if the pk is a numeric type field
Me.Recordset.FindFirst "[pk field]=" & varKey
'if the pk is a Text field
Me.Recordset.FindFirst "[pk field]=""" & varKey & """"

Me.checkbox.SetFocus
 
Thanks! This works well. I am using a conditional format ILO the
after update check to avoid the hesitations you alluded to. For an
imediate though it needs a forms.requery. Unfortunately this causes
the control to loose focus. Do you know of a way to keep focus on the
updated checkbox when updating the control and using forms.requery?
This is a pretty awsome method.
Click to expand...

What is "a conditional format ILO"?

Are you sure you need a Requery? Maybe a Recalc will be
sufficient. If not, see if Refresh can deal with it.

If you must use a Requery, then the activities of other
users can change the set of records in the form, so you will
need to search for the record that was current before the
Requery:

varKey = Me.[pk field]
Me.Requery

'if the pk is a numeric type field
Me.Recordset.FindFirst "[pk field]=" & varKey
'if the pk is a Text field
Me.Recordset.FindFirst "[pk field]=""" & varKey & """"

Me.checkbox.SetFocus

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

You're right. The recalc works better. When you select a checkbox
the current count is incremented int the text box. Is there a way so
that all members of the group are updated as well? Probably on a
continuous form this is not practical.

Thanks for your help.

RBolling
 
robboll said:
rpboll said:
On Jul 13, 12:12 pm, Marshall Barton wrote:
Click to expand...
You can use a text box for the label. Set the text box's
control source expression to:
=DCount("*","table","check AND Group = '" & Group & "'")
Click to expand...
Use the check box's AfterUpdate event to see if too many are
checked:
Click to expand...
If checkboxcontrol And DCount("*","table", _
"check AND group = '" & group & "'")=4 Then
MsgBox "Too many"
checkboxcontrol = False
End If
Click to expand...
While that may do at least most of what you asked for, I
don't think it will be very practical. The DCount
expression will be evaluated frequetly and you will probably
cause significant hesitations in the form's behavior.
Click to expand...
Thanks! This works well. I am using a conditional format ILO the
after update check to avoid the hesitations you alluded to. For an
imediate though it needs a forms.requery. Unfortunately this causes
the control to loose focus. Do you know of a way to keep focus on the
updated checkbox when updating the control and using forms.requery?
This is a pretty awsome method.
Click to expand...

What is "a conditional format ILO"?

Are you sure you need a Requery? Maybe a Recalc will be
sufficient. If not, see if Refresh can deal with it.

If you must use a Requery, then the activities of other
users can change the set of records in the form, so you will
need to search for the record that was current before the
Requery:

varKey = Me.[pk field]
Me.Requery

'if the pk is a numeric type field
Me.Recordset.FindFirst "[pk field]=" & varKey
'if the pk is a Text field
Me.Recordset.FindFirst "[pk field]=""" & varKey & """"

Me.checkbox.SetFocus
Click to expand...

You're right. The recalc works better. When you select a checkbox
the current count is incremented int the text box. Is there a way so
that all members of the group are updated as well? Probably on a
continuous form this is not practical.
Click to expand...


That's why I originally suggested using the DLookup in a
text box expression.

What is "a conditional format ILO"?

I can't guess at it's implications, but, whatever it is, it
seems to require a Requery to update??
 
robboll said:
rpbollwrote:
On Jul 13, 12:12 pm, Marshall Barton wrote:
You can use a text box for the label. Set the text box's
control source expression to:
=DCount("*","table","check AND Group = '" & Group & "'")
Use the check box's AfterUpdate event to see if too many are
checked:
If checkboxcontrol And DCount("*","table", _
"check AND group = '" & group & "'")=4 Then
MsgBox "Too many"
checkboxcontrol = False
End If
While that may do at least most of what you asked for, I
don't think it will be very practical. The DCount
expression will be evaluated frequetly and you will probably
cause significant hesitations in the form's behavior.
Thanks! This works well. I am using a conditional format ILO the
after update check to avoid the hesitations you alluded to. For an
imediate though it needs a forms.requery. Unfortunately this causes
the control to loose focus. Do you know of a way to keep focus on the
updated checkbox when updating the control and using forms.requery?
This is a pretty awsome method.
What is "a conditional format ILO"?
Are you sure you need a Requery? Maybe a Recalc will be
sufficient. If not, see if Refresh can deal with it.
If you must use a Requery, then the activities of other
users can change the set of records in the form, so you will
need to search for the record that was current before the
Requery:
varKey = Me.[pk field]
Me.Requery
'if the pk is a numeric type field
Me.Recordset.FindFirst "[pk field]=" & varKey
'if the pk is a Text field
Me.Recordset.FindFirst "[pk field]=""" & varKey & """"
Me.checkbox.SetFocus
Click to expand...
Click to expand...
You're right. The recalc works better. When you select a checkbox
the current count is incremented int the text box. Is there a way so
that all members of the group are updated as well? Probably on a
continuous form this is not practical.
Click to expand...

That's why I originally suggested using the DLookup in a
text box expression.

What is "a conditional format ILO"?

I can't guess at it's implications, but, whatever it is, it
seems to require a Requery to update??

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Disregard the conditional format. =DCount("*","table","check AND
Group = '" & Group & "'")
does it perfect. Thank you.
 
Back
Top