Create table entries from form for each month selected (One line per month)

[mattodeez]

I created a form that has three fields; Name, Project, Month



The goal of the form is to create a table where if I pick a person and a project, i want to pick what months they are working on a project.

The table where the info is to be added has only three columns for Name, Project, and Month as well.

The issue that I'm having is I want to be able to select multiple months and create a unique entry for each month. When I make that list an "extended" or "simple" for MultiSelect, it only creates one entry for the table. If I choose Matt, Project A and January, February, March.... I would need to create three lines in the table, one for each month (with Name and Project the same values in all three lines). I can't figure out how to do this.

Also, if I have the month list set to "extended" or "Simple" for multiselect, when I choose more than one month, nothing shows up in the table (Table name is "WhatIf").

Here is my Event Procedure for the click

Private Sub cmdAdd_Click ()
'add data to the table

CurrentDB.Execute "INSERT INTO WhatIf(Name, Project, Month) VALUES ('" & Me.txtname & "','" & Me.txtproject & "','" & Me.listMonth & "')"

'refresh data in subform

frmWhatIfSub.Form.Requery

End Sub

[pbaldy]

How about:

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

[mattodeez]

How about:

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

I was not able to figure out how to use your sql in my db. I think I am a little confused since employees is the thing that will have multiple unique values, while in mine, it will be the month that is unique to each line. Also, being a rookie, I don't have all the same things defined at the top of my VBA and not sure how they would translate.

I attached my project. The form I am working with is "frmWhatIfMain" with the reference tables clrearly labeled.

Side question...I have one person with an apostraphe in their name and it breaks the system even when I only try to add one month. Any ideas how to fix that?Online Sample1.zip

[pbaldy]

I'm using a different method. If you want to stay with yours, you still have to loop the selected items of the listbox and use the value in your SQL inside that loop.

To get around the apostrophe, use Chr(34) as discussed here:

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