473,372 Members | 1,050 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Looping thru a list box

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
2 16013
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: sparks | last post by:
I was going to loop thru the tables and change some properties. 10 <<<<<< prp.value --prp.name = Type False <<<<prp.value --prp.name = allow zero length 10 <<<<<< prp.value --prp.name = Type...
1
by: u473 | last post by:
I am still a beginner with VBA and I need the VBA Code for the following problem. I have a list of activities with their Scheduled Start date, Duration, and Working hours in Table1. I need to...
5
by: Craig G | last post by:
how do i go about this thru serverside code (VB.NET)? any links to any articles anywhere? basically i just want something simple that will loop thru all txt & cbo server side controls, and then...
2
by: Michael Beck | last post by:
I am new to ASP.net. All my work so far has been in VB ans Access. I want to dynamically popluate a list box with four columns of data. Only one column is the value. This is what I have tried...
16
by: kaferro | last post by:
What is the typical way to loop through a vector while deleting certain elements during the loop process? The code below works, but I am wondering if there is a better solution. ...
1
by: pds79 | last post by:
Hi everyone, I'm a newbie to the forum. I have an issue and was hoping to get some assistance/ideas: Im trying to read a XML file into two record sets. I can acheive looping through the...
1
by: tshad | last post by:
I was trying to do this in Javascript but found I can't do spans (which is what Labels turn into). I can do a loop thru a control list something like: Public Sub LoopingControls(ByVal oControl...
6
by: mahowe | last post by:
Hi, I have had this problem for a while and have not been able solve it. What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission...
4
by: sparks | last post by:
well I am trying to get around looking at all the buttons with a case statement so I was trying this. Public Sub looking(ctlname) Dim ctl As OptionGroup Dim btn As ToggleButton ctl =...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.