Multiselect listbox

[oxicottin]

Hello, I have a completed database and after reviewing and messing with it a lot and trying different scenarios I think I should have a multiselect listbox instead of a combo box on my main form. My question is right now I save my selection my table (tbl_Hold) field (ProductID) and the record source is of the combo box is:

SELECT [tbluProductDefects].[DefectID], [tbluProductDefects].[Defect] FROM tbluProductDefects ORDER BY [Defect];

I want to be able to save all the selected items I choose and when I open the form I want to be able to see the items that I choose in the listbox. What do I have to do to the table and the form?


Thanks,
SoggyCashew.....

 

[oxicottin]

Majp, sorry for not getting back I had a death in the family.... Now back to the DB I cant figure out how to get the Concatenate added into the query. I played with it all night and im lost...! I want to show the tbluProductDefects Defect all in a row with the comas like the Function by Duane you posted. Duane if your there maybe you can help?

SELECT tbl_Hold.HoldID, tbl_Hold.EntryDate, tbl_Products.ProductID, tbl_Products.Product, tbl_Hold.Length, tbl_Hold.DescreteJob, tbluMachines.Machine, tbluProductDefects.Defect
FROM tbluProductDefects INNER JOIN (tbluMachines INNER JOIN (tbl_Products INNER JOIN (tbl_Hold INNER JOIN tbl_HoldProdDefects ON tbl_Hold.HoldID = tbl_HoldProdDefects.HoldID) ON tbl_Products.ProductID = tbl_Hold.ProductID) ON tbluMachines.MachineID = tbl_Hold.MachineID) ON tbluProductDefects.DefectID = tbl_HoldProdDefects.DefectID
GROUP BY tbl_Hold.HoldID, tbl_Hold.EntryDate, tbl_Products.ProductID, tbl_Products.Product, tbl_Hold.Length, tbl_Hold.DescreteJob, tbluMachines.Machine, tbluProductDefects.Defect
ORDER BY tbl_Hold.EntryDate DESC;

Thanks,
SoggyCashew.....

 

[dhookom]

Create a query with this SQL view:

SELECT tbl_Hold.HoldID, tbl_Hold.EntryDate, tbl_Products.ProductID, tbl_Products.Product, 
tbl_Hold.Length, tbl_Hold.DescreteJob, tbluMachines.Machine, 
Concatenate("SELECT Defect FROM tbl_HoldProdDefects HPD INNER JOIN tbluProductDefects PD ON HPD.DefectID = PD.DefectID 
  WHERE HPD.HoldID = " & [HoldID]) AS Defects
FROM tbluMachines INNER JOIN (tbl_Products INNER JOIN tbl_Hold ON tbl_Products.ProductID = tbl_Hold.ProductID) 
ON tbluMachines.MachineID = tbl_Hold.MachineID
ORDER BY tbl_Hold.EntryDate DESC;

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Thank You....

Thanks,
SoggyCashew.....