Combo Box - SQL help please

G

Guest

Hi All

I am trying to display in my combo box only those bowlers that belong to a
said team and not display all the bowlers in the whole system.

I have one main form and am using a subform to display the bowlers assigned
to a specific team from another subform.

I have two tables and the record source for my subform is:
tblBowlerScores
- BowlerResultID
- BowlerID
- Game1
- Game2
- Game3
- MatchID
- ScheduleID
- TeamID
- HCP
- Blind
- Series

The table for storing the bowlers is
tblBowler
- BowlerID
- FirstName
- LastName
- LeagueID
- TeamID
- Sub

The SQL that was created is:

SELECT tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
FROM tblBowlers INNER JOIN tblBowlerScores ON tblBowlers.BowlerID =
tblBowlerScores.BowlerID
WHERE (((tblBowlers.BowlerID)=[tblBowlerScores].[BowlerID]))
GROUP BY tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
ORDER BY tblBowlers.LastName;

but this just shows all of the bowlers in the system!

Please can someone help me or advise me?

Kind Regards

Peter
 
P

Paul Johnson

This is all untested, please forgive typos!

If you're just finding bowlers in the combobox, then the query doesn't need
to return so much information. I would suggest simplifying the combobox's
RowSource to:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
'" & & Me.lstTeam & "' ORDER BY LastName"

You'll put the code above in the AfterUpdate event of the lstTeam control.

lstTeam is a list box where the user chooses from the teams. That control's
Row Source will be

"SELECT TeamID FROM tblBowler ORDER BY TeamID"

