VBA Code to dynamically select rows from a list box and insert into SQL

I have a complex code question.
I did have the list box (LstProvNPI) as a single select only that worked flawlessly.  
Once a user selected a row, the data would populate fields within in my form.  
Once the record was saved a SQL insert statement would update the record back to tables in SQL.

The problem is now the user would like to multi-select, I do not know how to do that.  
I can no longer have the Dynamic list box populate fields.  What is the best way to do this?

Step 1:
When a member ID is searched from a form menu, the LoadMember Function is loaded when my form opens
Step 2:
The Load Member will display a list of choices in a unbound list box.
Step 3:
The on LstProvNPI_Click() property within the list box allows the user to select the entry and this will populate the fields on the form.
Step 4:
When a user clicks save, A call module will call a function to Insert date from all the fields on the form to SQL.
Step 1:
Function loadMember(HID As String)
Dim server      As New SQLServer
Dim rs          As ADODB.Recordset
Dim SSQL        As String
Dim n           As Long
n = 0

If Len(HID) = 9 Then
    HID = HID & "00"
End If

Call server.initializeToRSMS(False, False, True)
    SSQL = ""
    SSQL = SSQL & "SELECT DISTINCT HumanaID, " & vbCrLf
    SSQL = SSQL & "         [Last_Name] + ', ' + [First_Name]   AS [Member Name], " & vbCrLf
    SSQL = SSQL & "         Last_Name, " & vbCrLf
    SSQL = SSQL & "         First_Name, " & vbCrLf
    SSQL = SSQL & "         Phone, " & vbCrLf
    SSQL = SSQL & "         Language " & vbCrLf
    SSQL = SSQL & "FROM PS.Poly_Campaign_Member " & vbCrLf
    SSQL = SSQL & "WHERE HumanaID = '" & HID & "' " & vbCrLf
    SSQL = SSQL & "ORDER BY HumanaID"
    Set rs = server.runSelectSQL(SSQL)
If rs.EOF Then
    MsgBox "Member not found. Check HumanaID"
    Exit Function
End If

HumanaID = rs.Fields("HumanaID")
Last_Name = rs.Fields("Last_Name")
First_Name = rs.Fields("First_Name")
ConsultID = HumanaID & "_" & Consult_Date
Language = rs.Fields("Language")
Phone = rs.Fields("Phone")
txtLabel = rs.Fields("label_name")
txtServiceDate = rs.Fields("service_date")

'Used in Provider List Box
SSQL = ""
SSQL = SSQL & "SELECT ps.Poly_Campaign_Member.id, " & vbCrLf
SSQL = SSQL & "       ps.Poly_Campaign_Member.Humanaid  AS HumanaID, " & vbCrLf
SSQL = SSQL & "       ps.Poly_Campaign_Medications.provider_npi  AS NPI, " & vbCrLf
SSQL = SSQL & "       campaigns.Provider.DEA  AS DEA, " & vbCrLf
SSQL = SSQL & "       ps.Poly_Campaign_Medications.Measure, " & vbCrLf
SSQL = SSQL & "       ps.Poly_Campaign_Medications.pharmacy_name AS Pharmacy, " & vbCrLf
SSQL = SSQL & "       ps.Poly_Campaign_Medications.label_name AS [Medication], " & vbCrLf
SSQL = SSQL & "       ps.Poly_Campaign_Medications.service_date  AS [Date Filled], " & vbCrLf
SSQL = SSQL & "       campaigns.Provider.Provider_Name AS [Provider Last Name,  First Name] " & vbCrLf
SSQL = SSQL & "FROM   ps.Poly_Campaign_Member " & vbCrLf
SSQL = SSQL & "       LEFT JOIN ps.Poly_Campaign_Medications " & vbCrLf
SSQL = SSQL & "            ON ps.Poly_Campaign_Member.humanaid = ps.Poly_Campaign_Medications.humanaid " & vbCrLf
SSQL = SSQL & "       LEFT JOIN campaigns.Provider " & vbCrLf
SSQL = SSQL & "            ON ps.Poly_Campaign_Medications.Provider_NPI = campaigns.Provider.NPI " & vbCrLf
SSQL = SSQL & "WHERE ps.Poly_Campaign_Member.HumanaID = '" & HID & "' " & vbCrLf
Set rs = server.runSelectSQL(SSQL)
Call addRSToList(LstProvNPI, rs, True)

