trying to get Filter on Subform to work with Multiselect in Listbo

G

Gator

Most of the code here is just FYI....my question really gets to the
TabCtl0_Change() event. The Filter works well with the Multiselect set as
None. How would I adjust the code to make the Filter work with Multiselect
set at Simple?

thanks

Private Sub Frame17_Click()
If Frame17.Value = 1 Then
List15.RowSource = "SELECT Format((DateDep),'yyyymm'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyymm'),
Account ORDER BY Format((DateDep),'yyyymm') DESC;"
Else
List15.RowSource = "SELECT Format((DateDep),'yyyy'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyy'),
Account ORDER BY Format((DateDep),'yyyy') DESC;"

End If
End Sub

Private Sub List11_Click()
List13.RowSource = "SELECT Account, AccountName, Fund FROM Deposits WHERE
(Fund= '" & List11 & "') GROUP BY Account, AccountName, Fund;"

End Sub
Private Sub List13_Click()
If Frame17.Value = 1 Then
List15.RowSource = "SELECT Format((DateDep),'yyyymm'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyymm'),
Account ORDER BY Format((DateDep),'yyyymm') DESC;"
Else
List15.RowSource = "SELECT Format((DateDep),'yyyy'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyy'),
Account ORDER BY Format((DateDep),'yyyy') DESC;"

End If
End Sub


Private Sub TabCtl0_Change()
Dim strFilter As String
With Me.TabCtl0
strFilter = "(Deposits.Account='" & List13 & "') AND (Deposits.Fund='" &
List11 & "') AND ((Year(Deposits.DateDep))='" & (Left(List15.Column(0), 4)) &
"') AND ((Month(Deposits.DateDep))='" & (Right(List15.Column(0), 2)) & "')"
End With

With Me.Deposits_subform.Form
..Filter = strFilter
..FilterOn = True
..OrderBy = "DateDep DESC"
..OrderByOn = True
End With
End Sub
 
D

Dale Fye

Gator,

With multi-select set to something other than "None", you will have to loop
through the ItemsSelected collection of the listbox, and concatenate the
values. In your case, it looks like the bound field of your list is text, so
you will have to wrap the values in quotes. I have a function (not currently
available, so the following is air code) that I use in these instances. The
function looks something like:

Public Function fnMultiList(lst as listbox, _
Optional WrapWith as String = "'") as
variant

Dim varItem as Variant

fnMultiList = NULL
if lst.ItemsSelected.count = 0 then Exit function

for each varItem in lst.itemsselected

fnMultiList = (fnMultiList + ",") _
& WrapWith _
& lst.column(lst.boundcolumn-1, varItem) _
& WrapWith
Next

if instr(fnMultiList, ",") = 0 then
fnMultiList = "= " & fnMultiList
Else
fnMultiList = " IN (" & fnMultiList & ")"
endif

End Function

