Add records to two tables with a one to one and one to many

[Paranap]

Hi.

I have a database where I have a table with lessons in it showing a lesson ID, lesson name, description and author. I then have another table with with lessonID and training need. So when I want to add a new lesson i can easily add the one to one stuff in the first table but then for each lesson there will be a number of training needs, which is the bit I am struggling with. Any thoughts.
I have thought about doing it as two different forms so you add the new lesson which creates the lesson ID then it opens a new form with a list of all the training needs to choose from. The bit I'm not sure on is adding the results from the list and linking it to the last created lesson ID.

Neil

[pbaldy]

Typically a one-to-many relationship is handled by a form bound to the "one' table with a subform bound to the "many" table. Master/child links keep them in sync with each other. You would add a record for each training need in the subform.

[Paranap]

Thank you. I did wonder about a sub Form. What I want to be able to do then is fill in the lesson details in the master form then select the training needs in a list in the child form click a command button and it to add one record in the lesson table and how every many records you have selected training needs in the lesson training needs table. Is that possible if so is there a macro or code to do that.

[pbaldy]

This kind of thing?

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

[Paranap]

Thats brilliant thank you. I ran that code with a seperate button to add all the items in the list and it worked a treat. When I tried to embed it into the same button and the save record for the lesson plan it seems to clear the selected items in the listbox. Is that because I haven't used a sub form or some other reason??

[Paranap]

Having a little play around to see what is happening and it would seem that when using

DoCmd.RunCommand acCmdSaveRecord

it clears the selected items in the list and so doesn't work and if I use an addnew rs.update code then it doesn't seem to add the record until I have closed the form down so the list still wont add. Any thoughts