Hi! I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".
When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:
Here's the code I'm trying to use:
ANYBODY's got any ideas?
Greetings from Sweden
BJörn
'------------------ Code Start ------------------
Function cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tbl_Aktuella_BUar"
'Build the IN string by looping through the listbox
For i = 0 To Form4BU.List19.ListCount - 1
If Form4BU.List19.Selected(i) Then
If Form4BU.List19.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & Form4BU.List19.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [AktuellaBUar] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "ASSETS_SKANDIA_B/U"
Set qdef = MyDB.CreateQueryDef("ASSETS_SKANDIA_B/U", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "ASSETS_SKANDIA_B/U", acViewNormal
'Clear listbox selection after running query
For Each varItem In Form4BU.List19.ItemsSelected
Form4BU.List19.Selected(varItem) = False
Next varItem
Exit_cmdOpenQuery_Click:
Exit Function
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If
End Function
'------------------ Code End -------------------
When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:
Here's the code I'm trying to use:
ANYBODY's got any ideas?
Greetings from Sweden
BJörn
'------------------ Code Start ------------------
Function cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tbl_Aktuella_BUar"
'Build the IN string by looping through the listbox
For i = 0 To Form4BU.List19.ListCount - 1
If Form4BU.List19.Selected(i) Then
If Form4BU.List19.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & Form4BU.List19.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [AktuellaBUar] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "ASSETS_SKANDIA_B/U"
Set qdef = MyDB.CreateQueryDef("ASSETS_SKANDIA_B/U", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "ASSETS_SKANDIA_B/U", acViewNormal
'Clear listbox selection after running query
For Each varItem In Form4BU.List19.ItemsSelected
Form4BU.List19.Selected(varItem) = False
Next varItem
Exit_cmdOpenQuery_Click:
Exit Function
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If
End Function
'------------------ Code End -------------------