Function to return number of items selected in listbox

[Guest]

I am trying to put together a query that will return only the records that
contain ALL of the criteria selected from a listbox rather than all of the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong direction...

I'm using a Find Duplicates Query and attempting to set the duplicate value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region], [Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to count
the number of selected criterion in the listbox. My most recent attempt
gives the error message "Object doesn't support this property or method" (It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Ken Snell \(MVP\)]

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

 

[Guest]

Access is crashing when I try to open the file...

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>

I am trying to put together a query that will return only the records that
contain ALL of the criteria selected from a listbox rather than all of the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong direction...

I'm using a Find Duplicates Query and attempting to set the duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to count
the number of selected criterion in the listbox. My most recent attempt
gives the error message "Object doesn't support this property or method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Ken Snell \(MVP\)]

ACCESS crashes when you try to open the .mdb file that is in the zip file?
More details, please. Did you download the version that matches the version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>

I am trying to put together a query that will return only the records
that
contain ALL of the criteria selected from a listbox rather than all of
the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong direction...

I'm using a Find Duplicates Query and attempting to set the duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Guest]

I'm using Access 2000 - I've tried to open it from the downloaded .zip file
as well as from the website. Each time I get "Microsoft Access for Windows
has encountered a problem and needs to close. We are sorry for the
inconvenience."

ACCESS crashes when you try to open the .mdb file that is in the zip file?
More details, please. Did you download the version that matches the version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>


I am trying to put together a query that will return only the records
that
contain ALL of the criteria selected from a listbox rather than all of
the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong direction...

I'm using a Find Duplicates Query and attempting to set the duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Guest]

I'm using Access 2000 - I've tried to open the .mdb from the .zip folder as
well as directly from the website. Each time I get the message "Microsoft
Access for Windows has encountered a problem and needs to close. We are
sorry for any inconvenience."

ACCESS crashes when you try to open the .mdb file that is in the zip file?
More details, please. Did you download the version that matches the version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>


I am trying to put together a query that will return only the records
that
contain ALL of the criteria selected from a listbox rather than all of
the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong direction...

I'm using a Find Duplicates Query and attempting to set the duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Guest]

I'm using Access 2000 - I've tried to open the file from the .zip folder as
well as directly from the website. Each time I get "Microsoft Access for
Windows has encountered a prolblem and needs to close. We are sorry for any
inconvenience."

ACCESS crashes when you try to open the .mdb file that is in the zip file?
More details, please. Did you download the version that matches the version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>


I am trying to put together a query that will return only the records
that
contain ALL of the criteria selected from a listbox rather than all of
the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong direction...

I'm using a Find Duplicates Query and attempting to set the duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Ken Snell \(MVP\)]

From the website, you download the zip file. Then, on your PC, extract the
..mdb file to a folder on your PC. Then try to open the database file that
you extracted.
--

Ken Snell
<MS ACCESS MVP>

I'm using Access 2000 - I've tried to open the file from the .zip folder
as
well as directly from the website. Each time I get "Microsoft Access for
Windows has encountered a prolblem and needs to close. We are sorry for
any
inconvenience."

ACCESS crashes when you try to open the .mdb file that is in the zip
file?
More details, please. Did you download the version that matches the
version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

:

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>


I am trying to put together a query that will return only the records
that
contain ALL of the criteria selected from a listbox rather than all
of
the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong
direction...

I'm using a Find Duplicates Query and attempting to set the
duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Guest]

Your sample only allows a single selection in the list box, I'm using a
multi-select list box & need to retrieve the results for all of the
selections made.

Right now, the results show all of the records with the selections, but I
need it only to show results that contain all of the selections from the list
box rather than those records that contain any one of the selections.

For example, if I select from my list box "ICS 100", "NIMS 800", and
"HazMat", I only want to return the names of employees that have had all
three of these trainings. Right now, those selections would return the names
of employees that have had any one of the three.

(I apologize for the multiple posts, the website said that the post could
not be processed and to try again later.)

From the website, you download the zip file. Then, on your PC, extract the
..mdb file to a folder on your PC. Then try to open the database file that
you extracted.
--

Ken Snell
<MS ACCESS MVP>

I'm using Access 2000 - I've tried to open the file from the .zip folder
as
well as directly from the website. Each time I get "Microsoft Access for
Windows has encountered a prolblem and needs to close. We are sorry for
any
inconvenience."

ACCESS crashes when you try to open the .mdb file that is in the zip
file?
More details, please. Did you download the version that matches the
version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

:

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>


