MS Access VBA : Update Loop with multiple IDs

Question

I'm kinda stuck on my project.

At the moment I got a form where you can fill in the roster for the players. It consists of a formation (like 4-3-3) then it will show to positions for the players where you can select a name from a drop down list.

Now I want to add the shirt number as well but i'm getting stuck on that part. I got no clue of how to update all the players where the MatchID equels the MatchID i'm working on and the PlayerID. Cause every player has a different shirt number.

Option Compare Database
Private Sub Form_Load()
Me.MatchID = Me.OpenArgs
End Sub

'This Sub shows the fields where you can select the players according to the chosen formation.

Private Sub Formation_AfterUpdate()
Dim DefenderLoopVal, MidfielderLoopVal, StrikerLoopVal As String

'Get the formation from the form.
Formation = Me.Formation

'Explode the formation on the - character
FormationExploded = Split(Formation, "-")

'Put the numbers is new variables to use in the Loops.
DefenderLoopVal = FormationExploded(0)
MidfielderLoopVal = FormationExploded(1)
StrikerLoopVal = FormationExploded(2)

'MsgBox DefenderLoopVal
'MsgBox MidfielderLoopVal
'MsgBox StrikerLoopVal

'Make Keeper Visable.
Me.imgKeeper.Visible = True
Me.cbKeeper.Visible = True
Me.NrKeeper.Visible = True

'Make as many textboxes visible as necessary
For i = 1 To DefenderLoopVal
    Form_frmFormation.Controls("cbDefender" & i).Visible = True
    Form_frmFormation.Controls("imgDefender" & i).Visible = True
    Form_frmFormation.Controls("nrDefender" & i).Visible = True
Next

For i = 1 To MidfielderLoopVal
    Form_frmFormation.Controls("cbMidfielder" & i).Visible = True
    Form_frmFormation.Controls("imgMidfielder" & i).Visible = True
    Form_frmFormation.Controls("nrMidfielder" & i).Visible = True
Next

For i = 1 To StrikerLoopVal
    Form_frmFormation.Controls("cbStriker" & i).Visible = True
    Form_frmFormation.Controls("imgStriker" & i).Visible = True
    Form_frmFormation.Controls("nrStriker" & i).Visible = True
Next

End Sub

'This is the actual saving Sub, it will save the players on the according positions
Private Sub Save_Formation_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset



Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblMatchFormation", dbOpenDynaset, dbAppendOnly)

rs.AddNew
rs!MatchID = Me!MatchID
rs!FormationID = Me!Formation
rs!Keeper = Me!cbKeeper
rs!CenterDefender = Me!cbDefender1
rs!CenterRightDefender = Me!cbDefender2
rs!CenterLeftDefender = Me!cbDefender3
rs!LeftDefender = Me!cbDefender4
rs!RightDefender = Me!cbDefender5
rs!CenterMidfielder = Me!cbMidfielder1
rs!CenterRightMidfielder = Me!cbMidfielder2
rs!CenterLeftMidfielder = Me!cbMidfielder3
rs!LeftMidfielder = Me!cbMidfielder4
rs!RightMidfielder = Me!cbMidfielder5
rs!CenterStriker = Me!cbStriker1
rs!RightStriker = Me!cbStriker2
rs!LeftStriker = Me!cbStriker3
rs.Update

'Should have a update query here that updates the tblMatchPlayer with the numbers according to the MatchID and PlayerID

End Sub

But now i want to add the player number as well, the field is on a other table named tblMatchPlayer, all of the Player details are being stored in that table

TblMatchFormation

MatchFormationID (AutoNumber)

FormationID (Gets the ID of the Formation that's being played)

MatchID (Gets the ID of the Match)

Keeper (Gets the ID of the player that's a Keeper)

CenterDefender (Gets the ID of the player that's a CenterDefender)

etc.

tblMatchPlayer

MatchPlayerID (AutoNumber)

MatchID (Gets the ID of the Match from a previous form)

PlayerID (Gets the ID of the player from a previous form)

Surname (Gets the surname of the player form a previous form)

ShirtNumber (Should get the number form the form)

Answer 1

The SQL statement to do this, if I understand you correctly, would be:

UPDATE tblMatchPlayer SET ShirtNumber = <shirt number>
WHERE MatchID = <matchID> AND PlayerID = <playerID>;

or constructing it in VB as:

Dim strSQL As String
strSQL= "UPDATE tblMatchPlayer SET ShirtNumber = '" & shirt_number & "' " & _
        "WHERE MatchID = '" & matchID & "' AND PlayerID = '" & playerID &"';"

To execute the query:

dbs.Execute strSQL