Stuck on form design and appropiate control to use

[Repent]

I have a current DB with forms that I've created and so far, for what it does, it's fine. I need to add to a form and I'm stuck on how I need to create/modify existing tables and exactly what control to drive it all.

I want to add a combo/listbox to the form. the data in the first combobox will represent equipment. the listbox under the combobox on the form should list applicable problems with said equipment. There are about 12 or so pieces of equipment (equipment) and a total of about 60 things that can be wrong (equipmentproblem) with the equipment. As the user fills out the form and comes to the equipment combobox, they need to select each piece of equipment, one at a time, and select the appropiate problems that equipment had. Some equipment will not have any problems and some will have multiple problems.

I currently have a table that the users populate via a form. These additions I want to make relate to those same records and will be placed on the same form they are using now.

I've created a combobox that populates a listbox and I can select a piece of equipment in the first box and the appropiate equipment problems show up in the second box. I found examples on this site and modified them to my needs. Other than that nothing else happens. The table is not updated and I know that's because nothing else is setup. Not sure how, really. I'm not even sure that I like the idea of a combobox populating a listbox because then how does the user select the next item in the combobox, to show the resulting items in the listbox, and have the previous selections recorded. ALso, how does the user go back to check their answers before saving/going to the next blank record. How then are queries done if I want to do a report of what equipment had what problems?

Currently I have:
form name=frmProductionNumbers
table=tblProductionNumbers
combobox-cbocategories
listbox=lstoptions

I'm more than ready to scrap what I've done so far.

Can someone please assist me? If necessary, I can sanitize my current DB and upload it, if the site will allow me to.

This is my first project with Access period. I'm a CIO/director/network engineer/Exchange admin/Citrix admin/help desk/whatever else is needed, just not Access or VBA programmer.

Being able to create forms driven DB's for my company though will get alot of mileage as we do alot of custom stuff.

thanks all;

chris

 

[Repent]

Bump for thoughts on this?

 

[wazz]

I want to add a combo/listbox to the form. the data in the first combobox will represent equipment. the listbox under the combobox on the form should list applicable problems with said equipment. There are about 12 or so pieces of equipment (equipment) and a total of about 60 things that can be wrong (equipmentproblem) with the equipment. As the user fills out the form and comes to the equipment combobox, they need to select each piece of equipment, one at a time, and select the appropiate problems that equipment had. Some equipment will not have any problems and some will have multiple problems.

hopefully at this point you have at least two tables, tblEquipment and tblEquipmentProblem (or other names). since a piece of equipment may have one or more problems, and a problem may apply to more than one piece of equipment, you need a third table. consider naming the tables something like:

tblEquipment
tblEquipmentProblem
tblProblem
(some people use plural (tblProblems), some people use singular. doesn't matter.)

the relationships would be:
- a piece of equipment may have many problems;
- a problem may apply to one or more pieces of equipment.

[U]tblEquipment[/U] (1) --> (many) [U]tblEquipmentProblem[/U] (many) <-- (1) [U]tblProblem[/U]
EquipmentID                 EquipmentID                        ProblemID
etc.                        ProblemID                          etc.

i think what you're looking for is a subform, not a listbox. main form represents equipment, subform represents EquipmentProblem(s). for each piece of equipment (each record on the main form), you can select one or more problems on the subform (ProblemID, from tblEquipmentProblem). the ProblemID you select on the subform will look up the 'available' problems in tblProblem.

 

[Repent]

Wazz;

I've created this so far:

A table called "tblEquipment" with a primary key called "EquipmentID" and listed all the equipment equipment, each as a separate field.

I also created a table called "tblProblems" with a primary key called "ProblemID" and listed all the problems available, each with a separate field.

I then created a table called "tblEquipmentProblems" and it has an autonumber primary key plus two fields named "ProblemID and EquipmentID".

I created a 1-many relationship with EquipmentID from table "tblEquipment" linked to "EquipmentID" in table "tblEquipmentProblem" and also a link from "ProblemID" in table "tblProblems" to "ProblemID" in table "tblEquipmentProblem".

Could you explain a little more about the next steps?

chris

hopefully at this point you have at least two tables, tblEquipment and tblEquipmentProblem (or other names). since a piece of equipment may have one or more problems, and a problem may apply to more than one piece of equipment, you need a third table. consider naming the tables something like:

tblEquipment
tblEquipmentProblem
tblProblem
(some people use plural (tblProblems), some people use singular. doesn't matter.)

the relationships would be:
- a piece of equipment may have many problems;
- a problem may apply to one or more pieces of equipment.

[U]tblEquipment[/U] (1) --> (many) [U]tblEquipmentProblem[/U] (many) <-- (1) [U]tblProblem[/U]
EquipmentID                 EquipmentID                        ProblemID
etc.                        ProblemID                          etc.

i think what you're looking for is a subform, not a listbox. main form represents equipment, subform represents EquipmentProblem(s). for each piece of equipment (each record on the main form), you can select one or more problems on the subform (ProblemID, from tblEquipmentProblem). the ProblemID you select on the subform will look up the 'available' problems in tblProblem.