在提交新记录之前检查记录是否存在

Question

我目前有一个函数，可以在 Access 2010 中使用 SQL 语句将记录插入到表中。

Sub btnSubmit_Click()
dim strSQL as string
strSQL="INSERT INTO tblMaster (PeriodID,ClassID,ChildID,SubjectID,LevelID) VALUES (" & cboPeriod & "," & cboClass & "," & cboName & "," & tbSubject1 & "," & cboLevel1 & ")"
CurrentDB.Execute strSQL
End Sub

我首先要做的是检查"tblMaster"是否已经有相同的记录。

我将如何实现这一点？

Answer 1

我本以为在最坏的情况下只会有几个唯一标识符;以下内容应该有效。

Sub btnSubmit_Click()
    Dim strSQL as string
    strSQL = "INSERT INTO tblMaster (PeriodID, ClassID, ChildID, SubjectID, LevelID) VALUES (" & _
                cboPeriod & ", " & cboClass & ", " & cboName & ", " & tbSubject1 & ", " & cboLevel1 & ")"
    If DCount("*", "tblMaster", "PeriodID = " & cboPeriod & " AND " & _
                                "ClassID = " & cboClass & " AND " & _
                                "ChildID = " & cboName & " AND " & _
                                "SubjectID = " & tbSubject1 & " AND " & _
                                "LevelID = " & cboLevel1) = 0 Then
        CurrentDB.Execute strSQL
    Else
        MsgBox "A Record already exists for this information.", vbOKOnly, "No Records Added."
    End If
End Sub

Answer 2

您可以使用另一个 select 语句检查该记录是否存在。