In both these SQL statements, I have made the assumption that TeamID is a
text field, because I don't see a separate field for TeamName. However, if
you have a table (I suspect you do, but didn't include that information) for
Teams, your SQL statements will be slightly different:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
" & & Me.lstTeam & " ORDER BY LastName"
(Only removed the single quotes)

"SELECT TeamID, TeamName FROM tblTeams ORDER BY TeamName"

You can then use the BowlerID returned in the combobox to help you access
the record from the full joined query you list, which I suspect you have
used for the recordsource of the form.

Good luck
Paul
 
G

Guest

Thankyou for that Paul. I will give it a go tonight when I get home

Thanks for your time and I'll let you know how I get on

Kind Regards

Peter

Paul Johnson said:
This is all untested, please forgive typos!

If you're just finding bowlers in the combobox, then the query doesn't need
to return so much information. I would suggest simplifying the combobox's
RowSource to:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
'" & & Me.lstTeam & "' ORDER BY LastName"

You'll put the code above in the AfterUpdate event of the lstTeam control.

lstTeam is a list box where the user chooses from the teams. That control's
Row Source will be

"SELECT TeamID FROM tblBowler ORDER BY TeamID"

In both these SQL statements, I have made the assumption that TeamID is a
text field, because I don't see a separate field for TeamName. However, if
you have a table (I suspect you do, but didn't include that information) for
Teams, your SQL statements will be slightly different:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
" & & Me.lstTeam & " ORDER BY LastName"
(Only removed the single quotes)

"SELECT TeamID, TeamName FROM tblTeams ORDER BY TeamName"

You can then use the BowlerID returned in the combobox to help you access
the record from the full joined query you list, which I suspect you have
used for the recordsource of the form.

Good luck
Paul

PDUK1 said:
Hi All

I am trying to display in my combo box only those bowlers that belong to a
said team and not display all the bowlers in the whole system.

I have one main form and am using a subform to display the bowlers assigned
to a specific team from another subform.

I have two tables and the record source for my subform is:
tblBowlerScores
- BowlerResultID
- BowlerID
- Game1
- Game2
- Game3
- MatchID
- ScheduleID
- TeamID
- HCP
- Blind
- Series

The table for storing the bowlers is
tblBowler
- BowlerID
- FirstName
- LastName
- LeagueID
- TeamID
- Sub

The SQL that was created is:

SELECT tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
FROM tblBowlers INNER JOIN tblBowlerScores ON tblBowlers.BowlerID =
tblBowlerScores.BowlerID
WHERE (((tblBowlers.BowlerID)=[tblBowlerScores].[BowlerID]))
GROUP BY tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
ORDER BY tblBowlers.LastName;

but this just shows all of the bowlers in the system!

Please can someone help me or advise me?

Kind Regards

Peter
Click to expand...
Click to expand...
 
P

Paul Johnson

I was hasty and posted with typos. Please correct the double ampersands in
WHERE TeamID = '" & & Me.lstTeam

We could also break up the lines to make the code more readable:

Should be
"SELECT BowlerID, LastName & ", " & FirstName " & _
"FROM tblBowler " & _
"WHERE TeamID = '" & Me.lstTeam & _
"' ORDER BY LastName"
(value in lstTeam is text)
or
"SELECT BowlerID, LastName & ", " & FirstName " & _
"FROM tblBowler " & _
"WHERE TeamID = " & Me.lstTeam & _
" ORDER BY LastName"
(value in lstTeam is numeric)

You probably had already caught that.

Paul

PDUK1 said:
Thankyou for that Paul. I will give it a go tonight when I get home

Thanks for your time and I'll let you know how I get on

Kind Regards

Peter

Paul Johnson said:
This is all untested, please forgive typos!

If you're just finding bowlers in the combobox, then the query doesn't need
to return so much information. I would suggest simplifying the combobox's
RowSource to:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
'" & & Me.lstTeam & "' ORDER BY LastName"

You'll put the code above in the AfterUpdate event of the lstTeam control.

lstTeam is a list box where the user chooses from the teams. That control's
Row Source will be

"SELECT TeamID FROM tblBowler ORDER BY TeamID"

In both these SQL statements, I have made the assumption that TeamID is a
text field, because I don't see a separate field for TeamName. However, if
you have a table (I suspect you do, but didn't include that information) for
Teams, your SQL statements will be slightly different:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
" & & Me.lstTeam & " ORDER BY LastName"
(Only removed the single quotes)

"SELECT TeamID, TeamName FROM tblTeams ORDER BY TeamName"

You can then use the BowlerID returned in the combobox to help you access
the record from the full joined query you list, which I suspect you have
used for the recordsource of the form.

Good luck
Paul

PDUK1 said:
Hi All

I am trying to display in my combo box only those bowlers that belong to a
said team and not display all the bowlers in the whole system.

I have one main form and am using a subform to display the bowlers assigned
to a specific team from another subform.

I have two tables and the record source for my subform is:
tblBowlerScores
- BowlerResultID
- BowlerID
- Game1
- Game2
- Game3
- MatchID
- ScheduleID
- TeamID
- HCP
- Blind
- Series

The table for storing the bowlers is
tblBowler
- BowlerID
- FirstName
- LastName
- LeagueID
- TeamID
- Sub

The SQL that was created is:

SELECT tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
FROM tblBowlers INNER JOIN tblBowlerScores ON tblBowlers.BowlerID =
tblBowlerScores.BowlerID
WHERE (((tblBowlers.BowlerID)=[tblBowlerScores].[BowlerID]))
GROUP BY tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
ORDER BY tblBowlers.LastName;

but this just shows all of the bowlers in the system!

Please can someone help me or advise me?

Kind Regards

Peter
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Johnson

On your form, you need a list box that displays the teams. If you have a
table that holds team data, then I will assume that TeamID is a numeric
value. You can use the wizard to help you create a list box that is bound to
TeamID, but displays the TeamName field (my guess). It will be a two-column
list box where the width of the first column is zero inches, so only the
second column shows in the control. If the wizard helps you, it will
probably name it List#, but you ought to rename it lstTeamID, so you can
distinguish in your code that it represents the value in the list box
control. I have used that name in the code below. I have also used
cboBowlerID for the name of your combo box that is based on tblBowler.

In the AfterUpdate event for the lstTeamID list box () is where you will
change the SQL statement for the row source of the cboBowlerID combobox:

Private Sub lstTeamID_AfterUpdate()
Me.cboBowlerID.RowSource= _
"SELECT BowlerID, LastName & ", " & FirstName " & _
"FROM tblBowler " & _
"WHERE TeamID = " & Me.lstTeamID & _
" ORDER BY LastName, FirstName" ' The first space is important!
End Sub

I don't know if there's an easier way to accomplish this, but to "seed" the
combobox with the first name in the list (it looks funny when it's empty at
the start), you could use this code before the End Sub statement (all on one
line):
Me.cboBowler = dFirst("BowlerID", "tblBowler", "TeamID=" & Me.lstTeamID)

I hope this helps.
Paul


PDUK1 said:
Hi Paul

Thanks for that. I have made the amendments and thanks for that. I have
placed the code in the said procedure and I get errors. Can I just confirm
that I have done what you said correctly please:

Private Sub BowlerID_AfterUpdate()
Dim BowlerID As Integer
Dim FirstName As String
Dim LastName As String
Dim TeamID As Integer

"SELECT BowlerID, LastName & ", " & FirstName " & _
"FROM tblBowler " & _
"WHERE TeamID = " & Me.TeamID & _
"ORDER BY LastName"

End Sub

The error is Compile error: syntax error also I took the quote out and then
it was expecting a select!, sorry but I am very new to SQL

I appreciate your help

Kind Regards

Peter

Paul Johnson said:
I was hasty and posted with typos. Please correct the double ampersands in
WHERE TeamID = '" & & Me.lstTeam

We could also break up the lines to make the code more readable:

Should be
"SELECT BowlerID, LastName & ", " & FirstName " & _
"FROM tblBowler " & _
"WHERE TeamID = '" & Me.lstTeam & _
"' ORDER BY LastName"
(value in lstTeam is text)
or
"SELECT BowlerID, LastName & ", " & FirstName " & _
"FROM tblBowler " & _
"WHERE TeamID = " & Me.lstTeam & _
" ORDER BY LastName"
(value in lstTeam is numeric)

You probably had already caught that.

Paul

PDUK1 said:
Thankyou for that Paul. I will give it a go tonight when I get home

Thanks for your time and I'll let you know how I get on

Kind Regards

Peter

:

This is all untested, please forgive typos!

If you're just finding bowlers in the combobox, then the query
Click to expand...
doesn't
need
to return so much information. I would suggest simplifying the combobox's
RowSource to:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
'" & & Me.lstTeam & "' ORDER BY LastName"

You'll put the code above in the AfterUpdate event of the lstTeam control.

lstTeam is a list box where the user chooses from the teams. That control's
Row Source will be

"SELECT TeamID FROM tblBowler ORDER BY TeamID"

In both these SQL statements, I have made the assumption that TeamID
Click to expand...
is
a
text field, because I don't see a separate field for TeamName.
Click to expand...
However,
if
you have a table (I suspect you do, but didn't include that
Click to expand...
information)
for
Teams, your SQL statements will be slightly different:

"SELECT BowlerID, LastName & ", " & FirstName FROM tblBowler WHERE TeamID =
" & & Me.lstTeam & " ORDER BY LastName"
(Only removed the single quotes)

"SELECT TeamID, TeamName FROM tblTeams ORDER BY TeamName"

You can then use the BowlerID returned in the combobox to help you access
the record from the full joined query you list, which I suspect you have
used for the recordsource of the form.

Good luck
Paul

Hi All

I am trying to display in my combo box only those bowlers that
Click to expand...
belong
to a
said team and not display all the bowlers in the whole system.

I have one main form and am using a subform to display the bowlers
assigned
to a specific team from another subform.

I have two tables and the record source for my subform is:
tblBowlerScores
- BowlerResultID
- BowlerID
- Game1
- Game2
- Game3
- MatchID
- ScheduleID
- TeamID
- HCP
- Blind
- Series

The table for storing the bowlers is
tblBowler
- BowlerID
- FirstName
- LastName
- LeagueID
- TeamID
- Sub

The SQL that was created is:

SELECT tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
FROM tblBowlers INNER JOIN tblBowlerScores ON tblBowlers.BowlerID =
tblBowlerScores.BowlerID
WHERE (((tblBowlers.BowlerID)=[tblBowlerScores].[BowlerID]))
GROUP BY tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.TeamID,
tblBowlers.LeagueID
ORDER BY tblBowlers.LastName;

but this just shows all of the bowlers in the system!

Please can someone help me or advise me?

Kind Regards

Peter
Click to expand...
Click to expand...
Click to expand...
 
Top