访问VBA /防止重复值

您可以更改您的SQL以使用IF EXISTS条件并仅在记录尚不存在时插入。

你的SQL可能看起来像：

IF NOT EXISTS （ SELECT ...... ）

BEGIN 
     INSERT INTO tblEmployees ......<insert since employee does not exists> 
    END 

这听起来像你想如果存在对于给定的ID否则你想添加一个新雇员更新雇员。您可以通过先尝试更新具有给定ID的员工记录，并且如果没有记录更新，然后添加新的员工记录，可以防止添加重复员工。

Private Sub Command12_Click() 
    If (IsNull(Me.emID) Or (Me.emID = "") Or IsNull(Me.emFirst) Or (Me.emFirst = "") Or IsNull(Me.emLast) Or (Me.emLast = "")) Then 
     Me.emID.BorderColor = vbRed 
     Me.emFirst.BorderColor = vbRed 
     Me.emLast.BorderColor = vbRed 
     MsgBox "Please fill required fields", vbInformation, "Information" 
     Exit Sub 
    End If 
    ' You must set CurrentDb to a variable otherwise the RecordsAffected 
    ' property used later will be incorrect. 
    Dim db As DAO.Database 
    Set db = CurrentDb 
    ' First try to update an existing employee. 
    db.Execute _ 
     "UPDATE tblEmployees " & _ 
     "SET first ='" & Me.emFirst & "', " & _ 
      "last = '" & Me.emLast & "', " & _ 
      "gender ='" & Me.emGender & "', " & _ 
      "phone = '" & Me.emPhone & "', " & _ 
      "mobphone ='" & Me.emMob & "', " & _ 
      "city ='" & Me.emCity & "', " & _ 
      "state ='" & Me.emState & "', " & _ 
      "zip ='" & Me.emZip & "', " & _ 
      "adress ='" & Me.emAdress & "', " & _ 
      "email ='" & Me.emEmail & "', " & _ 
      "comment ='" & Me.emComment & "'" & _ 
     "WHERE emID =" & Me.emID.Tag & ";" 
    ' If no records were affected by update then add a new employee. 
    If db.RecordsAffected = 0 Then 
     db.Execute _ 
      "INSERT INTO tblEmployees(emID, first, last, gender, phone, mobphone, city, state, zip, adress, email, comment) " & _ 
      "VALUES ('" & Me.emID & "', '" & Me.emFirst & "', '" & Me.emLast & "', '" & Me.emGender & "', '" & Me.emPhone & "', '" & Me.emMob & "', '" & Me.emCity & "', '" & Me.emState & "', '" & Me.emZip & "', '" & Me.emAdress & "', '" & Me.emEmail & "', '" & Me.emComment & "');" 
     MsgBox "Record Added", vbInformation, "Information" 
    Else 
     MsgBox "Updated!", vbInformation, "Information" 
    End If 
    Me.tblEmployees_subform.Form.Requery 
End Sub 

注：在更新查询我删除了更新的EMID领域，因为这是该查询是基于（WHERE子句中）。如果emID字段正在更改，您将无法使用新的emID值查找具有旧emID值的员工记录。

如Daniel Cook所建议的，如果您从不需要任何重复项，我还建议您为数据库表添加约束以防止出现重复项。我还建议研究使用参数化查询，而不是在VBA中构建SQL字符串。