'Checks to see if Consult ID exists and will increment final integer if exists
    Do While ConsultIDExists(HumanaID & "_" & Consult_Date & "-" & n) = True
        n = n + 1
    ConsultID = HumanaID & "_" & Consult_Date & "-" & n
End Function

Open in new window

Step 3:
Private Sub LstProvNPI_Click()
Dim server  As New SQLServer
Dim SSQL    As String
Dim rs      As ADODB.Recordset
Call server.initializeToRSMS(False, False, True)

SSQL = ""
SSQL = SSQL & "SELECT Provider_Name, DEA,[Measure],label_name, service_date  FROM [Rightsource_MS].[campaigns].[Provider] CP LEFT JOIN ps.Poly_Campaign_Medications CM" & vbCrLf
SSQL = SSQL & "ON CP.NPI = CM.Provider_NPI WHERE [NPI] = '" & PNPI & "'"
Set rs = server.runSelectSQL(SSQL)
If rs.EOF Then
PName = "N/A"
PName = rs.Fields(0)
PDEA = rs.Fields(1)
txtLabel = rs.Fields(2)
txtServiceDate = rs.Fields(3)
End If

End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I am not sure what is the issue...
You just set the listbox to multiselect...
You iterate the Listbox
Dim itm As Variant
    For Each itm In YourListBox.ItemsSelected
       ' Now here you refactor your code to accept itm as an argument
    Next itm 

Open in new window

you pick each of the selected value...you feed the function you use....
gracie1972Author Commented:
What do you mean exactly?  I did change the unbound type to multi select but it did not work, the problem is that it is unbound, I have to tell it where to do once I select.
Before when I selected the line  the data would automatically populate the fields below:
If rs.EOF Then
PName = "N/A"
PName = rs.Fields(0)
PDEA = rs.Fields(1)
txtLabel = rs.Fields(2)
txtServiceDate = rs.Fields(3)
End If

Then I had a Call module that would (Snippet)
        SSQL = SSQL & "INSERT INTO PS.Poly_Campaign_Consult VALUES ('"
        SSQL = SSQL & frm.First_Name & "','"
        SSQL = SSQL & frm.Last_Name & "','"
        SSQL = SSQL & frm.PName & "','"
        SSQL = SSQL & frm.PNPI & "','"
        SSQL = SSQL & frm.PDEA & "','"
Based on those values, now I cannot use this code, there might be more than one line and therefore when I select more than one the data would have to write back to SQL and cannot write to the fields anymore.  This is beyond my capabilities.
John TsioumprisSoftware & Systems EngineerCommented:
Well if its the iteration that's causing the issue just take a look at my attachment
gracie1972Author Commented:
This makes sense, in your code, would I replace the SQL Field Name with Item Data?  I think I am understanding what you are saying....
MsgBox Me.List0.ItemData(itm) & " -- " & Me.List0.Column(1, itm)

For my SQL Field "NPI" which is column 2 in my list box: then DEA which is 3, etc?

MsgBox Me.LstProvNPI.NPI(itm) & " -- " & Me.LstProvNPI.Column(2, itm)
MsgBox Me.LstProvNPI.DEA(itm) & " -- " & Me.LstProvNPI.Column(3, itm)
MsgBox Me.LstProvNPI.Label(itm) & " -- " & Me.LstProvNPI.Column(8, itm)
End Sub
What is the reason that the selected record is displayed?  If it is to allow the user to modify data before inserting to SQL Server, then it makes no sense to work with more than one record at a time.  If the function is simply copying data from one table to another (I won't ask why), then there is no need to stop in the middle to display it.  Just use the suggested code loop to insert each row and don't display it first.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.