Select multiple records in a subform and link them to a parent item

[TheCyrusVirus]

I have a product table (tblProd which has ProdID, Name, Description, price)

I have an attributes table (tblAtt which has AttId, Description)

I then have a join table (tblProdAtt which has ProdAttId, prodID, AttId) which links the products to the attributes.

I also have a form which shows the product details and has the attributes that relate to this product as a subform.

What I am after is an easy way for the end user to see a list of all of the attributes and be able to select multiple records and link them to the product.

Ideally I wanted a pop up form where they can just click a checkbox and it would then insert the id of the parent product and the selected attribute(s) in the tblProdAtt table.

I am comfortable with VBA (although my experience is mostly limited to excel vba) so any suggestions are welcome on a neat way to achieve this.

Hope that all makes sense

Thanks in advance

 

[stopher]

Hi and welcome to the forums

Here's how I'd do it....

I'd add another field to your attributes table (yes/no) to be used to identify which attributes are to be selected. This field will only be holding temporary info while the user selects some attributes.

Then I'd create a form base on the attributes table such that the user can tick some records. In the On Open event for the form you need to run a query to set the selection field for all records to false. Also on the form you need a button to run another query to copy the selected records to your join table (and presumably close the form). This event should also refresh your main for (otherwise you still won't see the new records). This form can be a pop-up type if desired.

On your main form you need an event to load the attributes selection form e.g. a button (On Click).

Does that make sense?

Chris

 

[TheCyrusVirus]

Perfect, sounds like a plan. I will give this a go this afternoon.

Should I maybe run the reset of the yes/no values after the query to insert the records. Or will the form onload event run when I change between records on the parent form?

 

[voltzwagon]

This seems to be right in line with what I am trying to do in Access but I do not have the VBA experience to put it into action. Could you post the VBA you used to do this?