Inhibit tick boxes

I have a form with about 7 tick box controls. What can I do so that once one
box is ticked the others become inactive and a message pops up that says "You
can only tick one box at a time"

Would I be better using radio buttons?

Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

It's not so much radio buttons as it is an option group. However, if you are
storing the seven values in seven different fields you will need to redo
things. With an option group you store a numeric value in a single field.
How you translate that into something with meaning to the user, or work with
the value in code or expressions, depends on the specific situation.

In direct answer to your question, you could write a function to disable all
but the selected check box, which you would call in the After Update event of
each check box and in the form's Current event, but you really don't want to
do that. For one thing, it would be very difficult for somebody to change
their mind and make a different selection. For another, for a new record at
least all of the options must be available. For another, if you add another
check box at some time it would mean another field, which could mean changing
queries and VBA code. It would be less work now and in the future to
implement the option group.

I don't know if it applies to this situation, but this article about not
using Yes/No fields to store preferences may be of interest:
http://allenbrowne.com/casu-23.html

TonyWilliams wrote:
I have a form with about 7 tick box controls. What can I do so that once one
box is ticked the others become inactive and a message pops up that says "You
can only tick one box at a time"

Would I be better using radio buttons?

Thanks
Tony


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

Thanks Bruce I'll have a look at that article.
Cheers
Tony


BruceM wrote:
It's not so much radio buttons as it is an option group. However, if you are
storing the seven values in seven different fields you will need to redo
things. With an option group you store a numeric value in a single field.
How you translate that into something with meaning to the user, or work with
the value in code or expressions, depends on the specific situation.

In direct answer to your question, you could write a function to disable all
but the selected check box, which you would call in the After Update event of
each check box and in the form's Current event, but you really don't want to
do that. For one thing, it would be very difficult for somebody to change
their mind and make a different selection. For another, for a new record at
least all of the options must be available. For another, if you add another
check box at some time it would mean another field, which could mean changing
queries and VBA code. It would be less work now and in the future to
implement the option group.

I don't know if it applies to this situation, but this article about not
using Yes/No fields to store preferences may be of interest:
http://allenbrowne.com/casu-23.html

I have a form with about 7 tick box controls. What can I do so that once one
box is ticked the others become inactive and a message pops up that says "You
[quoted text clipped - 4 lines]
Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

I've has a look at Allen Browne's page and Think I understand the concept but
am struggling a bit in relating it to my situation as I don't think I have a
many to many relationship.

This is what I have:
I have a table which holds company details (tblcompany) with the company name
as txtcompany.

I have a table which holds all the details of financial deals for each
company (tblhighvalue). This table also has a field txtcompany which is
linked to tblcompany. I think it is a many to many relationship as there can
be many deals for each company. However each deal has a choice as to which
market sector it belongs. I have 7 seven market sectors and at the moment
have them as seperate fields with tick boxes on my form.

If I want to create an Option Group which would allow only one choice how
would I structure my tables?

Thanks for your help
Tony

BruceM wrote:
It's not so much radio buttons as it is an option group. However, if you are
storing the seven values in seven different fields you will need to redo
things. With an option group you store a numeric value in a single field.
How you translate that into something with meaning to the user, or work with
the value in code or expressions, depends on the specific situation.

In direct answer to your question, you could write a function to disable all
but the selected check box, which you would call in the After Update event of
each check box and in the form's Current event, but you really don't want to
do that. For one thing, it would be very difficult for somebody to change
their mind and make a different selection. For another, for a new record at
least all of the options must be available. For another, if you add another
check box at some time it would mean another field, which could mean changing
queries and VBA code. It would be less work now and in the future to
implement the option group.

I don't know if it applies to this situation, but this article about not
using Yes/No fields to store preferences may be of interest:
http://allenbrowne.com/casu-23.html

I have a form with about 7 tick box controls. What can I do so that once one
box is ticked the others become inactive and a message pops up that says "You
[quoted text clipped - 4 lines]
Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

