Update the subform or refresh based on multiple selection in a lis

  • Thread starter Thread starter kris
  • Start date Start date
K

kris

I have a list box with multiple seletion set to "simple".Based on the
selection made i build the query using the following code

Function s()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms![ROC-Home1]
Set ctl = frm![control-list]

strSQL = "SELECT Table3.* FROM Table3 WHERE [Controls]="

For Each varItem In ctl.ItemsSelected
If ctl.ItemData(varItem) = "(All)" Then 'added
strSQL = "SELECT Table3.* FROM Table3 OR [Controls]=" 'added
Exit For
End If
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR [Controls]="
Next varItem

'Trim the end of strSQL
strSQL = Left(strSQL, Len(strSQL) - Len(" OR [Controls]="))
strSQL = strSQL & ";"
CurrentDb.QueryDefs("Query3").SQL = strSQL
DoCmd.Requery ""
End Function

The query is built fine and is correct.
I have a subform to which the source-object is query3.
When i change the seletion in the listbox and run the code(with the help of
a macro)
the subform data doesnt change even though the query has been changed.
I can see the changes when i close the form and reopen it.
I need a way to sync the subform with changes done in the listbox and
running the function s().
 
The list box is an unbound listbox.
I tried this statement for requery
Forms![ROC-Home1]![Query3 subform].Form.Requery but it dint work.
 
Back
Top