Full Version: A2002 ListCount bug
UtterAccess Forums > Microsoft® Access > Access Forms
I'm using Czech version of A2002 but, as far as I can remembre, it was
similar in A97.
I have a ListBox lstOrg with this properties:
.ColumnCount = 9
.ColumnHeads = False
.ColumnWidths =
.RowSourceType = Table/Query
.RowSource =
... and a TextBox txtOrgCount:
.ControlSource = =[lstOrg].[ListCount]
lstOrg.RowSource will be generated leater according to user's interaction.
But, weird, empty lstOrg gives ListCount = 1!!! Combo-boxes behave the samwe
way. Is there any workaround? Am I doing something wrong?
Oknow there's a problem with ColumnHeads property, but it is set to False
(No)... :-/
Hi Vlado,
o you requery txtOrgCount after each user interaction setting the RowSource of lstOrg?
You migght want to add the requery code to the form's Current event too.
Hello again,
MHO, it doesn't have to do anything with requery. If I need, I always save record and requery an appropriate combo-box, but this si not "the case". This is an Access "combo-box-ColumnHeads" bug... :-(
Hi Vlado,

Hmmm.... I seem to remember coming across something like this before.

main post deleted due to typing without thinking - will post again in a minute


Edited by: cheekybuddha on Sat Jun 2 15:44:17 EDT 2007.
I think this is a problem only if you change the ColumnHeads property at Runtime (I just did some tests which have left me a bit confused).

If you don't plan to change the ColumnHeads property then you could use as the ControlSource of txtOrgCount:

IIf(Len(lstOrg.RowSource) > 0, lstOrg.ListCount + lstOrg.ColumnHeads, 0)

but you're probably better off using a DCount() on the listbox's source instead :(

Using A2002, I created a new form and added a blank listbox to it (rowsource type = TABLE/QUERY, but no rowsource). I also added a button to give me a MSGBOX with the LISTCOUNT.
o columnheads or any other properties were set; just a default listbox.
It gives me 1. If I ask it for ItemData(0), it's NULL.
When I change the rowsource type to VALUE LIST, it gives me 0. If I change it back to TABLE/QUERY and give it a query that will return zero rows, it also gives me zero. So, it's just the blank rowsource coupled with a type of TABLE/QUERY that does this.
This smells a little like a bug to me. Of course, you've told it you are giving it a table/query and then don't give it one, so it seems to be confused. Your workaround could be to leave the rowsource type as VALUE LIST (since you're not giving it anything, that seems to be closer to the truth, anyway), and then set the type to TABLE/QUERY at the same time that you set the ROWSOURCE property.
Thx for your input, cheekybuddha. I think DCount is the only way how to get correct results.
Yet another ListBox bug:
Have you ever tried to count selected items in multiselect ListBox?
ListBox.Name = lstTitul
TextBox.Name = VybranoTitul
VybranoTitul.ControlSource = [lstTitul].[ItemsSelected].[Count]
Now try:
1) select 11 records in the listbox (left-click and drag down)
2) press Ctrl and:
a) left-click on the 9th selected record
b) left-click on the 8th selected record
c) left-click on the 5th selected record
d) left-click on the 4th selected record
e) left-click on the 6th selected record !!! BUG !!! Should be 6 instead of 7!!!
If you'll play a little longer you can get what you see on ListBox2.jpg, see the attachment.
This bug remains in Access since 97... well, I started with A97 so it might have been even earlier.
ListBox3.jpg shows the situation after step 2e).
Thx for your response, Peter.
MO, it surely is a bug. Very old one.
No need to requery when you change the RowSource, it's done automatically wink.gif
Just tried this ... I cannot duplicate your results.
If you create a new form in a new DB and set up this test ... does it do it there? If not, it may be specific to something happening in your form. If it does it in any circumstance, it may be specific to your install of Access or your local country's version.
Combo-box needs requery in this situation:
- cbo.LimitToList = False
- cbo.RowSource is based on form's data source (no lookup table)
What do you mean 'based on the form's data source'?
That I was saying was that if you dynamically change the Rowsource of a combobox, or a form, or a subform for that matter by saying Me.ComboBoxName.RowSource = "Select ..." then you do not have to do a requery.
If you're adding records to a table and NOT changing the RowSource via VBA, then yes, you do need to do a Requery. From Cheeky's statement, the RowSource itself was being changed.
I've just tried it on a new form, see the attachment. Same result, ie. there IS a bug.
P.S. Thx for testing.
Yes, NoahP, I understand what you mean. In your case you dont have to requery the combo-box. But if I have a combo-box field which is not referenced to a lookup table I have to requery the combo-box on Form_Current (at least) so that it displays correctly.
No, it only confirms you are having a problem.

You seem to be very hung up on identifying "bugs" today. The software isn't perfect; no software is.

You are seeing an issue, so you are assuming it is a bug. I cannot duplicate the issue you are seeing, so I cannot corroborate, and am inclined to think it is a problem localized on your end (your app, your Access installation, your PC, etc.). One or both of us may be right.