One company, many deals is a one-to-many relationship. Typically you would
use a numeric CompanyID field to identify the company, and therefore a
linking field of the same type. If CompanyID is Autonumber, the linking
field in the related table is Number (Long Integer). Names are subject to
change, which means you will need to update the related table when that
happens.

An example of many-to-many is Students and Courses. One Student may take
many Courses, and one Course may be attended by many Students. A third table
is needed to resolve the relationship. In your case, unless a deal may apply
to many companies you have a one-to-many as described in my first paragraph.

As I mentioned, an Option Group is bound to a numeric field. Each of the
option buttons (they can be radio buttons, toggle buttons, or check boxes)
has an Option Value (set on the Property Sheet for the control).

You could have a table for Market Sectors.

tblSector
SectorID (numeric primary key, or PK)
Sector

Each sector is a separate record.

Best would be to create a simple form bound to tblSector (or a query based on
tblSector). As the Default Value property of the text box bound to SectorID:
=Nz(DMax("SectorID","tblSector"),0) + 1
This will number the records sequentially, starting with 1.

The Option Values of the option buttons are numbered to correspond to the
SectorID values in tblSector. Once that is done there are a number of ways
to display the corresponding Sector (text) value. For instance, in a text
box on the form, set the Control Source to:

=DLookup("Sector","tblSector","SectorID = " & [CoSectorID])

The above assumes the option group is bound to a field named CoSectorID.
CoSectorID is the field on the main form's table in which you are storing the
option group number (i.e. the Option Value from the selected option button).

A sample table setup for the main table would be:

tblCompany
CompanyID (autonumber PK)
CompanyName
CoSectorID (Number field - long integer)
Address, etc.

Note that you do not need fields for each of the market sectors. That value
is being stored as a number in CoSectorID. The DLookup expression above
finds the Sector (text) value from the tblSector record in which SectorID is
the same as the Option Group value.

TonyWilliams wrote:
I've has a look at Allen Browne's page and Think I understand the concept but
am struggling a bit in relating it to my situation as I don't think I have a
many to many relationship.

This is what I have:
I have a table which holds company details (tblcompany) with the company name
as txtcompany.

I have a table which holds all the details of financial deals for each
company (tblhighvalue). This table also has a field txtcompany which is
linked to tblcompany. I think it is a many to many relationship as there can
be many deals for each company. However each deal has a choice as to which
market sector it belongs. I have 7 seven market sectors and at the moment
have them as seperate fields with tick boxes on my form.

If I want to create an Option Group which would allow only one choice how
would I structure my tables?

Thanks for your help
Tony

It's not so much radio buttons as it is an option group. However, if you are
storing the seven values in seven different fields you will need to redo
[quoted text clipped - 21 lines]
Thanks
Tony


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

Thanks Bruce I'll work my way through that and come back if I have a problem.
Regards
tony

BruceM wrote:
One company, many deals is a one-to-many relationship. Typically you would
use a numeric CompanyID field to identify the company, and therefore a
linking field of the same type. If CompanyID is Autonumber, the linking
field in the related table is Number (Long Integer). Names are subject to
change, which means you will need to update the related table when that
happens.

An example of many-to-many is Students and Courses. One Student may take
many Courses, and one Course may be attended by many Students. A third table
is needed to resolve the relationship. In your case, unless a deal may apply
to many companies you have a one-to-many as described in my first paragraph.

As I mentioned, an Option Group is bound to a numeric field. Each of the
option buttons (they can be radio buttons, toggle buttons, or check boxes)
has an Option Value (set on the Property Sheet for the control).

You could have a table for Market Sectors.

tblSector
SectorID (numeric primary key, or PK)
Sector

Each sector is a separate record.

