I have a multi select list box. I copied the corresponding code from a functioning database. It runs correctly in the new database until LOCNCODE. I then get an "input box" to enter the LOCNCODE. LOCNCODE should be pulled from the list box. I have tried removing the statement, but then the code doesn't complete because of commas. I have searched these boards and the internet to no avail. Any ideas?
PS This was originally written by someone here. Other than changing field names, I removed 2 Dim statements as they would not function in the new database (though they do in the other).
Code:
' if nothing is selected, display message and exit.
If lstDataType.ItemsSelected.Count = 0 Then
MsgBox "Select location(s) first"
Exit Sub
End If
Dim SQL As String
Dim sCriteria As String
Dim varItem As Variant
' loop through list box selections.
For Each varItem In lstDataType.ItemsSelected
sCriteria = sCriteria & ",'" & lstDataType.ItemData(varItem) & "'"
Next varItem
' build criteria string.
For Each varItem In Me.lstDataType.ItemsSelected
sLocation = sLocation & ",'" & Me.lstDataType.ItemData(varItem) & "'"
Next varItem
sLocation = Mid(sLocation, 2)
' remove leading comma.
sLocation = " [LOCNCODE] in (" & sLocation & ")"
' build SQL Statement.
SQL = " SELECT * " & _
" FROM qryResults " & _
" WHERE " & sLocation
Set db = CurrentDb
' delete query qryDataType if exists.
On Error Resume Next
db.QueryDefs.Delete "qrySlowMov"
On Error GoTo 0
' create and run query qryDataType.
Set qDef = db.CreateQueryDef("qrySlowMov", SQL)
DoCmd.OpenQuery "qrySlowMov"PS This was originally written by someone here. Other than changing field names, I removed 2 Dim statements as they would not function in the new database (though they do in the other).
Last edited by a moderator:
