Multiselect Listbox selections don't show up in report

[jdub]

Hey Forum,

I'm a newbie to access and have been tasked to allow users to select a group
of names from a list and display them in a report. I've created a query that
grabs the names from the database. I've created a form with a list box and
I've created a report. I'm able to select the names and then click the
button to start the report. My report comes in blank. I'm not sure what
Control Source to use for the field. I've searched the internet and have
found some code that I'm using. It is below. The report name is GameRoster,
the Listbox is List0 and the field I need is the Name field that is being
returned in the query for the listbox.

Any help would be greatly appreciated.

Private Sub ShowRecord_Click()
On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "GameRoster"

'Loop through the ItemsSelected in the list box.
With Me.List0
For Each varItem In List0.ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & List0.ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible column.
See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With


lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Name] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Player Name " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ShowRecord_Click"
End If
Resume Exit_Handler
End Sub

 

[Allen Browne]

You have a field called Name?

That will confuse Access, because nearly everything has a Name property.
Access might therefore compare the Name of the report against the items in
the list, and it never matches, so the report comes up empty.

Rename the field to something else. Before you do that, make sure the Name
AutoCorrect boxes are unchecked under:
Tools | Options | General
and the compact the database. Otherwise Access may still misunderstand the
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hey Forum,

I'm a newbie to access and have been tasked to allow users to select a
group
of names from a list and display them in a report. I've created a query
that
grabs the names from the database. I've created a form with a list box
and
I've created a report. I'm able to select the names and then click the
button to start the report. My report comes in blank. I'm not sure what
Control Source to use for the field. I've searched the internet and have
found some code that I'm using. It is below. The report name is
GameRoster,
the Listbox is List0 and the field I need is the Name field that is being
returned in the query for the listbox.

Any help would be greatly appreciated.

Private Sub ShowRecord_Click()
On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "GameRoster"

'Loop through the ItemsSelected in the list box.
With Me.List0
For Each varItem In List0.ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & List0.ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column.
See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With


lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Name] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Player Name " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ShowRecord_Click"
End If
Resume Exit_Handler
End Sub

 

[jdub via AccessMonster.com]

Thanks Allen.

I changed to a new field and made the changes to the AutoCorrect boxes. I
used a Home Phone field instead of selecting by Name. The report is still
empty. I did past a Report.OpenArgs on my report so that I could see the
where command and it has the values of the items that were selected. On my
report I've created a text box that should list all of the selections. How
does the data make it to this field? Do I need to modify the Control Source
property or ???????????

JW

You have a field called Name?

That will confuse Access, because nearly everything has a Name property.
Access might therefore compare the Name of the report against the items in
the list, and it never matches, so the report comes up empty.

Rename the field to something else. Before you do that, make sure the Name
AutoCorrect boxes are unchecked under:
Tools | Options | General
and the compact the database. Otherwise Access may still misunderstand the
field.

Hey Forum,

[quoted text clipped - 70 lines]

Resume Exit_Handler
End Sub

 

[Allen Browne]

The answer will depend on how your tables are set up.

I'm guessing you have these tables:
- Player table: one record for each player
o PlayerID AutoNumber primary key (pk)
o Surname Text
...
- Game table: one record for each time a game is played:
o GameID AutoNumber pk
o GameDate Date/Time
...

- GamePlayer table: one record for each player in each game:
o GameID Number relates to Game.GameID
o PlayerID Number relateds to Player.PlayerID

Now you will have a query that contains all 3 tables, and that query will be
the RecordSource of your report. You have a form with an unbound
multi-select list box that uses the Player table as its RowSource. And you
are trying to limit the report to only the games played by the players
selected in the list box?

If that is not what you are doing, you may need explain how your data is set
up.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.

I changed to a new field and made the changes to the AutoCorrect boxes. I
used a Home Phone field instead of selecting by Name. The report is still
empty. I did past a Report.OpenArgs on my report so that I could see the
where command and it has the values of the items that were selected. On
my
report I've created a text box that should list all of the selections.
How
does the data make it to this field? Do I need to modify the Control
Source
property or ???????????

JW

You have a field called Name?

That will confuse Access, because nearly everything has a Name property.
Access might therefore compare the Name of the report against the items in
the list, and it never matches, so the report comes up empty.

