Using checkboxes on subforms

[Deborah V. Gardner]

I would like to use "Yes" and "No" checkboxes on a subform. The problem
is that when I click the Yes checkbox on the subform, all of the
checkboxes are checked.

Currently, I have a field "Refused." It is defined as a text field. A
"Y" means that the service has been refused; an "N" indicates it has
been accepted. If the field is Null, the question has not been answered,
i.e. we don't know if it has been refused or not.

I want the user to be able to check the Yes checkbox and have a Y
entered into the field, or check the No checkbox and have an N entered.
This is not a problem. I have used an If statement on the Click events
of each checkbox.

Let's say that one of the boxes is checked in error. I want to be able
to uncheck it and set the field to Null.

I tried changing the field to Number and using an option group and
writing code in the OnClick event and the BeforeUpdate event, but to no
avail. I figured if I could capture the field contents before the field
was changed, I could get it to work but cannot figure out out to capture
that data.

I know that I can have two fields "RefusedYes" and "RefusedNo" but that
means using two fields when only one is really required and will cause
more work down the line when using the data in queries and in code.

Any ideas on how to do this and not have all the Yes or No checkboxes
checked?

Thank you,
Deborah

[Larry Linson]

That is the way unbound controls work in continuous forms view, which I
assume is the form of display in your subform. There's just not a
workaround, not an even near simple one, at least.

Bind the checkbox to a Yes/No field and it'll work as you desire. If
necessary, put the Yes/No field in a different table and join the two tables
in the Query that you use for RecordSource.

Larry Linson
Microsoft Access MVP
"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:40**************@twcny.rr.com...

I would like to use "Yes" and "No" checkboxes on a subform. The problem
is that when I click the Yes checkbox on the subform, all of the
checkboxes are checked.

Currently, I have a field "Refused." It is defined as a text field. A
"Y" means that the service has been refused; an "N" indicates it has
been accepted. If the field is Null, the question has not been answered,
i.e. we don't know if it has been refused or not.

I want the user to be able to check the Yes checkbox and have a Y
entered into the field, or check the No checkbox and have an N entered.
This is not a problem. I have used an If statement on the Click events
of each checkbox.

Let's say that one of the boxes is checked in error. I want to be able
to uncheck it and set the field to Null.

I tried changing the field to Number and using an option group and
writing code in the OnClick event and the BeforeUpdate event, but to no
avail. I figured if I could capture the field contents before the field
was changed, I could get it to work but cannot figure out out to capture
that data.

I know that I can have two fields "RefusedYes" and "RefusedNo" but that
means using two fields when only one is really required and will cause
more work down the line when using the data in queries and in code.

Any ideas on how to do this and not have all the Yes or No checkboxes
checked?

Thank you,
Deborah

[Rich Harrington]

The main problem is that you are using an 'unbound' subform - in other words
there is no data table underlying the form object.

The easiest way to do what you want is to create a table containing fields
for each gui element on the subform. Set the Subform.Form.RecordSource
property to the new table, and set the Control.DataSource property for each
of the text / checkboxes on the subform to the appropriate table field.
Access will then automatically update the underlying table values and you
can get rid of the code events setting values. Each of the controls will now
behave independently. Set the Checkbox.TripleState property to allow for
true/false/null values.

Good luck

Enrico Palazzo


"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:40**************@twcny.rr.com...

I would like to use "Yes" and "No" checkboxes on a subform. The problem
is that when I click the Yes checkbox on the subform, all of the
checkboxes are checked.

Currently, I have a field "Refused." It is defined as a text field. A
"Y" means that the service has been refused; an "N" indicates it has
been accepted. If the field is Null, the question has not been answered,
i.e. we don't know if it has been refused or not.

I want the user to be able to check the Yes checkbox and have a Y
entered into the field, or check the No checkbox and have an N entered.
This is not a problem. I have used an If statement on the Click events
of each checkbox.

Let's say that one of the boxes is checked in error. I want to be able
to uncheck it and set the field to Null.

I tried changing the field to Number and using an option group and
writing code in the OnClick event and the BeforeUpdate event, but to no
avail. I figured if I could capture the field contents before the field
was changed, I could get it to work but cannot figure out out to capture
that data.

I know that I can have two fields "RefusedYes" and "RefusedNo" but that
means using two fields when only one is really required and will cause
more work down the line when using the data in queries and in code.

Any ideas on how to do this and not have all the Yes or No checkboxes
checked?

Thank you,
Deborah

[TC]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:sa***************@nwrddc02.gnilink.net...

That is the way unbound controls work in continuous forms view, which I
assume is the form of display in your subform. There's just not a
workaround, not an even near simple one, at least.
Sure there is! You can easily display distinct (per-row) values in an
unbound control on a continuous form. It takes a dozen lines of code (if
that). You can even make them updatable! That's more difficult, but still
quite do-able. See the following google message ID:

< 1067659397.218969 @ teuthos > (remove the four spaces)

TC


Bind the checkbox to a Yes/No field and it'll work as you desire. If
necessary, put the Yes/No field in a different table and join the two tables in the Query that you use for RecordSource.

Larry Linson
Microsoft Access MVP
"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:40**************@twcny.rr.com...

I would like to use "Yes" and "No" checkboxes on a subform. The problem
is that when I click the Yes checkbox on the subform, all of the
checkboxes are checked.

Currently, I have a field "Refused." It is defined as a text field. A
"Y" means that the service has been refused; an "N" indicates it has
been accepted. If the field is Null, the question has not been answered,
i.e. we don't know if it has been refused or not.

I want the user to be able to check the Yes checkbox and have a Y
entered into the field, or check the No checkbox and have an N entered.
This is not a problem. I have used an If statement on the Click events
of each checkbox.

Let's say that one of the boxes is checked in error. I want to be able
to uncheck it and set the field to Null.

I tried changing the field to Number and using an option group and
writing code in the OnClick event and the BeforeUpdate event, but to no
avail. I figured if I could capture the field contents before the field
was changed, I could get it to work but cannot figure out out to capture
that data.

I know that I can have two fields "RefusedYes" and "RefusedNo" but that
means using two fields when only one is really required and will cause
more work down the line when using the data in queries and in code.

Any ideas on how to do this and not have all the Yes or No checkboxes
checked?

Thank you,
Deborah

[Larry Linson]

<CHUCKLE> If it were "even near simple", wouldn't you have found it
worthwhile to remember the details to do it, as you caution you didn't?
"TC" <a@b.c.d> wrote in message news:1074567506.874946@teuthos...

"Larry Linson" <bo*****@localhost.not> wrote in message
news:sa***************@nwrddc02.gnilink.net...

That is the way unbound controls work in continuous forms view, which I
assume is the form of display in your subform. There's just not a
workaround, not an even near simple one, at least.

Sure there is! You can easily display distinct (per-row) values in an
unbound control on a continuous form. It takes a dozen lines of code (if
that). You can even make them updatable! That's more difficult, but still
quite do-able. See the following google message ID:

< 1067659397.218969 @ teuthos > (remove the four spaces)

TC

Bind the checkbox to a Yes/No field and it'll work as you desire. If
necessary, put the Yes/No field in a different table and join the two

tables

in the Query that you use for RecordSource.

Larry Linson
Microsoft Access MVP
"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:40**************@twcny.rr.com...

I would like to use "Yes" and "No" checkboxes on a subform. The problem is that when I click the Yes checkbox on the subform, all of the
checkboxes are checked.

Currently, I have a field "Refused." It is defined as a text field. A
"Y" means that the service has been refused; an "N" indicates it has
been accepted. If the field is Null, the question has not been answered, i.e. we don't know if it has been refused or not.

I want the user to be able to check the Yes checkbox and have a Y
entered into the field, or check the No checkbox and have an N entered. This is not a problem. I have used an If statement on the Click events
of each checkbox.

Let's say that one of the boxes is checked in error. I want to be able
to uncheck it and set the field to Null.

I tried changing the field to Number and using an option group and
writing code in the OnClick event and the BeforeUpdate event, but to no avail. I figured if I could capture the field contents before the field was changed, I could get it to work but cannot figure out out to capture that data.

I know that I can have two fields "RefusedYes" and "RefusedNo" but that means using two fields when only one is really required and will cause
more work down the line when using the data in queries and in code.

Any ideas on how to do this and not have all the Yes or No checkboxes
checked?

Thank you,
Deborah

[TC]

The comment in question was referring to the option of making the per-row
unbound fields updatable.

Since you are clearly skeptical of the simplicity of having per-row unbound
values on a continuous form, let me spell it out - from memory!

1. Create bound subform.

2. Add an unbound textbox with recordsource:
=FieldValue()

3. Put this code in the form module:
private gValue() as variant
private sub form_open (cancel as integer)
' do anything to redim gValue() from zero to
' me.recordsetclone.absoluteposition, and
' put a value in each element.
end sub
private function FieldValue() as variant
with me.recordsetclone
.bookmark = me.bookmark
FieldValue = gValue (.absoluteposition)
end with
end sub

A dozen lines of code. I say this is perfectly simple. Your milage may vary!

TC
"Larry Linson" <bo*****@localhost.not> wrote in message
news:wa****************@nwrddc02.gnilink.net...

<CHUCKLE> If it were "even near simple", wouldn't you have found it
worthwhile to remember the details to do it, as you caution you didn't?
"TC" <a@b.c.d> wrote in message news:1074567506.874946@teuthos...

"Larry Linson" <bo*****@localhost.not> wrote in message
news:sa***************@nwrddc02.gnilink.net...

That is the way unbound controls work in continuous forms view, which I assume is the form of display in your subform. There's just not a
workaround, not an even near simple one, at least.

Sure there is! You can easily display distinct (per-row) values in an
unbound control on a continuous form. It takes a dozen lines of code (if
that). You can even make them updatable! That's more difficult, but still

quite do-able. See the following google message ID:

< 1067659397.218969 @ teuthos > (remove the four spaces)

TC

Bind the checkbox to a Yes/No field and it'll work as you desire. If
necessary, put the Yes/No field in a different table and join the two

tables

in the Query that you use for RecordSource.

Larry Linson
Microsoft Access MVP
"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:40**************@twcny.rr.com...
> I would like to use "Yes" and "No" checkboxes on a subform. The

problem > is that when I click the Yes checkbox on the subform, all of the
> checkboxes are checked.
>
> Currently, I have a field "Refused." It is defined as a text field. A > "Y" means that the service has been refused; an "N" indicates it has
> been accepted. If the field is Null, the question has not been answered, > i.e. we don't know if it has been refused or not.
>
> I want the user to be able to check the Yes checkbox and have a Y
> entered into the field, or check the No checkbox and have an N entered. > This is not a problem. I have used an If statement on the Click events > of each checkbox.
>
> Let's say that one of the boxes is checked in error. I want to be able > to uncheck it and set the field to Null.
>
> I tried changing the field to Number and using an option group and
> writing code in the OnClick event and the BeforeUpdate event, but to no > avail. I figured if I could capture the field contents before the field > was changed, I could get it to work but cannot figure out out to capture > that data.
>
> I know that I can have two fields "RefusedYes" and "RefusedNo" but that > means using two fields when only one is really required and will cause > more work down the line when using the data in queries and in code.
>
> Any ideas on how to do this and not have all the Yes or No checkboxes > checked?
>
> Thank you,
> Deborah
>