Multiselection Listbox Insert into SQL

[thr33xx]

Hello everyone,

I have a form which contains a single multiselection listbox. The listbox houses values of different facilities. I would like users to be able to select all applicable facilities pertaining to that record (could be 1 or more), and then click a button which would insert the record into a SQL server.

Any ideas on how to make this happen (inserting selected values from the listbox into the SQL server)? I am still very much a beginner at using VBA and SQL. Any suggestions would be appreciated!

Thanks

 

[pbaldy]

Like this?

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

 

[thr33xx]

Yes!

Like that, but in SQL. How would I make this happen to record to a SQL server?

Like this?

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

 

[pbaldy]

A recordset can be opened on SQL Server tables.

 

[thr33xx]

Alright, I've been at this for over a week trying to get the code pbaldy provided to work with SQL, unfortunately, I'm not getting it. Attached below is the code I currently have. I would like to add the function of a multi selection listbox which will add to a second table all selections in the listbox, and record the selected study_id. Any help would be appreciated.

Private Sub Add_New_Study_Click()
Dim cn As ADODB.Connection
Set cn = CurrentProject.AccessConnection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
Dim sql As String

sql = "insert into dbo_Setup "
sql = sql & "(study_id, coor_id_last, coor_id_first, study_phase, dt_first_subject_enrolled, dt_exp_enroll_complete, exp_num_mon_enroll, recruiting, dt_irb_approval, dt_go_live, pi, calculate, tot_target_enroll, comments) "
sql = sql & "Values "
sql = sql & "('" & Me.Add_Study_ID & "'," _
& IIf(IsNull(Me.Add_Coor_ID_Last), "NULL", "'" & Me.Add_Coor_ID_Last & "'") & ", " _
& IIf(IsNull(Me.Add_Coor_ID_First), "NULL", "'" & Me.Add_Coor_ID_First & "'") & ", " _
& IIf(IsNull(Me.Add_Study_Phase), "NULL", "'" & Me.Add_Study_Phase & "'") & ", " _
& IIf(IsNull(Me.Add_Dt_First_Subject_Enrolled), "NULL", "'" & Me.Add_Dt_First_Subject_Enrolled & "'") & ", " _
& IIf(IsNull(Me.Add_Dt_Exp_Enroll_Complete), "null", "'" & Me.Add_Dt_Exp_Enroll_Complete & "'") & ", " _
& IIf(IsNull(Me.Add_Exp_Num_Mon_Enroll), "null", "'" & Me.Add_Exp_Num_Mon_Enroll & "'") & ", " _
& IIf(IsNull(Me.Add_Recruiting), "null", "'" & Me.Add_Recruiting & "'") & ", " _
& IIf(IsNull(Me.Add_Dt_IRB_Approval), "null", "'" & Me.Add_Dt_IRB_Approval & "'") & ", " _
& IIf(IsNull(Me.Add_Dt_Go_Live), "null", "'" & Me.Add_Dt_Go_Live & "'") & ", " _
& IIf(IsNull(Me.Add_PI), "null", "'" & Me.Add_PI & "'") & ", " _
& IIf(IsNull(Me.add_cbo_calculate), "null", "'" & Me.add_cbo_calculate & "'") & ", " _
& IIf(IsNull(Me.Add_Tot_Target_Enroll), "null", "'" & Me.Add_Tot_Target_Enroll & "'") & ", " _
& IIf(IsNull(Me.Add_Setup_Comments), "null", "'" & Me.Add_Setup_Comments & "'") & ") "


MsgBox ("Hello")
MsgBox sql

cn.Execute sql

Me.Requery

Me.Add_Study_ID = Null
Me.Add_Coor_ID_Last = Null
Me.Add_Coor_ID_First = Null
Me.Add_Study_Phase = Null
Me.Add_Dt_First_Subject_Enrolled = Null
Me.Add_Dt_Exp_Enroll_Complete = Null
Me.Add_Exp_Num_Mon_Enroll = Null
Me.Add_Recruiting = Null
Me.Add_Fac_ID = Null
Me.Add_Dt_IRB_Approval = Null
Me.Add_Dt_Go_Live = Null
Me.Add_PI = Null
Me.add_cbo_calculate = Null
Me.Add_Tot_Target_Enroll = Null
Me.Add_Setup_Comments = Null

Me.Add_Study_ID.SetFocus

Me.Refresh

Exit_AddRecord_Click:
    Exit Sub

 

[pbaldy]

A sample db demonstrating your specific situation would make more sense. Basically you would wrap the part from

sql = "insert into dbo_Setup "

through

cn.Execute sql

inside a loop like in my link, and get the value(s) from the listbox.