Use Records in Listbox to add new record to table

[brharrii]

Hi forum, I'm using access 2010.

I want to put some code behind a button that will allow the user to add selected products in a listbox to which ever operation is selected in a combobox by creating records for each product / operation combination in a third table:

tblProducts
tblOperations

tblOperationProductMM
- ProductID
- OperationID

I have a form with a combobox on top that allows the user to select the operation for which they want to add products. There is also a listbox that displays all records in tblProducts. The user can select one or more products and then the idea is that they can then press a button that will use the selected record IDs from the list box and the record ID from the combo box to create new records in the many to many table.

Can anyone help get me started on this?

Thanks!

Bruce

 

[pbaldy]

Like this?

http://www.baldyweb.com/MultiselectAppend.htm

 

[brharrii]

I think that's exactly what I'm looking for, Thank you Mr. Baldy

 

[pbaldy]

Happy to help!

 

[brharrii]

One more question: do you have any suggestions for preventing users from being able to select a product once it's already been been added to an operation to prevent duplicate records from being created?

I currently have the operationID / ProductID combo set as a primary key so the user does get an error message if they try to add a duplicate product, but I wanted to try to go a step further and remove the product entirely from the listbox if it's already associated with the operation selected in the combobox.

I've been toying around with it a little bit but I think the query is more complex than I'm fully understanding. I've tried two things:

This one doesn't return any records at all:

SELECT tblProducts.ProductID, tblProducts.ProductNumber, tblSpecies.SpeciesMarketName, tblProducts.ProductDesc
FROM tblSpecies RIGHT JOIN (tblProducts LEFT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblSpecies.SpeciesID = tblProducts.ProductSpecies
WHERE (((tblOperationProductMM.OperationIDMM)<>[Forms]![frmOperationProducts]![cboOperation]));

And this one removes Products from the list box for all opearations when the product is added to any Operation at all. In other words, Adding a product to operation 1 makes it unavailable to operation 2.

SELECT tblProducts.ProductID, tblProducts.ProductNumber, tblSpecies.SpeciesMarketName, tblProducts.ProductDesc
FROM tblSpecies RIGHT JOIN (tblProducts LEFT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblSpecies.SpeciesID = tblProducts.ProductSpecies
WHERE (((tblOperationProductMM.OperationIDMM) Is Null));

 

[brharrii]

oh also, that sample database worked perfectly. It was clear and simple to deconstruct and make work for my database. Thank you very much