return the number of selected items in a list box

J

Jarreman

Registered User.
Local time
Today, 13:47
Joined
Sep 24, 2010
Messages
12
Hi everyone,

I have a list box of names in a table, on the report i wish to count the number of names selected in from the list box. for instance i have 20 names to choose from, if i select 5 of them, i would like a text box on my report to show 5.

i have tried using =count(listboxfield) in the control source of the text box on the report but it is not working as expected.

cheers
Richard
 
Hi VbaInet,

thank-you for the reply but is this a line of vba code? if so i have no idea how/where to put this.

any help is much appreciated

cheers
 
I may have given you the incorrect method. So you put the following in the Control Source property of your textbox:
Code: 
=[COLOR=red]ListBoxName[/COLOR].ItemsSelected.Count
Amend the red bit.
 
hi again vbaInet,

I have changed it to =[Scout].[ItemsSelected].[Count], (the square brackets were added automatically), however the result it now displays is 0.

i am using Access 2010 if this helps

cheers
 
Okay, the probem is it won't change everytime you make a selection. If you want it change everytime you select or deselect a row then you need to put this in the After Update event (i.e. Code Builder) of your Listbox:
Code: 
Me.[COLOR=Red]TextboxNameHere[/COLOR] = Scout.ItemsSelected.Count
Amend the red bit.
 
Sorry vbaInet

i have added the code to the on open event and i get an error, the code i have is:

Option Compare Database
Private Sub Report_Open(Cancel As Integer)
=Me.Text62=Scout.ItemsSelected.Count
End Sub


Thanks for all your help so far and aplolgoies for being a pain.
 
In the code I gave you I'm pretty sure there wasn't a leading = sign.

Plus I did say the After Update event of your listbox. It just seems that you followed none of my instructions.
 
well spotted,

now i have a runtime error "you cannot assign a value to this object"

cheers
 
It sounds like the listbox is actually a LOOKUP defined at TABLE LEVEL or it is a multivalued field. (probably a combination of both)

So there is no ItemsSelected in that. It is just the number of records that exist in the underlying hidden system table for the multivalued field.

So a count using the field name and .Value should work if used in a query

Select Count([FieldName].Value) From TableNameHere

and a DCount Might be able to be used.

Me.Text62 = DCount("[FieldName].Value", "tableNameHere")

It might not either so you may have to create a saved query to use the select I showed and then reference it in the DCount instead of the table name.

And it would need to go in the Report's ON FORMAT event of the section the text box you want to display it is in.
 
It just dawned on me that this is for a report. In what section do you have the listbox and textbox?
 
hi all,

the report is based upon a table which includes a mutiselect lookup of another table (the names). the listbox and textbox are both in the detail section of the report. should have mentoned this earlier perhaps.

cheers
 
It looks like Bob has answered your question. See above.
 
Hi Bob,

Sorry but im not sure where to put the code. when it comes to programming i'm usless. :)

cheers
 
If you have another good read of Bob's post, it's mentioned there ;)
 
Hi Bob,

Still having trouble im afraid, here is the SQL from the query i have:

SELECT Register.Scout, Register.Date, Register.Activity, Count(Register.Scout) AS [Counter]
FROM Scout INNER JOIN Register ON Scout.ID = Register.Scout.Value
GROUP BY Register.Date, Register.Activity, Scout.LastName
ORDER BY Scout.LastName;


i cant find an onformat in the report.

The report is using the register table for the data not the query, if i use the query it repeats the data for each individual selected.

Thanks for allyour help
Richard
 
Jarreman said:
Hi Bob,

Still having trouble im afraid, here is the SQL from the query i have:

SELECT Register.Scout, Register.Date, Register.Activity, Count(Register.Scout) AS [Counter]
FROM Scout INNER JOIN Register ON Scout.ID = Register.Scout.Value
GROUP BY Register.Date, Register.Activity, Scout.LastName
ORDER BY Scout.LastName;


i cant find an onformat in the report.

The report is using the register table for the data not the query, if i use the query it repeats the data for each individual selected.

Thanks for allyour help
Richard
Click to expand...

First off, you need to select the section where the text box is. So if it is in the Detail Section, you select the detail section. Then you go to the properties dialog and you should then find, under the Events Tab, an ON FORMAT event. But you have to physically select the section first, or else it won't show up.
 
You must log in or register to reply here.

Similar threads

J
Count IIf Function in Report
2
Replies
22
Views
456
Pat Hartman
P
J
Display Selection of a List Box in a Text Box
2
Replies
29
Views
737
JithuAccess
J
S
Select next in drop down list
Replies
3
Views
247
Pat Hartman
P
K
Query to to exclude records with a date within last 4 months, and show records where most recent date is older than that
Replies
9
Views
175
kevin_maher
K
R
Conditional Formatting works only randomly
Replies
8
Views
79
Reno911
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom