database - 更新查询运行没有错误，但数据未在 Access 中更新

Question

我在 Access 中有一个 CompetencyTable，我正在使用 VBA 代码来更新访问表中的数据，但是，代码运行成功，但是，访问表中的数据没有得到更新。

我在访问表 ActualTarget、ActualCompleted、ActualPending 中有三个字段。现在实际目标已由用户定义，并且最初实际目标 = 实际待定。例如，如果实际目标为 40，则实际待处理为 40。现在我希望实际完成从 1、2、3、4 等递增，并且随着实际完成的数量增加，实际待处理的数量应该减少。

代码运行没有错误，但字段没有得到更新。

以下是我开发的代码

Private Sub CommandButtonSubmit_Click()

Dim CseID, Gid, NmofAgnt, Dt, LOB, Accnmbr, WrkTyp, CsNm, DtmtFnd, Validt, Rsn  As String
Dim Gid1, EmpNm As String
Dim ActCm, ActTgt, ActPend As Integer


Gid1 = Environ("USERNAME")
CseID = TextBoxCSID.Text
Gid = TextBoxGID.Value
NmofAgnt = TextBoxNm.Text
Dt = TextBoxDt.Text
LOB = TextBoxLOB.Text
Accnmbr = TextBoxAccNmbr.Text
WrkTyp = TextBoxWrkTyp.Text
CsNm = TextBoxCsNm.Text
DtmtFnd = ComboBoxDetmtFnd.Value
Validt = ComboBoxValidt.Value
Rsn = TextBoxRsn.Text

If ComboBoxValidt.Value = "Yes" Then
    Call connOpen
    rst.Open "SELECT * FROM LoginAdmin WHERE [GlobalID]= '" & Environ("USERNAME") & "'", conn
        If rst.EOF = False Then
            EmpNm = Trim(rst.Fields("EmpName").Value)
        Else
            Exit Sub
        End If
    rst.Close

        strQuery = "INSERT INTO QAAudits ([GlobalIDQA], [EmpName], [CaseID], [GlobalIDAgent], 
[NameofAgent], [DateProcessed], [LOB], " & _
            "[AccntNmbr], [WorkType], [CustomerName], [DeterimentFindings], [Validated]) " & _
        "Values('" & Gid1 & "', '" & EmpNm & "', '" & CseID & "', '" & Gid & "', '" & NmofAgnt & "', 
'" & Dt & "', '" & LOB & "', '" & Accnmbr & "', " & _
            "'" & WrkTyp & "', '" & CsNm & "', '" & DtmtFnd & "', '" & Validt & "')"
    conn.Execute strQuery


    rst.Open "SELECT * FROM CompetencyTable WHERE [GlobalID]= '" & Gid & "'", conn
        If rst.EOF = False Then
            ActCm = Trim(rst.Fields("ActualCompleted").Value)
            ActTgt = Trim(rst.Fields("ActualTarget").Value)
            ActPend = Trim(rst.Fields("ActualPend").Value)
            ActCm = ActCm + 1
            ActPend = ActTgt - ActCm
        End If
        rst.Close


    rst.Open "UPDATE CompetencyTable SET ActualCompleted= " & ActCm & " AND ActualPend= " & ActPend & 
" WHERE [GlobalID]= '" & Gid & "'", conn



Call connclose

MsgBox "Audited Case Submitted Successfully", vbInformation, "Done"
Unload Me
Qualityform.Show

ElseIf ComboBoxValidt.Value = "No" Then

    Call connOpen
        rst.Open "SELECT * FROM LoginAdmin WHERE [GlobalID]= '" & Environ("USERNAME") & "'", conn
            If rst.EOF = False Then
                EmpNm = Trim(rst.Fields("EmpName").Value)
            Else
                Exit Sub
            End If
        rst.Close

        strQuery = "INSERT INTO QAAuditsIncomplete ([GlobalIDQA], [EmpName], [CaseID], 
[GlobalIDAgent], [NameofAgent], [DateProcessed], [LOB]" & _
        "[AccntNmbr], [WorkType], [CustomerName], [DeterimentFindings], [Validated], [Reason])" & _
        "VALUES ('" & Gid1 & "', '" & EmpNm & "', '" & CseID & "', '" & Gid & "', '" & NmofAgnt & "', 
'" & Dt & "', '" & Accnmbr & "'" & _
        "'" & WrkTyp & "', '" & CsNm & "', '" & DtmtFnd & "', '" & Validt & "', '" & Rsn & "')"
        conn.Execute strQuery
    Call connclose

MsgBox "Incomeplete Audit Case Submitted Successfully in Database", vbInformation, "Done"
Unload Me
Qualityform.Show
Else
    MsgBox "Please select Validated or not", vbCritical
End If





End Sub

因此，在 vba 中有一个名为 Audit 表单的表单，当数据将从 Audit 表单中添加时，组合框验证enter code here值 = Yes 然后它应该更新 Actual Completed 和 Actual Pending 中的能力表

请帮助我度过难关！

提前谢谢了！

标签： databasevbams-access