I'm attaching a sample DB, created in A2002 (A2000 format); no matter what I do, I cannot get the count to be wrong on my machine. Let me know if you have issues with it, as well. Perhaps some others can join us in testing to confirm if anyone else can duplicate the problem.
Actually, what he's saying is that the following code:
nameofcombo.RowSource = "qryWhatever"
oes not ALSO require a:
after it. The act of setting the RowSource property, even if you set it to the same thing it already was, forces the combo to requery.
TYVM for the database, Peter. Same problem, ie. there must be u bug... no matter what it is caused by.
See the attachment.

I'd like to check out with you: did you follow my steps?


Edited by: Vladimir on Sun Jun 3 1:02:29 EDT 2007.
Interesting ... noted that the steps you followed were different from what you posted above:
However, from your screenshots, what you actually did was:
e) left-click on the 7th selected record
Following your original instructions, I could not duplicate the problem. However, I can duplicate the problem with that change.
Let me look at this one a little more...
Shame on me! crazy.gif
'd better go to bed, it's 7:15 am here and I was at UA overnight. sleeping.gif
Anyway, I'll wait for a little while to see if you find out what's going on.
More interesting ... I added a button to show me which items WERE selected.
When I un-select "John", it actually thinks "PETER" is selected (using the ITEMSSELECTED collection).
That's actually a worse issue than the count being wrong. Once it's messed up like this, it seems to go from bad to worse quickly. If I then select "PETER", the count is right and the items are right; if I unselect PETER, the count stays at 7 and it tells me PETER is still selected, even though (visually) it's not.
I will continue to play.
The problem seems to be confined to the ITEMSSELECTED collection ... if I loop through the SELECTED collection, I get the right count and values.
Confirmed it doesn't happen if the MULTI SELECT property is set to SIMPLE ... only with EXTENDED.
That's what you say in your siggy:
"In theory, there is no difference between theory and practice. In practice, there is."
YVM for testing and for giving us results. thanks.gif
Some GOOGLE searches turn up others who have discussed the problem. Some of the posts seemed to indicate it was fixed in A2000, but it's obviously back in A2002.
I'm in the process of rebuilding my dev box right now, so I only have access to A2002 at the moment.
My wife's PC has Office2003 on it ... I can confirm the issue exists in A2003.
And I can confirm for A97 with a little difference: you can see bad result even if you unclick last of the selected records. If you start with 11 selected records you may end up with none-selected in listbox but with value 6 in the textbox!
Yes, SIMPLE seems to perform OK.
OK, I am posting here a modified version of my app with testing instructions; I'd love to see if others are seeing the same things we are.
It has the following elements:
1) A multi-select (extended) list box linked to a table with 16 elements
2) A calculated control that shows the COUNT of the ITEMSSELECTED collection of the listbox
3) A button that will give you the COUNT and actual elements ITEMSSELECTED is reporting
4) A button that will give you a COUNT and actual elements, using the SELECTED collection
5) A button that will show you any differences between ITEMSSELECTED and SELECTED
In the case of #5, it's going to show you items in ITEMSSELECTED that actually (per the visual display and the SELECTED collection) aren't selected.
To test:
1) Click BOB on the list - the count should say 1
2) Holding SHIFT, click VICTOR - the count should say 11
3) Holding CTRL, click Susan - the count should say 10
4) Holding CTRL, click Peter - the count should say 9
5) Holding CTRL, click Greg - the count should say 8
6) Holding CTRL, click Fred - the count should say 7
7) Holding CTRL, click John - the count should say 6, but it says 7
8) Click the buttons; the first will show you 7 names are selected (incorrect); the second will show you 6 names are selected (correct); the last will show you that ITEMSSSELECTED thinks that Peter is selected (but it's not).
9) Now, holding CTRL, click Victor - the count should say 5, but it still says 7
10) Click DIFFERENCES; you will now see it thinks both Peter and Vladimir are selected (but they are not, and Vladimir has never been)
11) Click the second button; it gives the proper count (5) and items.
12) Now, holding CTRL, un-select the remaining names one at a time. The count will still say 2 when you are done, but nothing is selected! The DIFFERENCES button will show that the ITEMSSELECTED collection still thinks Peter and Vladimir are selected.
Can others confirm they are seeing the same thing? This looks like a serious issue to me, for any code relying on ITEMSSELECTED.
followed your instructions and got the same errors as you outlined.
I am using 2003.
I got the same in 2000 with Extended. I never use Extended though, only Simple and could find no problems there.
This is how I have worked around the .ListCount issue ... I have used this practice probably since A97, When I was learning, I started doing this because, I never thought of a ZLS as a Table/Query, so I just started setting each property when I reset tht list box...
When "Clearing" a List/Combo boxes recordsource ... This will get the .ListCount to ZERO when you expect it to be zero ...
Me.lstMyListBox.RowSourceType = "Value List"
Me.lstMyListBox.RowSource = ""
Me.lstMyListBox = Null
HAs stated, I started doing this because I did not even think a ZLS would be a valid rowsource
... Also ... sometimes I will set up the SQL Statement to return ZERO records, I figured since I was modifying the SQL with code anyway, why toggle the list box type? so I wind up setting the recordsource to something like this ...
Me.lstMyListBox.RowSource = "SELECT * FROM tblMyTable WHERE 1 = 0"
Me.lstMyListBox = Null
Both of the above methods will return a .ListCount of 0, when you expect it to. But note that IF you have the ColumnHeads property set to TRUE, the .ListCount will be "off by one" since the Headers are considered a row. But ALSO remember, in a Table/Query listbox, that if ZERO records are returned by the rowsource, the listcount will be 0, but then if the rowsource returns 1 RECORD, the Headers are invoked and thus create a ListCount of 2
... Getting sleepy now ... I must rest ... I will post more tommorrow regarding this topic.
OK, Peter has determind strange behaviour with a listbox set to Extended Multi-Select, but I am slightly more concerned with anomalies that Vladimir originally pointed out regarding .ListCount.
created a new form (Form1) and added a listbox (List0). I changed nothing in the property sheet - List0 is single-select, no columnheaders, no RowSource.
Othen performed the follwing operations in the Immediate window - I hope you can follow what I was trying to do (I added comments).
?Forms!Form1.list0.rowsourcetype    '   Check rowsource type
?Forms!Form1.list0.rowsource        '   No rowsource set yet
?Forms!Form1.list0.columnheads      '   Check ColumnHeads - none
?Forms!Form1.list0.listcount        '   Listcount should be 0?
1                                  '   Hmmm, returns 1
Forms!Form1.list0.Rowsource = "Select objID From tblObj Where objID = 3;"   '   Set rowsource to single record
?Forms!Form1.list0.RowSource        '   Check RowSource
Select objID From tblObj Where objID = 3;
?Forms!Form1.list0.ListCount        '   Check ListCount - should be 1
1                                  '   OK
Forms!Form1.list0.RowSource = "Select objID From tblObj Where 1=2;"         '   Set RowSource to return no records
?Forms!Form1.list0.RowSource        '   Check RowSource
Select objID From tblObj Where 1=2;
?Forms!Form1.list0.ListCount        '   Check ListCount - should be 0
0                                  '   OK
Forms!Form1.list0.RowSource = ""    '   Set RowSource to nothing
?Forms!Form1.list0.RowSource        '   Check RowSource
                                    '   OK
