By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,474 Members | 966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,474 IT Pros & Developers. It's quick & easy.

Looping thru a list box

P: n/a
pob
Whats the difference between using a control or a listbox when looping
thru a listbox. In example 1 it dims a listbox and an example 2 it
dims a control. Please explain. Thanks in advance

Example 1 from Allen Browne MVP Access

To use a Multiselect list box for criteria for a report,
you need to loop through its ItemsSelected collection to
create a string that can act as the WhereCondition for
your report.

Here's the basic idea:
---------------------------
Dim sWhere as String ' Where condition
Dim lst as Listbox ' multiselect list box
Dim vItem as Variant ' items in listbox
Dim iLen as Integer ' length of string.

Set lst = Me!lstSource
'loop through all items in listbox
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
sWhere = sWhere & "(SourceID = """ & lst.ItemData(vItem) & """) OR
"
End If
Next

iLen = Len(sWhere) - 4 ' Without trailing " OR ".
If iLen 0 Then
sWhere = "(" & Left$(sWhere, iLen) & ")"
End If
DoCmd.OpenReport "MyReport", acViewPreview, , sWhere
set lst = nothing

Example 2 - From MS Access help

Dim frm As Form, ctl As Control Dim varItm As Variant
Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm
End Sub

Jan 27 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
In general, you want to Dim your variables as tightly as possible. Therefore
the Listbox is better (more specific) than Control (more generic), but
Control would be better than the much more generic Object.

One advantage is that Access is able to show you a list of the relevant
properties (Intellisense, drop-downs in code) and methods that apply to
ListBox. If you just use Control, it can't be as helpful. Similarly, if you
Dim As Listbox, and you refer to a property that it doesn't have that some
other controls do (such as the DropDown method of a combo, or the Picture
property of an Image control), Access gives you a compile error. Anything
that helps you write good, tight, debuggable code is worthwhile.

There are occasions where you want to declare something as Object, e.g. for
late binding (for non-specific references), or when handling properties that
exist in later versions but you need the code to run in earlier versions as
well. But these are rare. More common is the need for the Variant: although
it's very broad, you need to use that type when handling fields (as in the
examples you posted), since other types cannot handle Nulls.

The rule of thumb is declare your variables with the most specific type, the
narrowest scope, and the shortest lifetime that copes with all possible
situations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"pob" <po*******@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...
Whats the difference between using a control or a listbox when looping
thru a listbox. In example 1 it dims a listbox and an example 2 it
dims a control. Please explain. Thanks in advance

Example 1 from Allen Browne MVP Access

To use a Multiselect list box for criteria for a report,
you need to loop through its ItemsSelected collection to
create a string that can act as the WhereCondition for
your report.

Here's the basic idea:
---------------------------
Dim sWhere as String ' Where condition
Dim lst as Listbox ' multiselect list box
Dim vItem as Variant ' items in listbox
Dim iLen as Integer ' length of string.

Set lst = Me!lstSource
'loop through all items in listbox
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
sWhere = sWhere & "(SourceID = """ & lst.ItemData(vItem) & """) OR
"
End If
Next

iLen = Len(sWhere) - 4 ' Without trailing " OR ".
If iLen 0 Then
sWhere = "(" & Left$(sWhere, iLen) & ")"
End If
DoCmd.OpenReport "MyReport", acViewPreview, , sWhere
set lst = nothing

Example 2 - From MS Access help

Dim frm As Form, ctl As Control Dim varItm As Variant
Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm
End Sub
Jan 27 '07 #2

P: n/a
pob
That helps quite a bit. Thanks for the detailed explanation !

POB

On Jan 27, 12:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
In general, you want to Dim your variables as tightly as possible. Therefore
the Listbox is better (more specific) than Control (more generic), but
Control would be better than the much more generic Object.

One advantage is that Access is able to show you a list of the relevant
properties (Intellisense, drop-downs in code) and methods that apply to
ListBox. If you just use Control, it can't be as helpful. Similarly, if you
Dim As Listbox, and you refer to a property that it doesn't have that some
other controls do (such as the DropDown method of a combo, or the Picture
property of an Image control), Access gives you a compile error. Anything
that helps you write good, tight, debuggable code is worthwhile.

There are occasions where you want to declare something as Object, e.g. for
late binding (for non-specific references), or when handling properties that
exist in later versions but you need the code to run in earlier versions as
well. But these are rare. More common is the need for the Variant: although
it's very broad, you need to use that type when handling fields (as in the
examples you posted), since other types cannot handle Nulls.

The rule of thumb is declare your variables with the most specific type, the
narrowest scope, and the shortest lifetime that copes with all possible
situations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"pob" <pobnos...@gmail.comwrote in messagenews:11**********************@m58g2000cwm.g ooglegroups.com...
Whats the difference between using a control or a listbox when looping
thru a listbox. In example 1 it dims a listbox and an example 2 it
dims a control. Please explain. Thanks in advance
Example 1 from Allen Browne MVP Access
To use a Multiselect list box for criteria for a report,
you need to loop through its ItemsSelected collection to
create a string that can act as the WhereCondition for
your report.
Here's the basic idea:
---------------------------
Dim sWhere as String ' Where condition
Dim lst as Listbox ' multiselect list box
Dim vItem as Variant ' items in listbox
Dim iLen as Integer ' length of string.
Set lst = Me!lstSource
'loop through all items in listbox
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
sWhere = sWhere & "(SourceID = """ & lst.ItemData(vItem) & """) OR
"
End If
Next
iLen = Len(sWhere) - 4 ' Without trailing " OR ".
If iLen 0 Then
sWhere = "(" & Left$(sWhere, iLen) & ")"
End If
DoCmd.OpenReport "MyReport", acViewPreview, , sWhere
set lst = nothing
Example 2 - From MS Access help
Dim frm As Form, ctl As Control Dim varItm As Variant
Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm
End Sub
Jan 27 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.