I am trying to put together a query that will return only the records
that
contain ALL of the criteria selected from a listbox rather than all
of
the
records that contain ANY of the criteria selected. I've tried a few
different approaches & may still be working in the wrong
direction...

I'm using a Find Duplicates Query and attempting to set the
duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Ken Snell \(MVP\)]

The listbox in the sample database does indeed allow multiple selections.
Hold down the Ctrl key when you click on another item in the listbox. ACCESS
listboxes require use of Ctrl and/or Shift key to make multiple selections.

--

Ken Snell
<MS ACCESS MVP>

Your sample only allows a single selection in the list box, I'm using a
multi-select list box & need to retrieve the results for all of the
selections made.

Right now, the results show all of the records with the selections, but I
need it only to show results that contain all of the selections from the
list
box rather than those records that contain any one of the selections.

For example, if I select from my list box "ICS 100", "NIMS 800", and
"HazMat", I only want to return the names of employees that have had all
three of these trainings. Right now, those selections would return the
names
of employees that have had any one of the three.

(I apologize for the multiple posts, the website said that the post could
not be processed and to try again later.)

From the website, you download the zip file. Then, on your PC, extract
the
..mdb file to a folder on your PC. Then try to open the database file
that
you extracted.
--

Ken Snell
<MS ACCESS MVP>

I'm using Access 2000 - I've tried to open the file from the .zip
folder
as
well as directly from the website. Each time I get "Microsoft Access
for
Windows has encountered a prolblem and needs to close. We are sorry
for
any
inconvenience."

:

ACCESS crashes when you try to open the .mdb file that is in the zip
file?
More details, please. Did you download the version that matches the
version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

:

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>


I am trying to put together a query that will return only the
records
that
contain ALL of the criteria selected from a listbox rather than
all
of
the
records that contain ANY of the criteria selected. I've tried a
few
different approaches & may still be working in the wrong
direction...

I'm using a Find Duplicates Query and attempting to set the
duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As
Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement
to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!

 

[Ken Snell \(MVP\)]

To add to my other post, the listbox in my sample is set to "Extended
Multiselect". If you change it to "Simple Multiselect", you then can click
on additional items without the need to use Ctrl and/or Shift keys.

--

Ken Snell
<MS ACCESS MVP>

Your sample only allows a single selection in the list box, I'm using a
multi-select list box & need to retrieve the results for all of the
selections made.

Right now, the results show all of the records with the selections, but I
need it only to show results that contain all of the selections from the
list
box rather than those records that contain any one of the selections.

For example, if I select from my list box "ICS 100", "NIMS 800", and
"HazMat", I only want to return the names of employees that have had all
three of these trainings. Right now, those selections would return the
names
of employees that have had any one of the three.

(I apologize for the multiple posts, the website said that the post could
not be processed and to try again later.)

From the website, you download the zip file. Then, on your PC, extract
the
..mdb file to a folder on your PC. Then try to open the database file
that
you extracted.
--

Ken Snell
<MS ACCESS MVP>

I'm using Access 2000 - I've tried to open the file from the .zip
folder
as
well as directly from the website. Each time I get "Microsoft Access
for
Windows has encountered a prolblem and needs to close. We are sorry
for
any
inconvenience."

:

ACCESS crashes when you try to open the .mdb file that is in the zip
file?
More details, please. Did you download the version that matches the
version
of ACCESS that you're using?

--

Ken Snell
<MS ACCESS MVP>

Access is crashing when I try to open the file...

:

See my sample database for code to do exactly what you seek:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>


I am trying to put together a query that will return only the
records
that
contain ALL of the criteria selected from a listbox rather than
all
of
the
records that contain ANY of the criteria selected. I've tried a
few
different approaches & may still be working in the wrong
direction...

I'm using a Find Duplicates Query and attempting to set the
duplicate
value
equal to the number of items selected.

SELECT [Selections].[name], [Selections].[Region],
[Selections].[TrainingType]
FROM Selections
WHERE (((Selections.name) In (SELECT [name] FROM [Selections] As
Tmp
GROUP
BY [name] HAVING Count(*)=CountSelections())))
ORDER BY [Selections].[name];

I've been unable to get my function (CountSelections()) statement
to
count
the number of selected criterion in the listbox. My most recent
attempt
gives the error message "Object doesn't support this property or
method"
(It
was taken from a Sub statement example).


Function CountSelections()
Dim TrainingCount As Control
Set TrainingCount = Forms!Search!List2
With TrainingCount
If .ListCount > 1 Then
.ListRows = .ListCount
Else
.ListRows = 1
End If
End With
End Function

Any help would be greatly appreciated!