Best would be to create a simple form bound to tblSector (or a query based on
tblSector). As the Default Value property of the text box bound to SectorID:
=Nz(DMax("SectorID","tblSector"),0) + 1
This will number the records sequentially, starting with 1.

The Option Values of the option buttons are numbered to correspond to the
SectorID values in tblSector. Once that is done there are a number of ways
to display the corresponding Sector (text) value. For instance, in a text
box on the form, set the Control Source to:

=DLookup("Sector","tblSector","SectorID = " & [CoSectorID])

The above assumes the option group is bound to a field named CoSectorID.
CoSectorID is the field on the main form's table in which you are storing the
option group number (i.e. the Option Value from the selected option button).

A sample table setup for the main table would be:

tblCompany
CompanyID (autonumber PK)
CompanyName
CoSectorID (Number field - long integer)
Address, etc.

Note that you do not need fields for each of the market sectors. That value
is being stored as a number in CoSectorID. The DLookup expression above
finds the Sector (text) value from the tblSector record in which SectorID is
the same as the Option Group value.

I've has a look at Allen Browne's page and Think I understand the concept but
am struggling a bit in relating it to my situation as I don't think I have a
[quoted text clipped - 22 lines]
Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via http://www.accessmonster.com

On Wed, 10 Feb 2010 13:38:11 GMT, "TonyWilliams via AccessMonster.com"
u56994@uwe wrote:

I have a table which holds all the details of financial deals for each
company (tblhighvalue). This table also has a field txtcompany which is
linked to tblcompany. I think it is a many to many relationship as there can
be many deals for each company. However each deal has a choice as to which
market sector it belongs. I have 7 seven market sectors and at the moment
have them as seperate fields with tick boxes on my form.

If I want to create an Option Group which would allow only one choice how
would I structure my tables?

You should have a (seven row) table of MarketSectors. Your tblHighValue should
have a single integer field for MarketSector (not seven yes/no fields); you
could bind this field to an Option Group, a Listbox, or a Combo Box control,
whatever suits your needs - but you only need to store one numeric value into
your table.
--

John W. Vinson [MVP]

Better still would be to use an Option group - also called a frame.
This will give you the option of using checkboxes or radio buttons or some
other sort of button.
The great thing about the option group is that the user can only select one
choice at a time - you don't need any code to achieve this.

I don't know anything about your database, but if you have many yes/no
fields in that table, your design would benefit from eliminating most of
those yes/no fields.

If you are interested, post back.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"TonyWilliams via AccessMonster.com" u56994@uwe wrote in message
news:a36b24b14755d@uwe...
I have a form with about 7 tick box controls. What can I do so that once
one
box is ticked the others become inactive and a message pops up that says
"You
can only tick one box at a time"

Would I be better using radio buttons?

Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I
hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

Thanks Jeanette, I've created a new table as suggested and got an Option
Group working fine.
Thanks again
Tony

Jeanette Cunningham wrote:
Better still would be to use an Option group - also called a frame.
This will give you the option of using checkboxes or radio buttons or some
other sort of button.
The great thing about the option group is that the user can only select one
choice at a time - you don't need any code to achieve this.

I don't know anything about your database, but if you have many yes/no
fields in that table, your design would benefit from eliminating most of
those yes/no fields.

If you are interested, post back.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a form with about 7 tick box controls. What can I do so that once
one
[quoted text clipped - 6 lines]
Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

John That's what I've now created and used an Option Group.
Thanks for your input.
Tony

John W. Vinson wrote:
I have a table which holds all the details of financial deals for each
company (tblhighvalue). This table also has a field txtcompany which is
[quoted text clipped - 5 lines]
If I want to create an Option Group which would allow only one choice how
would I structure my tables?

You should have a (seven row) table of MarketSectors. Your tblHighValue should
have a single integer field for MarketSector (not seven yes/no fields); you
could bind this field to an Option Group, a Listbox, or a Combo Box control,
whatever suits your needs - but you only need to store one numeric value into
your table.

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1