Multi select list box to unbound textbox

[Guest]

I have an unbound single form with many subforms.
On the footer of the single form I have an unbound multi-selectt listbox
displaying the different departments.
I'm trying to pass the values for each department into an unbound text box
that each subform is filtered by... ie. entering [1] or [1 or 2 or 3] or [1
or 4 or 6] into the text box.

How do I cycle through the listbox and send the value to a textbox?
any ideas?

 

[John Vinson]

I have an unbound single form with many subforms.
On the footer of the single form I have an unbound multi-selectt listbox
displaying the different departments.
I'm trying to pass the values for each department into an unbound text box
that each subform is filtered by... ie. entering [1] or [1 or 2 or 3] or [1
or 4 or 6] into the text box.

How do I cycle through the listbox and send the value to a textbox?
any ideas?

Well... you can put the text into the textbox, but if you try to use
the textbox as a parameter in a Query (or as a Master Link Field) you
may be disappointed. You can pass *values* as paramters, but you
cannot pass *operators*.

Just in case it might be useful anyway, this little function should
return the first column of the selected rows of a listbox:

Public Function LstToText(ctl As Control, _
Optional Delim as String = ",") As String
Dim varItem As Variant
LstToText = ""
For Each varItem in ctl.ItemsSelected
LstToText = LstToText & ctl.Column(0, varItem) & Delim
Next varItem
End Function

This can be used if desired to build up the IN() clause of a Query:
e.g.

strSQL = "SELECT * FROM tablename WHERE ID IN(" _
& LstToText(Me!lstIDs) & ");"
Me!subMySubform.Form.Recordsource = strSQL


John W. Vinson[MVP]

 

[Guest]

I'm tempted to get the first suggestion to work because when I type-in "[1 or
2]" everything filters just fine... if I can just get the result to place an
"or" between each number.
I'm a bit confused on how to call a public function. Does the code go in a
module and then I call that function in the afterupdate of the listbox? or
the textboxt?

This is what I'm thinking so far:

Module 1 contains:
Public Function ListToText(ctl As Control, Optional Delim As String = "OR ")
As String
Dim varItm As Variant
ListToText = ""
Set ctl = Forms!frmCalendar!DepartmentSelect
For Each varItm In ctl.ItemsSelected

ListToText = "[" & ListToText & ctl.Column(0, varItm) & Delim & "]"
Next varItm
End Function

DepartmentSelect (this is the mulit-select list box):
afterupdate()
me.departmentfilter = listtotext
end sub

DepartmentFilter (this is the textbox that filters each query)
afterupdate()
call requerydates
end sub

The result of ListToText isn't going anywhere and I'm sure its because I'm
not calling it right.

Thanks.

I have an unbound single form with many subforms.
On the footer of the single form I have an unbound multi-selectt listbox
displaying the different departments.
I'm trying to pass the values for each department into an unbound text box
that each subform is filtered by... ie. entering [1] or [1 or 2 or 3] or [1
or 4 or 6] into the text box.

How do I cycle through the listbox and send the value to a textbox?
any ideas?

Well... you can put the text into the textbox, but if you try to use
the textbox as a parameter in a Query (or as a Master Link Field) you
may be disappointed. You can pass *values* as paramters, but you
cannot pass *operators*.

Just in case it might be useful anyway, this little function should
return the first column of the selected rows of a listbox:

Public Function LstToText(ctl As Control, _
Optional Delim as String = ",") As String
Dim varItem As Variant
LstToText = ""
For Each varItem in ctl.ItemsSelected
LstToText = LstToText & ctl.Column(0, varItem) & Delim
Next varItem
End Function

This can be used if desired to build up the IN() clause of a Query:
e.g.

strSQL = "SELECT * FROM tablename WHERE ID IN(" _
& LstToText(Me!lstIDs) & ");"
Me!subMySubform.Form.Recordsource = strSQL


John W. Vinson[MVP]

 

[John Vinson]

I'm tempted to get the first suggestion to work because when I type-in "[1 or
2]" everything filters just fine... if I can just get the result to place an
"or" between each number.

Ah, but that temptation will lead you into a blind alley. When you
type the expression in, Access is actively interpreting what you type
and constructing the query in the background. If it works by passing
the code as a parameter I'll be very surprised.

I'm a bit confused on how to call a public function. Does the code go in a
module and then I call that function in the afterupdate of the listbox? or
the textboxt?

If it's going to work at all, which (again) I doubt, you can put the
function call right in the criteria line of the Query. Or you can put
code in the click event of a Button which you select when you've
finished selecting all the desired rows of the listbox, or in the
LostFocus event of the listbox, by just typing

=ListToText([DepartmentSelect], "OR ")

in the event line.

This is what I'm thinking so far:

Module 1 contains:
Public Function ListToText(ctl As Control, Optional Delim As String = "OR ")
As String

You can pass the Delim to the function as I wrote it -

=ListToText(Me!DepartmentSelect, "OR ")

That's why it's an optional parameter.

Dim varItm As Variant
ListToText = ""
Set ctl = Forms!frmCalendar!DepartmentSelect

Again... you don't need to have a separate function for each control,
if you pass the ctl argument when you call the function.

For Each varItm In ctl.ItemsSelected

ListToText = "[" & ListToText & ctl.Column(0, varItm) & Delim & "]"
Next varItm
End Function

DepartmentSelect (this is the mulit-select list box):
afterupdate()
me.departmentfilter = listtotext
end sub

DepartmentFilter (this is the textbox that filters each query)
afterupdate()
call requerydates
end sub

The result of ListToText isn't going anywhere and I'm sure its because I'm
not calling it right.

If you're going to try that, then in the button's Click event or the
listbox's AfterUpdate event put

Me!DepartmentFilter = ListToText(Me!DepartmentSelect, "OR ")


John W. Vinson[MVP]

 

[Guest]

Okay....I gave in...I guess I'm a little stubborn. I ended up applying the
code as a filter to each subform. Thanks for your help

I'm tempted to get the first suggestion to work because when I type-in "[1 or
2]" everything filters just fine... if I can just get the result to place an
"or" between each number.

Ah, but that temptation will lead you into a blind alley. When you
type the expression in, Access is actively interpreting what you type
and constructing the query in the background. If it works by passing
the code as a parameter I'll be very surprised.

I'm a bit confused on how to call a public function. Does the code go in a
module and then I call that function in the afterupdate of the listbox? or
the textboxt?

If it's going to work at all, which (again) I doubt, you can put the
function call right in the criteria line of the Query. Or you can put
code in the click event of a Button which you select when you've
finished selecting all the desired rows of the listbox, or in the
LostFocus event of the listbox, by just typing

=ListToText([DepartmentSelect], "OR ")

in the event line.

This is what I'm thinking so far:

Module 1 contains:
Public Function ListToText(ctl As Control, Optional Delim As String = "OR ")
As String

You can pass the Delim to the function as I wrote it -

=ListToText(Me!DepartmentSelect, "OR ")

That's why it's an optional parameter.

Dim varItm As Variant
ListToText = ""
Set ctl = Forms!frmCalendar!DepartmentSelect

Again... you don't need to have a separate function for each control,
if you pass the ctl argument when you call the function.

For Each varItm In ctl.ItemsSelected

ListToText = "[" & ListToText & ctl.Column(0, varItm) & Delim & "]"
Next varItm
End Function

DepartmentSelect (this is the mulit-select list box):
afterupdate()
me.departmentfilter = listtotext
end sub

DepartmentFilter (this is the textbox that filters each query)
afterupdate()
call requerydates
end sub

The result of ListToText isn't going anywhere and I'm sure its because I'm
not calling it right.

If you're going to try that, then in the button's Click event or the
listbox's AfterUpdate event put

Me!DepartmentFilter = ListToText(Me!DepartmentSelect, "OR ")


John W. Vinson[MVP]