Multiple Check Boxes

[Search & You Will Find]

I have a database in Access 2000 that I need some help on.

I have three tables: PROJECTS, SYSTEMS, & SYSTEMSREF.

They possess the following fields:

-----------------------------------
PROJECTS
project_pk (autonumber)
project_name
project_descr
project_date
etc
-----------------------------------
SYSTEMS
systems_pk (autonumber)
project_fk
systemsref_fk
-----------------------------------
SYSTEMSREF
systemsref_pk (autonumber)
systemsref_desc
-----------------------------------

The tables are related as follows:

project_pk ------project_fk
systemsref_fk ------systemsref_pk

I have created a form that allows me to input new projects in the
project table. I have a subreport (in datasheet view) to select all
the systems a project may impact. This is working okay for now, but I
need to change the design of the subreport to list each option as a
checkbox. However, I need to be able to select multiple options.
Furthermore, if an option is checked, it needs to create a new record
within the SYSTEMS table. If I uncheck an option, it needs to delete
the record.

I am comfortable coding in VBA if this is required.

[Salad]

Search & You Will Find wrote:

I have a database in Access 2000 that I need some help on.

I have three tables: PROJECTS, SYSTEMS, & SYSTEMSREF.

They possess the following fields:

-----------------------------------
PROJECTS
project_pk (autonumber)
project_name
project_descr
project_date
etc
-----------------------------------
SYSTEMS
systems_pk (autonumber)
project_fk
systemsref_fk
-----------------------------------
SYSTEMSREF
systemsref_pk (autonumber)
systemsref_desc
-----------------------------------

The tables are related as follows:

project_pk ------project_fk
systemsref_fk ------systemsref_pk

I have created a form that allows me to input new projects in the
project table. I have a subreport (in datasheet view) to select all
the systems a project may impact. This is working okay for now, but I
need to change the design of the subreport to list each option as a
checkbox. However, I need to be able to select multiple options.
Furthermore, if an option is checked, it needs to create a new record
within the SYSTEMS table. If I uncheck an option, it needs to delete
the record.

I am comfortable coding in VBA if this is required.

What makes something true and something false? IOW, what is the initial
value of the checkbox? Maybe your subform could be a listbox...set up
to display all systems. Make it Multiselect; simple or extended. In
the OnOpen event select set the selected to True for those that exist.
Maybe set a Command button to add/remove those that were
clicked/selected/deselected.

[Larry Linson]

Assuming, that the options will change from time to time, what you will do
if you represent them with Checkboxes is embed data in the structure of your
UI and you will have to change the Form whenever an option changes, is
added, or is deleted. If you explain that to whoever told you that you
"need to" make the change, perhaps they will allow you to use Salad's good
suggestion.

However, if they have the say in the matter, and won't relent, then you may
have to bite the bullet, add the checkboxes, and add code to perform the
actions you describe.

Larry Linson
Microsoft Access MVP

"Search & You Will Find" <jo**********@gmail.comwrote in message
news:11**********************@r34g2000hsd.googlegr oups.com...

>I have a database in Access 2000 that I need some help on.

I have three tables: PROJECTS, SYSTEMS, & SYSTEMSREF.

They possess the following fields:

-----------------------------------
PROJECTS
project_pk (autonumber)
project_name
project_descr
project_date
etc
-----------------------------------
SYSTEMS
systems_pk (autonumber)
project_fk
systemsref_fk
-----------------------------------
SYSTEMSREF
systemsref_pk (autonumber)
systemsref_desc
-----------------------------------

The tables are related as follows:

project_pk ------project_fk
systemsref_fk ------systemsref_pk

I have created a form that allows me to input new projects in the
project table. I have a subreport (in datasheet view) to select all
the systems a project may impact. This is working okay for now, but I
need to change the design of the subreport to list each option as a
checkbox. However, I need to be able to select multiple options.
Furthermore, if an option is checked, it needs to create a new record
within the SYSTEMS table. If I uncheck an option, it needs to delete
the record.

I am comfortable coding in VBA if this is required.

[Search & You Will Find]

On Sep 5, 7:48 pm, "Larry Linson" <[e-mail]:

Assuming, that the options will change from time to time, what you will do
if you represent them with Checkboxes is embed data in the structure of your
UI and you will have to change the Form whenever an option changes, is
added, or is deleted. If you explain that to whoever told you that you
"need to" make the change, perhaps they will allow you to use Salad's good
suggestion.

However, if they have the say in the matter, and won't relent, then you may
have to bite the bullet, add the checkboxes, and add code to perform the
actions you describe.

Larry Linson
Microsoft Access MVP

"Search & You Will Find" <[e-mail] in messagenews:11**********************@r34g2000hsd.g ooglegroups.com...

I have a database in Access 2000 that I need some help on.

I have three tables: PROJECTS, SYSTEMS, & SYSTEMSREF.

They possess the following fields:

-----------------------------------
PROJECTS
project_pk (autonumber)
project_name
project_descr
project_date
etc
-----------------------------------
SYSTEMS
systems_pk (autonumber)
project_fk
systemsref_fk
-----------------------------------
SYSTEMSREF
systemsref_pk (autonumber)
systemsref_desc
-----------------------------------

The tables are related as follows:

project_pk ------project_fk
systemsref_fk ------systemsref_pk

I have created a form that allows me to input new projects in the
project table. I have a subreport (in datasheet view) to select all
the systems a project may impact. This is working okay for now, but I
need to change the design of the subreport to list each option as a
checkbox. However, I need to be able to select multiple options.
Furthermore, if an option is checked, it needs to create a new record
within the SYSTEMS table. If I uncheck an option, it needs to delete
the record.

I am comfortable coding in VBA if this is required.- Hide quoted text -

- Show quoted text -

I tried to get around the check boxes, but since my boss has the final
say and her final say is check boxes, I have to go along. I would like
to have the form present the check boxes similarly to how Access
presents Switchboard items. So if new items are added, they
automatically appear.

My main issue is deleting the record that is associated with project
when the items is unchecked.