What to do with multi select list box SQL??

[Guest]

Hi,

I have two list boxes in a main form that I want to feed the conditions of a
query which is part of a subform (on the main form). I want multiple
selections available for both list boxes and have read several threads
explaining that this needs to be done by using VBA code which ends up
creating an SQL string with the correct conditions.

What I don't understand is what I do with this string? How do I get the
current query in my subform to adopt the new SQL string as it's source?? A
lot of the threads talk about reports, but this is for a query, not a report.

Many thanks in advance!

 

[Guest]

You will need to assign the SQL statement to the RecordSource property of the
subform and then requery it. To build the WHERE clause you itereate through
the ItemsSelected collections of the list boxes and build value lists in
code. The IN operator can then be used with the value lists.

You don't say how the selections from the two list boxes interrelate, so for
this example I'm assuming that you want the subform to return rows where one
field matches any selection in one list box AND another field matches any
selection in the other. If you want rows returned where one field matches
any selection in one list box OR another field matches any selection in the
other then just change the Boolean AND operator in the code to an OR. The
code would go something like this and would most likely be called from a
button on the main form. To illustrate how different data types are handled
one list box (lstSupplierID) is of numbers, the other (lstProducts) of text
items:

Dim varItem As Variant
Dim strSQL As String
Dim strSupplierIDList As String
Dim strProductList As String
Dim ctrl As Control
Dim frm As Form

Set ctrl = Me.lstSupplierID

' iterate through suppliers list box's ItemsSelected collection
' and build value list of numbers
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSupplierIDList = strSupplierIDList & "," &
ctrl.ItemData(varItem)
Next varItem
' remove leading comma
strSupplierIDList = Mid(strSupplierIDList, 2)
End If

Set ctrl = Me.lstProducts

' iterate through suppliers list box's ItemsSelected collection
' and build value list of text values
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strProductList = strProductList & ",""" & ctrl.ItemData(varItem)
& """"
Next varItem
' remove leading comma
strProductList = Mid(strProductList, 2)
End If

' build SQL string
strSQL = "SELECT * FROM MyTable"
If Len(strSupplierIDList) > 0 Then
strSQL = strSQL & " WHERE SupplierID " & _
"IN(" & strSupplierIDList & ")"
End If

If Len(strSupplierIDList) > 0 Then
If Len(strProductList) > 0 Then
strSQL = strSQL & _
" AND Product IN(" & strProductList & ")" ' change this
line if

' a Boolean OR is required
End If
Else
If Len(strProductList) > 0 Then
strSQL = strSQL & _
" WHERE Product IN(" & strProductList & ")"
End If
End If

' add order by clause to sort subfrom by product
strSQL = strSQL & " ORDER BY Product"

' assign SQL statement to subform's RecordSource
' property and requery subform
Set frm = Me.MySubformControl.Form
frm.RecordSource = strSQL
frm.Requery

Note that MySubformControl here is the name of the subform control in the
main form's Controls collection, i.e. the control which houses the subform.

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

Assuming you've created the new SQL in a variable named strSQL, you can use
something like:

CurrentDb().QueryDefs("NameOfQuery").SQL = strSQL

If your subform has a SQL string, as opposed to a query name, as its Record
Source, you can assign the new SQL as the RecordSource property of the form
being used as a subform:

Me!MySubformControl.Form.RecordSource = strSQL

Note that MySubformControl is the name of the control that holds the subform
on the parent form. That may or may not be the same as the name of the form
being used as a subform.

 

[Guest]

Thanks very much for the replies.

I've gone with the line:

CurrentDb().QueryDefs("NameOfQuery").SQL = strSQL

as that's exactly what I want it to do....simply change the SQL for my
query. The only problem I have now is that it changes the SQL but doesn't
seem to requery the query?? If I close and re-open the form it works, but my
other requeries work using this command. Any ideas???

Here's a simplified example of my code:

############################

Dim strSQL As String
Dim stDocName4 As String

strSQL = "SELECT DISTINCT tbl_Union_Sales.Month FROM tbl_Union_Sales WHERE
tbl_Union_Sales.Month = " & """Jan""" & ";"
CurrentDb().QueryDefs("Query1").SQL = strSQL

stDocName4 = "Subfrm_query"
DoCmd.Requery stDocName4 'Refresh Query1

############################

Ok, so this isn't the SQL, it's built using a list box, but this query
should select "Jan" as the Month from a list of Months. If I go back and
check the query it has changed (let's say before it wasn't selecting any
particular Month) and includes the WHERE clause to select "Jan". With other
subforms I then have to requery them if they change, but this doesn't seem to
have any affect here?
Obviously my Query is called "Query1" and my Subform is called "Subfrm_query".

Any help would be great. Thanks so much for your help!!

 

[Douglas J. Steele]

You're absolutely right: it doesn't requery unless you tell it to.

After you change the SQL, put:

Me!MySubformControl.Form.Requery

Note that MySubformControl is the name of the control that holds the subform
on the parent form. That may or may not be the same as the name of the form
being used as a subform.

 

[Guest]

Thanks for the reply. However it doesn't seem to like this. I'm not sure if
I'm using the right "MySubformControl" as the only thing I can think to use
here is the name of the subform which is "Subfrm_query". Therefore I've
tried it like this....

CurrentDb().QueryDefs("Query1").SQL = strSQL
Me!Subfrm_query.Form.Requery

The first line correctly alters the SQL of the query, but the second line
doesn't requery it?? However if I use this code.....

Set frm = Me.Subfrm_query.Form
frm.RecordSource = strSQL
frm.Requery

This works! The only issue I have here is that this doesn't alter the
query's SQL, rather it just creates a new embedded SQL for the form to use.
This is ok, but I would prefer to change the query (Query1) as it's easier to
monitor/change in the future.

I guess the control that holds my subform onto my form isn't the same name
of my subform (Subfrm_query). Therefore, how do I find out what it is?

Thanks in advance!

 

[Douglas J. Steele]

If the second one's working, then your subform control name must be
Subfrm_qry. To be sure, open the parent form in Design view, select the
control that holds the subform and look at its properties (specifically the
Name property).

The first syntax I show should work, assuming that the RecordSource for the
form being used as the subform really is "Query1".

Try:

CurrentDb().QueryDefs("Query1").SQL = strSQL
Me!Subfrm_query.Form.RecordSource = "Query1"

If that doesn't work, add the Me!Subfrm_query.Form.Requery line back in
after resetting the RecordSource.

 

[Guest]

This work's a treat, thanks very much for your help.

It seems like an oversight by microsft with list boxes in general I think.
The logic would be to build them in such a way they could simply be passed
into the query by using an in() statement in the query i.e. in
([Form1].[ListBox1].[itemselected]) or something like that. At least that's
what I think!

Thanks again!