Query to select players for a team without duplicates

[PhilC]

Hello All,

I need some inspiration!

I have created a form. The form also has a subform which is a continuous form.

The form will be used to enter results from matches played in our local cribbage league. I will explain how matches are played.

In any match there are two teams, A and B.
Each team has six players.
Players play in pairs.
Three games constitute a match.
Pair 1 from the home team play 5 legs against Pair 1 from the away team etc.
Once the match is completed the whole team of 3 sets of pairs will have played a total of 15 legs.
The team with the highest total of legs won is the match winner.

The problem I have is that a single player can only be selected once in the match.

I have created combo boxes to select the players from the list of players registered with each team. The record source for the combo boxes are queries selecting the relevant players.

When I come to select the first player, everything works fine. But when I come to select his partner, I can still select the first player again!

So, how do I stop this from happening? I realise that I need to eliminate the players from a team if they have already been selected. But how?

I would appreciate any help you can offer.

Thanks

 

[June7]

Have to limit second combobox to players that are not already selected. A query something like:

SELECT * FROM Players WHERE PlayerID NOT IN (SELECT PlayerID FROM Matches WHERE MatchID=[input match ID]);

Use code in first combobox AfterUpdate event to commit record and requery second combobox.

If you need more help with this, provide your db for anlaysis. Quite possibly a UNION query will be required for the nested query.

 

[PhilC]

Hello June7,

Thank you for your contribution.

I tried your suggestion, but could not get it to work. So I tried an alternative strategy. I created a temporary table called tblSelectedPlayers and wrote an afterUpdate event where the playerID selected from the first combo was added to the tblSelectedPlayers along with the Fixture number(fixID). I then re-queried the second combo. This worked fine. BUT . . this is a continuous form with 3 records. Remember 3 tables (NOT Access tables note)? so when I move to the next table to enter the two players who played on the second table, the whole thing went screwy and my already selected players appeared in the combo box drop down again.

I have attached the database. The form is called frmResultEntry and the subform is called frmTableResult1.

I hope you can provide a little more inspiration. Thank You

Phil

 

[MajP]

I would make a union query like

SELECT tblTableResult.FixID, tblTableResult.HomePlayer1 as PlayerID, "Home" AS HomerOrAway
FROM tblTableResult
Union
SELECT tblTableResult.FixID, tblTableResult.HomePlayer2, "Home" AS HomerOrAway
FROM tblTableResult
UNION
SELECT tblTableResult.FixID, tblTableResult.AwayPlayer1, "Away" AS HomerOrAway
FROM tblTableResult
UNION SELECT tblTableResult.FixID, tblTableResult.AwayPlayer2, "Away" AS HomerOrAway
FROM tblTableResult;

Then get rid of all of your code and replace with.

  LimitPlayer Me.AwayPlayer1, Me.Parent.AID
End Sub

Private Sub AwayPlayer1_Exit(Cancel As Integer)
  RelaxFilter Me.AwayPlayer1
End Sub

Private Sub AwayPlayer2_Enter()
  LimitPlayer Me.AwayPlayer2, Me.Parent.AID
End Sub

Private Sub AwayPlayer2_Exit(Cancel As Integer)
RelaxFilter Me.AwayPlayer2
End Sub

Private Sub Form_Load()
Me.TableNumber.SetFocus
End Sub

Private Sub HomePlayer1_Enter()
  LimitPlayer Me.HomePlayer1, Me.Parent.HID
End Sub
Private Sub HomePlayer1_Exit(Cancel As Integer)
RelaxFilter Me.HomePlayer1
End Sub

Private Sub HomePlayer2_Enter()
  LimitPlayer Me.HomePlayer2, Me.Parent.HID
End Sub
Private Sub HomePlayer2_Exit(Cancel As Integer)
RelaxFilter Me.HomePlayer2
End Sub
Public Sub LimitPlayer(ctrl As Access.ComboBox, TeamID As String)
  Dim strSql As String
  Me.Dirty = False
  strSql = "SELECT PlayerID, PlayerName FROM tblPlayers where PlayerID NOT IN (SELECT PlayerID FROM qryFixPlayers WHERE FixID = " & Me.Parent.FixID & ") AND TeamID = '" & TeamID & "' ORDER BY tblPlayers.PlayerName"
  'Debug.Print strSql
  ctrl.RowSource = strSql
  ctrl.Requery
End Sub
Public Sub RelaxFilter(ctrl As Access.Control)
  Dim strSql As String
  strSql = "SELECT PlayerID, PlayerName FROM tblPlayers" '
  Debug.Print strSql
  ctrl.RowSource = strSql
  ctrl.Requery
End Sub

The one thing that is slightly problematic is that when you enter the combo it will appear as if the combo blanks out until selecting a value or leaving the combo.

 

[MajP]

If you want to simplify the code further then make two functions

Public Function LimitPlayer()
  Dim strSql As String
  Dim teamID As String
  Dim ctrl As Access.ComboBox
  Set ctrl = Me.ActiveControl
  If ctrl Is Me.HomePlayer1 Or ctrl Is Me.HomePlayer2 Then
    teamID = Me.Parent.HID
  Else
    teamID = Me.Parent.AID
  End If
  Me.Dirty = False
  strSql = "SELECT PlayerID, PlayerName FROM tblPlayers where PlayerID NOT IN (SELECT PlayerID FROM qryFixPlayers WHERE FixID = " & Me.Parent.FixID & ") AND TeamID = '" & teamID & "' ORDER BY tblPlayers.PlayerName"
  'Debug.Print strSql
  ctrl.RowSource = strSql
  ctrl.Requery
End Function
Public Function RelaxFilter()
  Dim strSql As String
  Dim ctrl As Access.Control
  strSql = "SELECT PlayerID, PlayerName FROM tblPlayers" '
  'Debug.Print strSql
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acComboBox Then
      ctrl.RowSource = strSql
     ctrl.Requery
    End If
  Next ctrl
End Function

For each combo in the onEnter
=LimitPlayer()
on the onExit
=RelaxFilter()