Hi - I have a list on a form that you can select multiple values, and when btn pressed it copies them to a text field. Then the other button should open the query with the contents of that text field in the criteria. But when you run the query it won't read directly from the form, and it brings up no values. However, If you copy the string into your clipboard and paste that exact same string manually into the criteria field and run the report, it finds all the values no problem! I've attached the screenshots. I've been at this 5 hours now, and I can't figure out what i'm doing wrong! Has anybody come across this inability to get the value from the text box?
On the 'Form' screenshot btn 'Copy List' code puts the values into the correct string (see 'Cohort List' in Form screenshot) to run the query (adding IN() and the "s.):
On the 'Form' screenshot btn 'Copy List' code puts the values into the correct string (see 'Cohort List' in Form screenshot) to run the query (adding IN() and the "s.):
Code:
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer
iCount = 0
If Me!lstCohort.ItemsSelected.Count <> 0 Then
For Each oItem In Me!lstCohort.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!lstCohort.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = "In (" & """" & sTemp & """" & "," & """" & Me!lstCohort.ItemData(oItem) & """" & ")"
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
Me!txtCohortList.Value = sTemp