Passing ItemData result from Listbox to a pull query

[Patfo]

Hello, I have been trying to pass the itemdata result from listbox List4 to the where condition of the query rather than having JohnDoe below. I tried with
& stLinkCriteriaAMG.

Option Compare Database
Option Explicit

Private Sub List4_Click()
On Error GoTo Err_List4_Click
Dim stLinkCriteriaAMG As String
Dim varItem As Variant
Dim ctl As Control
If (Me.List4.ItemsSelected.Count = 0 Or Me.List4.ItemsSelected.Count > 1) Then
MsgBox "Must select 1 Application Manager"
Me.List9.Enabled = False
Me.List9.Visible = False
Exit Sub
End If

Set ctl = Me.List4
For Each varItem In ctl.ItemsSelected
stLinkCriteriaAMG = stLinkCriteriaAMG & ctl.ItemData(varItem)
Next varItem

Me.List9.Enabled = True
Me.List9.RowSourceType = "Table/Query"
Me.List9.RowSource = Me.List9.RowSource = "SELECT [BOB App Governance].[App MAL Code], [BOB App Governance].[App Name]," & _
"[BOB App Governance].[Lifecycle State], [BOB App Governance].Category, " & _
"[BOB App Governance].[Application Governance delegate]" & _
"FROM [BOB App Governance]" & _
"WHERE (([BOB App Governance].[Application_Accepted]) IN ('YES','ONBOARDING'))" & _
" AND [BOB App Governance].[Application Governance delegate] = 'JohnDoe' " & _
"ORDER BY [BOB App Governance].[App MAL Code] ;"
Me.List9.Visible = True

Exit_List4_Click:
Exit Sub

Err_List4_Click:
MsgBox Err.Description
Resume Exit_List4_Click

End Sub

 

[pbaldy]

This is different, but note the use of In() and the comma between values in the loop. Also the delimiters for text values.

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

Also, I moved your thread out of the introductions forum.

 

[Patfo]

I believe i did what is recommended. Still doesn't work. Believe I placed the quote in right places. Could this be related to the string having a comma? for example : 'Doe, John')

Set ctl = Me.List4
For Each varItem In ctl.ItemsSelected
stLinkCriteriaAMG = stLinkCriteriaAMG & "'" & ctl.ItemData(varItem) & "',"
Next varItem
stLinkCriteriaAMG = Left(stLinkCriteriaAMG, Len(stLinkCriteriaAMG) - 1)

Me.List9.Enabled = True
Me.List9.RowSourceType = "Table/Query"
Me.List9.RowSource = Me.List9.RowSource = "SELECT [BOB App Governance].[App MAL Code], [BOB App Governance].[App Name]," & _
"[BOB App Governance].[Lifecycle State], [BOB App Governance].Category, " & _
"[BOB App Governance].[Application Governance delegate]" & _
"FROM [BOB App Governance]" & _
"WHERE (([BOB App Governance].[Application_Accepted]) IN ('YES','ONBOARDING'))" & _
" AND [BOB App Governance].[Application Governance delegate] IN ('" & stLinkCriteriaAMG & "')" & _
"ORDER BY [BOB App Governance].[App MAL Code] ;"
Me.List9.Visible = True

 

[pbaldy]

You're putting apostrophes around the values, which is appropriate for text values, but the you put another set in with the IN() clause.