List Box in many to many relationship

[griffen]

I'm trying to implement a list box that populates a link table.

Tables:
- Product
- ProductLink
- Attributes

The listbox is a multi select. When I select a value from the listbox it should insert attributeID and productID into ProductLink. When a value is removed the row should be removed from ProductLink. How do I go about achieving this?

Both attributeID and ProductID are primary keys. At the moment it's trying to insert attributeID but failing as it is trying to insert a NULL value into ProductID which is required.

Any help that you can provide me would be greatly appreciated.

Thanks

 

[MarkK]

My 2 cents is you'll have a much easier time doing this with a subfrom; either a datasheet or a continuous form. Put a combo on each of the foreign key ID fields in the link table so they show meaningful data. Then you can very easily add and delete the links.

 

[griffen]

Hey lagbolt. Thanks for your help. I've done the subform thing already and I'm just not happy with the format the data is presented to users.

I thought this should be relatively easy but obviously not.

In droping a listbox onto the main Form I'm assuming the following:
- The control is bound to ProductID in the product table.
- The Row Source is ProductID and AttributeID from the ProductLink table

I'm a little stuck in:
1. Getting the AttributeName to appear in the listbox
2. Updating the ProductLink table to include the Product ID. At the moment it just seems to insert AttributeID and then tries to insert a blank value into ProductID in the Product table.

I'm obviously doing this wrong. Does anyone have any other suggestions?

 

[GeoMetry]

I am having trouble picturing what you are doing. It seems like you would have two list boxes, one for the product and one for the attributes. After you have made your selections in each listbox then click a button and the code would loop thru all the possible combinations and add records to the link table. What am I missing?

 

[MarkK]

You can't bind to a multi-select listbox. It's value property is always null. Not sure I understand.

 

[griffen]

Yeah sorry I should have been a bit clearer.

At the moment I'm wanting to come up with a better widget for users to be able to select multiple attributes against a product.

3 tables:

Product --------<ProductLink>----------Attribute

That's the relationship.

I've got a product form and I want to be able to select multiple attributes. I thought I could use a listbox with multiselect but it didn't work. Any suggestions?