Rename the field to something else. Before you do that, make sure the Name
AutoCorrect boxes are unchecked under:
Tools | Options | General
and the compact the database. Otherwise Access may still misunderstand the
field.

Hey Forum,

[quoted text clipped - 70 lines]

Resume Exit_Handler
End Sub

 

[jdub via AccessMonster.com]

I have one table. DJFRegistrationTable. The table has the names of all the
players and there squad name. Each Saturday, the user has to submit game
rosters to the league and opposing team. In the past they manually write the
players in on a form. They would like to be able to pull up the list of
players, based on their squad. Select the players that are playing and then
print the game roster. A squad may have 35 people on their registration
roster but only 26 players show up on Saturday. For the form, I run a query
against the DJFRegistrationTable to populate the unbound multiselect listbox
with players from the appropriate squad. There are 20 squads. I've created
20 queries to do this. On the report, I'm not sure how to get the selections
to appear in the report field under the Player Name column. I would like for
the players to be listed in a row and not separated by commas.

Thanks,

The answer will depend on how your tables are set up.

I'm guessing you have these tables:
- Player table: one record for each player
o PlayerID AutoNumber primary key (pk)
o Surname Text
...
- Game table: one record for each time a game is played:
o GameID AutoNumber pk
o GameDate Date/Time
...

- GamePlayer table: one record for each player in each game:
o GameID Number relates to Game.GameID
o PlayerID Number relateds to Player.PlayerID

Now you will have a query that contains all 3 tables, and that query will be
the RecordSource of your report. You have a form with an unbound
multi-select list box that uses the Player table as its RowSource. And you
are trying to limit the report to only the games played by the players
selected in the list box?

If that is not what you are doing, you may need explain how your data is set
up.

Thanks Allen.

[quoted text clipped - 28 lines]

 

[Allen Browne]

So if someone misspells a player name when entering, it won't show up? And
how does the list box get a valid list of the player names if there is no
table of players?

Take a look at the previous post again, and see how to build a relational
structure. In the long-term, this will save you lots of problems.

For another example of how and why to connect tables up like this, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

Ultimately, the specifics of the multi-select list box code depend on your
name and data type of the field where the player names are stored. It will
be more important to get the data structure right though. If you want to
read further on that, search for "normalization". Some introductory
articles:
http://support.microsoft.com/kb/209534/en-us
http://support.microsoft.com/kb/324613/en-us
http://support.microsoft.com/kb/288947/en-us
http://support.microsoft.com/kb/234208/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have one table. DJFRegistrationTable. The table has the names of all the
players and there squad name. Each Saturday, the user has to submit game
rosters to the league and opposing team. In the past they manually write
the
players in on a form. They would like to be able to pull up the list of
players, based on their squad. Select the players that are playing and
then
print the game roster. A squad may have 35 people on their registration
roster but only 26 players show up on Saturday. For the form, I run a
query
against the DJFRegistrationTable to populate the unbound multiselect
listbox
with players from the appropriate squad. There are 20 squads. I've
created
20 queries to do this. On the report, I'm not sure how to get the
selections
to appear in the report field under the Player Name column. I would like
for
the players to be listed in a row and not separated by commas.

Thanks,

The answer will depend on how your tables are set up.

I'm guessing you have these tables:
- Player table: one record for each player
o PlayerID AutoNumber primary key (pk)
o Surname Text
...
- Game table: one record for each time a game is played:
o GameID AutoNumber pk
o GameDate Date/Time
...

- GamePlayer table: one record for each player in each game:
o GameID Number relates to Game.GameID
o PlayerID Number relateds to Player.PlayerID

Now you will have a query that contains all 3 tables, and that query will
be
the RecordSource of your report. You have a form with an unbound
multi-select list box that uses the Player table as its RowSource. And you
are trying to limit the report to only the games played by the players
selected in the list box?

If that is not what you are doing, you may need explain how your data is
set
up.

Thanks Allen.

[quoted text clipped - 28 lines]

Resume Exit_Handler
End Sub

 

[jdub via AccessMonster.com]

Why would you have multiple tables if you only had a database of player names
and their squad? I'm not following the misspelled name question? The list
box is getting a valid list of players from the query that is being run
against the registration table. The problem is getting the selected users on
the form to be listed on the report. And from what I gather, I need to build
multiple tables to get a two column table to work?

So if someone misspells a player name when entering, it won't show up? And
how does the list box get a valid list of the player names if there is no
table of players?

