MS Access VBA :具有多个ID的Update循环

Question

我有点困在我的项目上了。

此刻，我得到了一份表格，你可以在那里填写球员名册。它包括一个队形(如4-3-3)，然后它将显示给球员的位置，在那里你可以从下拉列表中选择一个名字。

现在我也想把衬衫的号码加进去，但那部分我被卡住了。我不知道如何更新所有的球员，其中MatchID等于我正在工作的MatchID和PlayerID。因为每个球员都有不同的球衣号码。

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

但是现在我也想添加播放器号，字段位于另一个名为tblMatchPlayer的表上，所有的播放器详细信息都存储在该表中。

TblMatchFormation

MatchFormationID (AutoNumber)

FormationID (获取正在播放的编队的ID )

MatchID (获取匹配的ID )

守门员(获取守护者的球员的ID )

CenterDefender (获取播放器的ID，即CenterDefender)

等。

tblMatchPlayer

MatchPlayerID (AutoNumber)

MatchID (从前面的表单获取匹配的ID )

PlayerID (从以前的表单中获取播放器的ID )

姓氏(从以前的表格中获取球员的姓氏)

ShirtNumber (应该从表单中获取数字)

Answer 1

如果我对您的理解正确，执行此操作的SQL语句将是：

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

或在VB中将其构造为：

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

要执行查询，请执行以下操作：

dbs.Execute strSQL