Many to Many Data Entry

Hi all,

This is a doozy of a problem, using a form to join many to many fields, so
thanks if you're up for checking it out!

I have two tables that are linked with a many to many relationship using a
third table.
TblZones (with fields ZoneNo(primary key, autonumber), ZoneMo, Zone
Description, CustNo, SiteNo, etc)
TblDevices (with fields DeviceNo (primary key, autonumber), DeviceType,
Location, CustNo, SiteNo, etc)
TblDeviceZoning (With fields ID (primary key, autonumber), DeviceNo, and
ZoneNo)

A device may only be assigned to a zone that shares the same customer number
and site number. Each customer and site number combination has around 4
zones, and from 5-500 devices.

Normally, a person will use a form to enter all of the devices for a site,
separately enter all of the zones, and then go back to assign devices to
zones (possibly each at a different time). Each zone will have many devices,
and each device may have one or many zones.

Currently, I'm trying to design a form to assign the devices to zones (or
zones to devices), and am looking to make it as straight forward as possible,
hopefully with users not even knowing the PK DeviceNo and ZoneNo (you can
tell them apart basically from the other fields, but that uses descriptions
and other fields that can change).

My current form idea, though I'm at a loss at how to make it work:
Open a form to select the SiteNo and CustNo. Run a query on this form to
select all of the applicable zones (this ranges from 1-5). Run a query to
select all of the applicable devices (5-200 devices). (This first part I'm
all set with.) In my dream world I open a form that has all of the devices
on continuous form, the descriptions of the applicable zones in the header,
and check boxes (one for each applicable zone, ie 1-5 depending on the number
of zones) next to the devices to select the zones for each device. I'd also
like to select a zone for groups of devices at one time. Perhaps by
filtering them and then clicking a button to select a zone for all of the
visible devices. I have no problem having the check boxes unbound and then
clicking an update button to program everything into the TblDeviceZoning
table.

Does anyone have any ideas of how to proceed with this? Or have ideas of a
user-friendly way to join many to many fields?

Thank you so very much!
~Claire

Claire

A classic standard approach is to use a main form for one of your tables,
and a subform for your junction table.

In the subform, use a combobox to list the second table items, and make sure
your subform points to the junction table, and includes the foreign key from
both the first table and (via the combobox) the second table.

Now open your main form in design view, add the subform. Open the property
window for your subform control and use the primary key on the table filling
your main form as the "parent" property, and that foreign key from the
subform as your "child" property.

Now, when ever you have a main form record displayed, any entry you make on
the subform (e.g., selecting a ... Device) gives that record the foreign key
of the main form's record (i.e., ZoneID).

Good Luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Claire" wrote in message
...
Hi all,

This is a doozy of a problem, using a form to join many to many fields, so
thanks if you're up for checking it out!

I have two tables that are linked with a many to many relationship using a
third table.
TblZones (with fields ZoneNo(primary key, autonumber), ZoneMo, Zone
Description, CustNo, SiteNo, etc)
TblDevices (with fields DeviceNo (primary key, autonumber), DeviceType,
Location, CustNo, SiteNo, etc)
TblDeviceZoning (With fields ID (primary key, autonumber), DeviceNo, and
ZoneNo)

A device may only be assigned to a zone that shares the same customer
number
and site number. Each customer and site number combination has around 4
zones, and from 5-500 devices.

Normally, a person will use a form to enter all of the devices for a site,
separately enter all of the zones, and then go back to assign devices to
zones (possibly each at a different time). Each zone will have many
devices,
and each device may have one or many zones.

Currently, I'm trying to design a form to assign the devices to zones (or
zones to devices), and am looking to make it as straight forward as
possible,
hopefully with users not even knowing the PK DeviceNo and ZoneNo (you can
tell them apart basically from the other fields, but that uses
descriptions
and other fields that can change).

My current form idea, though I'm at a loss at how to make it work:
Open a form to select the SiteNo and CustNo. Run a query on this form to
select all of the applicable zones (this ranges from 1-5). Run a query to
select all of the applicable devices (5-200 devices). (This first part I'm
all set with.) In my dream world I open a form that has all of the
devices
on continuous form, the descriptions of the applicable zones in the
header,
and check boxes (one for each applicable zone, ie 1-5 depending on the
number
of zones) next to the devices to select the zones for each device. I'd
also
like to select a zone for groups of devices at one time. Perhaps by
filtering them and then clicking a button to select a zone for all of the
visible devices. I have no problem having the check boxes unbound and
then
clicking an update button to program everything into the TblDeviceZoning
table.

Does anyone have any ideas of how to proceed with this? Or have ideas of
a
user-friendly way to join many to many fields?

Thank you so very much!
~Claire

Thank you Jeff!

I had to pause for a moment to figure out the combo box, which just meant
changing the bound text box field to a listbox, but it's now all set. Now to
work on some of the data entry so that I can test out more forms and
reports...

Thanks!
~Claire


"Jeff Boyce" wrote:

Claire

A classic standard approach is to use a main form for one of your tables,
and a subform for your junction table.

In the subform, use a combobox to list the second table items, and make sure
your subform points to the junction table, and includes the foreign key from
both the first table and (via the combobox) the second table.

Now open your main form in design view, add the subform. Open the property
window for your subform control and use the primary key on the table filling
your main form as the "parent" property, and that foreign key from the
subform as your "child" property.

Now, when ever you have a main form record displayed, any entry you make on
the subform (e.g., selecting a ... Device) gives that record the foreign key
of the main form's record (i.e., ZoneID).

Good Luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Claire" wrote in message
...
Hi all,

This is a doozy of a problem, using a form to join many to many fields, so
thanks if you're up for checking it out!

I have two tables that are linked with a many to many relationship using a
third table.
TblZones (with fields ZoneNo(primary key, autonumber), ZoneMo, Zone
Description, CustNo, SiteNo, etc)
TblDevices (with fields DeviceNo (primary key, autonumber), DeviceType,
Location, CustNo, SiteNo, etc)
TblDeviceZoning (With fields ID (primary key, autonumber), DeviceNo, and
ZoneNo)

A device may only be assigned to a zone that shares the same customer
number
and site number. Each customer and site number combination has around 4
zones, and from 5-500 devices.

Normally, a person will use a form to enter all of the devices for a site,
separately enter all of the zones, and then go back to assign devices to
zones (possibly each at a different time). Each zone will have many
devices,
and each device may have one or many zones.

Currently, I'm trying to design a form to assign the devices to zones (or
zones to devices), and am looking to make it as straight forward as
possible,
hopefully with users not even knowing the PK DeviceNo and ZoneNo (you can
tell them apart basically from the other fields, but that uses
descriptions
and other fields that can change).

My current form idea, though I'm at a loss at how to make it work:
Open a form to select the SiteNo and CustNo. Run a query on this form to
select all of the applicable zones (this ranges from 1-5). Run a query to
select all of the applicable devices (5-200 devices). (This first part I'm
all set with.) In my dream world I open a form that has all of the
devices
on continuous form, the descriptions of the applicable zones in the
header,
and check boxes (one for each applicable zone, ie 1-5 depending on the
number
of zones) next to the devices to select the zones for each device. I'd
also
like to select a zone for groups of devices at one time. Perhaps by
filtering them and then clicking a button to select a zone for all of the
visible devices. I have no problem having the check boxes unbound and
then
clicking an update button to program everything into the TblDeviceZoning
table.

Does anyone have any ideas of how to proceed with this? Or have ideas of
a
user-friendly way to join many to many fields?

Thank you so very much!
~Claire


.