one-to-many convert to form with multiple check boxes

I have a one-to-many relationship that I would like to convert to an
unbound checkbox or radio buttons within a form.

On form (pulls from tblEquipment) user can select one or all three
choices from table luCategory. Instead of putting a subform within
another subform, within another subform. Want to be able to put in
checkboxes for each of the 3 choices so that all could be selected,
but it would link to TblEquipmentCategory and luCategory to support
one-to-many relationship (one equipment entry can have many
categories).

TblEquipment
EquipID (PK)

TblEquipmentCategoryLink
CategoryID (PK)
EquipID (FK)
Category (Number àlook up table luCategory)

In luCategoryRef
CategoryID (PK)
Description (text = Equipment, Supplies or Property)

I hope I am describing this properly. I have heard it can be done in
visual basic, I am wondering how to do it in access.

Thx,

re: one-to-many convert to form with multiple check boxes

What's wrong with a subform? Are you trying to limit the number of
categories an item can belong to? Use (trying to remember) DCount in
the... BeforeInsert event of the subform. If you're over the limit,
cancel the insert...

Otherwise what you're saying is "I'd really like to denormalize my
database", and my response is "well, you'd better have a REALLY good
reason."

re: one-to-many convert to form with multiple check boxes

I am new to this stuff, so any ideas are greatly appreciated!

I don't want to de-normalize my one to many relationships in the access
database If I don't have to. I am just having trouble putting it
together on an access form.

Right now I have a listbox form inside a subform inside another subform
inside of a main form which is not working.

I have one main form

frmEquip
------------
EquipID (number - PK)
EquipCategoryID (Number)
Problem (text)

On the form a user can have multiple categories of equipment and
multiple owners of the equipment

tblEquipCategory (have a form with a listbox referencing
---------------- luEquipCategoryRef to insert in frmEquip)

EquipID (Number)- link to tblEquipment
EquipCategoryID (Number)- link to luEquipmentCategoryRef

luEquipCategoryRef
----------------------
EquipCategoryID (Number PK)
Category (text)

If in the main form for frmEquipment I have a listbox for the question
the category of equipment (subform) and set it up for them to choose
more than one (i.e. Equip, supplies, property), then in the form when I
test it I get a message saying "You cannot add or change a record
because a related record is required in tblEquipment".

I'm not sure what I am missing. Any suggestions?


*** Sent via Developersdex http://www.developersdex.com ***