This code will return a NULL value if no items are selected (I usually set
this up so that the query doesn't include that field in the SQL if no items
are selected.

Otherwise, it will return an equal sign followed by a single value (wrapped
by whatever you used as your "WrapWith" value. If the bound column in your
list is numeric, then pass the function an empty string ("").

If there is more than one item selected, it will return an IN ( ) clause,
that looks something like: IN ('value1', 'value2')

Assuming that list 11 is the one you want to change into a multi-select, the
way you would use this in your code would be something like:

strSQL = "SELECT Format([DateDep], 'yyyymm'), " _
& "Format(Sum(Amount), 'Currency'), " _
& "Account " _
& "FROM Deposits " _
& "WHERE Account = '" & me.list13 & "'" _
& (" AND [Fund] " + fnMultiList(me.list11)) _
& " GROUP BY Format([DateDep], 'yyyymm'), _
& "[Account] " _
& "ORDER BY Format([DateDep], 'yyyymm') Desc"
me.list15.RowSource = strsql

By wrapping the line after the WHERE in ( ) and using the + to concatenate
the items you ensure that if no items are selected in list11, then the [Fund]
field will not be included in the query.

Since this is untested (air code), you might want to test it before you
implement it.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
G

Gator

What I'm after is Multiselect in List15 and Filter Deposits_subform records
accordingly.

thanks

Dale Fye said:
Gator,

With multi-select set to something other than "None", you will have to loop
through the ItemsSelected collection of the listbox, and concatenate the
values. In your case, it looks like the bound field of your list is text, so
you will have to wrap the values in quotes. I have a function (not currently
available, so the following is air code) that I use in these instances. The
function looks something like:

Public Function fnMultiList(lst as listbox, _
Optional WrapWith as String = "'") as
variant

Dim varItem as Variant

fnMultiList = NULL
if lst.ItemsSelected.count = 0 then Exit function

for each varItem in lst.itemsselected

fnMultiList = (fnMultiList + ",") _
& WrapWith _
& lst.column(lst.boundcolumn-1, varItem) _
& WrapWith
Next

if instr(fnMultiList, ",") = 0 then
fnMultiList = "= " & fnMultiList
Else
fnMultiList = " IN (" & fnMultiList & ")"
endif

End Function

This code will return a NULL value if no items are selected (I usually set
this up so that the query doesn't include that field in the SQL if no items
are selected.

Otherwise, it will return an equal sign followed by a single value (wrapped
by whatever you used as your "WrapWith" value. If the bound column in your
list is numeric, then pass the function an empty string ("").

If there is more than one item selected, it will return an IN ( ) clause,
that looks something like: IN ('value1', 'value2')

Assuming that list 11 is the one you want to change into a multi-select, the
way you would use this in your code would be something like:

strSQL = "SELECT Format([DateDep], 'yyyymm'), " _
& "Format(Sum(Amount), 'Currency'), " _
& "Account " _
& "FROM Deposits " _
& "WHERE Account = '" & me.list13 & "'" _
& (" AND [Fund] " + fnMultiList(me.list11)) _
& " GROUP BY Format([DateDep], 'yyyymm'), _
& "[Account] " _
& "ORDER BY Format([DateDep], 'yyyymm') Desc"
me.list15.RowSource = strsql

By wrapping the line after the WHERE in ( ) and using the + to concatenate
the items you ensure that if no items are selected in list11, then the [Fund]
field will not be included in the query.

Since this is untested (air code), you might want to test it before you
implement it.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Gator said:
Most of the code here is just FYI....my question really gets to the
TabCtl0_Change() event. The Filter works well with the Multiselect set as
None. How would I adjust the code to make the Filter work with Multiselect
set at Simple?

thanks

Private Sub Frame17_Click()
If Frame17.Value = 1 Then
List15.RowSource = "SELECT Format((DateDep),'yyyymm'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyymm'),
Account ORDER BY Format((DateDep),'yyyymm') DESC;"
Else
List15.RowSource = "SELECT Format((DateDep),'yyyy'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyy'),
Account ORDER BY Format((DateDep),'yyyy') DESC;"

End If
End Sub

Private Sub List11_Click()
List13.RowSource = "SELECT Account, AccountName, Fund FROM Deposits WHERE
(Fund= '" & List11 & "') GROUP BY Account, AccountName, Fund;"

End Sub
Private Sub List13_Click()
If Frame17.Value = 1 Then
List15.RowSource = "SELECT Format((DateDep),'yyyymm'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyymm'),
Account ORDER BY Format((DateDep),'yyyymm') DESC;"
Else
List15.RowSource = "SELECT Format((DateDep),'yyyy'),
Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyy'),
Account ORDER BY Format((DateDep),'yyyy') DESC;"

End If
End Sub


Private Sub TabCtl0_Change()
Dim strFilter As String
With Me.TabCtl0
strFilter = "(Deposits.Account='" & List13 & "') AND (Deposits.Fund='" &
List11 & "') AND ((Year(Deposits.DateDep))='" & (Left(List15.Column(0), 4)) &
"') AND ((Month(Deposits.DateDep))='" & (Right(List15.Column(0), 2)) & "')"
End With

With Me.Deposits_subform.Form
.Filter = strFilter
.FilterOn = True
.OrderBy = "DateDep DESC"
.OrderByOn = True
End With
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

expected: end of statement error on DatePart 1
can't get SELECT to work 12
why won't date sort by date and not by number or text 4
Help with Currency Format 3
trying to work with table data 2
Repopulating form with combo boxes in subform 1
Filter by form with beginning date and enddate, where calender we 2
Noob VB code, Need help with Passing multiSelect Listbox 11

Top