?Forms!Form1.list0.ListCount        '   Check ListCount - should be 0                  
1                                  '   [color="red"]Hmmm... it would appear that NO ROWSOURCE gives ListCount = 1 ??????[/color]
Forms!Form1.list0.ColumnHeads = True    '   Set ColumnHeads to TRUE - see if that makes any difference
?Forms!Form1.list0.ColumnHeads      '   Check it was set
True                                '   OK
?Forms!Form1.list0.ListCount        '   Should be 0 (or possibly 1, if it counts the ColumnHead)
1                                  '   So why 1? ColumnHead or bug?
?Forms!Form1.list0.RowSource        '   Double check RowSource
                                    '   Nothing, so why return a ListCount > 0?
Forms!Form1.list0.Rowsource = "Select objID From tblObj Where objID = 3;"   '   Set 1 record RowSource
?Forms!Form1.list0.RowSource        '   Check RowSource
Select objID From tblObj Where objID = 3;
?Forms!Form1.list0.ListCount        '   ListCount should return 1
2                                  '   Seems to have included the ColumnHead too - OK
Forms!Form1.list0.RowSource = "Select objID From tblObj Where 1=2;" '   Set rowsource to return no records
?Forms!Form1.list0.RowSource        '   Check RowSource
Select objID From tblObj Where 1=2;
?Forms!Form1.list0.ListCount        '   Expect 0, possibly 1 due to ColumnHead
1                                  '   OK - ish
Forms!Form1.list0.RowSource = ""    '   Remove RowSource
?Forms!Form1.list0.RowSource        '   Check
?Forms!Form1.list0.ListCount        '   Expect 0, but probaly 1 due to ColumnHead
Forms!Form1.list0.ColumnHeads = False   '   Remove ColumnHeads
?Forms!Form1.list0.ListCount        '   No RowSource set - surely should be 0?
1                                  '   [color="red"]Why return 1 ????????
The table I used has only 1 record in it (with PK = 3)
The possible bug is that it would seem that an empty listbox, with no ColumnHeads, no RowSource, no reason to have a ListCount > 0 returns a ListCount = 1.
Once a RowSource is set then the ListCount returns what one would expect (provided you expect to include the ColumnHead as a list item)
Anyone able to reproduce these results or perform better/more extensive tests?
ps - I'm using Acc2003
TYVM for testing and explaining your tests detail.
So, at least, there are two bugs in Access's ListBox:
1) incorrects ListCount when ColumnHeads = True
2) incorrect ItemsSelected collection and ListCount when MultiSelect = 2
Still more fun!!! (???)

A have just tried Peter's database in A2007 in a non-maximized main Access window (so that I could easily follow Peter's steps as described in his post of 06/03/07 08:27. After step 7) I have maximized Access' main window. And I could not believe my eyes!!! You'd better try yourself...

P.S. You don't have to maximize the window, just switch to another application and then switch back! Scary, isn't it? shocked.gif

See the attachment.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.