Take a look at the previous post again, and see how to build a relational
structure. In the long-term, this will save you lots of problems.

For another example of how and why to connect tables up like this, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

Ultimately, the specifics of the multi-select list box code depend on your
name and data type of the field where the player names are stored. It will
be more important to get the data structure right though. If you want to
read further on that, search for "normalization". Some introductory
articles:
http://support.microsoft.com/kb/209534/en-us
http://support.microsoft.com/kb/324613/en-us
http://support.microsoft.com/kb/288947/en-us
http://support.microsoft.com/kb/234208/en-us

I have one table. DJFRegistrationTable. The table has the names of all the
players and there squad name. Each Saturday, the user has to submit game

[quoted text clipped - 50 lines]

 

[Allen Browne]

Okay, I don't think I can help. I don't understand what you are doing with
your table, so I can't help you to get the right output from that table into
your report.

Perhaps someone else has a suggestion for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Why would you have multiple tables if you only had a database of player
names
and their squad? I'm not following the misspelled name question? The
list
box is getting a valid list of players from the query that is being run
against the registration table. The problem is getting the selected users
on
the form to be listed on the report. And from what I gather, I need to
build
multiple tables to get a two column table to work?

So if someone misspells a player name when entering, it won't show up? And
how does the list box get a valid list of the player names if there is no
table of players?

Take a look at the previous post again, and see how to build a relational
structure. In the long-term, this will save you lots of problems.

For another example of how and why to connect tables up like this, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

Ultimately, the specifics of the multi-select list box code depend on your
name and data type of the field where the player names are stored. It will
be more important to get the data structure right though. If you want to
read further on that, search for "normalization". Some introductory
articles:
http://support.microsoft.com/kb/209534/en-us
http://support.microsoft.com/kb/324613/en-us
http://support.microsoft.com/kb/288947/en-us
http://support.microsoft.com/kb/234208/en-us

I have one table. DJFRegistrationTable. The table has the names of all
the
players and there squad name. Each Saturday, the user has to submit
game

[quoted text clipped - 50 lines]

Resume Exit_Handler
End Sub

 

[jdub via AccessMonster.com]

Allen, you are helping me. I've reviewed the references and understand what
they are saying. I'm not understanding why I would need multiple tables if
I only have a Name Field and a Squad Field in the table. The database was
designed by another developer. I'm simply trying to create a report from the
two fields. I looked at the setup and saw that the Name Field is the Primary
Key. Presently, there are queries for all of the different squads that
produce reports respectively. I was tasked to take it one step further and
that was to take the present table, create a form that will produce a list of
all of the players for the given squad, and then allow the user to select the
players from the list who are playing in that day's game and create a report.
Does that help some??

JW

Okay, I don't think I can help. I don't understand what you are doing with
your table, so I can't help you to get the right output from that table into
your report.

Perhaps someone else has a suggestion for you.

Why would you have multiple tables if you only had a database of player
names

[quoted text clipped - 36 lines]

 

[jdub via AccessMonster.com]

Allen,

I've setup the following and I'll attempt again. I read over your earlier
post a couple of more times and the light bulb popped on. This is what I've
setup so far.

- Player table: one record for each player
o PlayerID AutoNumber primary key (pk)
o Surname Text
...
- Squad table: one record for each time a game is played:
o SquadID AutoNumber pk
o Squad Name Text
...

- GameRosterTable: one record for each player in each game:
o GameID Number relates to Game.GameID
o PlayerID Number relateds to Player.PlayerID

Thanks Allen for your help. You're doing wonders for me!!!!

JW

Allen, you are helping me. I've reviewed the references and understand what
they are saying. I'm not understanding why I would need multiple tables if
I only have a Name Field and a Squad Field in the table. The database was
designed by another developer. I'm simply trying to create a report from the
two fields. I looked at the setup and saw that the Name Field is the Primary
Key. Presently, there are queries for all of the different squads that
produce reports respectively. I was tasked to take it one step further and
that was to take the present table, create a form that will produce a list of
all of the players for the given squad, and then allow the user to select the
players from the list who are playing in that day's game and create a report.
Does that help some??

JW

Okay, I don't think I can help. I don't understand what you are doing with
your table, so I can't help you to get the right output from that table into

[quoted text clipped - 7 lines]