URGENT multi-select list box help! (1 Viewer)

[Manic_Rach]

Hey guys,

On the database I'm working on, the company has a list of material codes. For every job that comes into the company they need to add what materials will be needed for the job. As it wasn't user friendly to have 20 different material code fields on a form I have used a multi-select list box with the material codes in. However, each individual material code needs a quantity.

Is there a way of displaying it so that the quantities can be selected for the material codes selected in the multi-select list box? (and preferably keeping it on the same page)

 

[boblarson]

A subform instead of multiselect list box should be used for this as then the applicable materials can be selected and the quantities added.

 

[Manic_Rach]

Okay.

The subform will be on "tbl_newjob" and the material codes are stored in "tbl_materials"
If im going to use the new job form to enter the quantity needed, which table will the "quantity needed" field be in tbl_newjob? or tbl_materials?

 

[boblarson]

Okay.

The subform will be on "tbl_newjob" and the material codes are stored in "tbl_materials"
If im going to use the new job form to enter the quantity needed, which table will the "quantity needed" field be in tbl_newjob? or tbl_materials?

It would not be in either. It would be in a junction table like this:

tblJobsMaterials
JobsMaterialsID - Autonumber (PK)
JobID - Long Integer (FK - ID field from tbl_newjob)
MaterialID - Long Integer (FK - ID field from tblMaterials)
Qty - Long Integer


And this would be a subform on the job form.

By the way, you have tbl_newjob so I'm wondering if you have another table for job information as well. If so, that would make me believe your table structure might need some fixing (normalizing) before going further.

 

[Manic_Rach]

yeah, i think it does need normalising. i'll look into it. thanks for your help