List box multi-select

[JamesWB]

I've browsed this a lot, but can't see a solution that fits my need.

I have a Job Plan form containing a selector that chooses a system number from another table. I want to (inside the same form) look in a third table that ties system numbers to equipment items (one system has multiple items of equipment) and generate a list box displaying those equipment items. I think I know how to do that bit.

I then want the user to be able to multi-select from the list and somehow to save those selections against the Job Plan number.

Is the best way to do this to create a multi-value field in the Job Plan table and store them in there?

From what I read, the best way is to create a join table between Job Plans and Equipment Items, but I'm not quite sure how to connect the selected items in a list box to that - is there some sort of obvious way to do that?

 

[JamesWB]

I think the problem is you are thinking about this from the wrong angle. You are thinking about creating a job plan and then considering what needs to go in the plan. I think you should consider thinking about it from the perspective of the equipment or possibly the system, (although I haven't grasped what system represents).Assuming the "Job Plan" is the necessity to repair and maintain the equipment then thinking about it from the equipments point of view, a piece of equipment now has a date when it needs to be maintained. This date can control when the equipment is put into a "Job Plan"

Making another assumption lets assume it is more efficient to gather together all of the equipment that is in one particular system and send that equipment in for maintenance at the same time then your questions might be:-

Which of these items of equipment are within one month off requiring maintenance?
Which Systems contain these items of equipment?
Which systems are easiest to bring in to the processing plant?

Tables:-
tblSystem (1) >>> tblEquipment (many)

tblJobs (1) .... ????

That's actually very helpful, thank you. Part of my problem is that this is really just a document generating system, it doesn't really get used as a planning tool, other than peripherally.

I think I've solved the specific problem I was wrestling with on one level, but your solution does sound better nevertheless. When I have a little more time I will restructure I think.