防止Access 2013中的重复记录

问题描述：

我看了下面的问题，这正是我想要做的，但是发布的答案似乎不适用于我。我的查询基本上是一样的，但我会详细说明。防止Access 2013中的重复记录

我有更新的信息的表具有以下形式：

该数据库还增加了经由=日期（）中的字段默认值设置自动DateEntered，并有一个主键自动编号称为CPDEAID。

我将下面的代码添加到表格。

Private Sub Form_BeforeUpdate(Cancel As Integer) 
    Set rst = Me.RecordsetClone 
    rst.FindFirst "[CPDEAID] <> " & Me.CPDEAID & " AND [Forename] = " & Me.Forename & " AND [Surname] = " & Me.Surname & " AND [EmailAddress] = " & Me.EmailAddress 
    If Not rst.NoMatch Then 
     Cancel = True 
     If MsgBox("This person already exists; would you like to go to the existing record?", vbYesNo) = vbYes Then 
      Me.Undo 
      DoCmd.SearchForRecord , , acFirst, "[CPDEAID] = " & rst("CPDEAID") 
     End If 
    End If 
    rst.Close 
End Sub

但是，这似乎并不奏效。我可以让我的数据库不创建重复记录的唯一方法是通过创建多列索引 - 但这有点混乱，因为我想要一个干净的“用户友好型”前端。

我在这里错过了很简单的东西吗？

Prevent Duplicate Records, Query Before Creating New Records

我不确定你的多列索引是什么意思，使它有点杂乱，不干净的“用户友好型”前端？它是否会导致您不希望用户看到的弹出式警告？ – Slai

将该字段添加为主键或设置多列索引。要么捕获在添加副本时产生的错误编号，要么执行类似'DCount'的操作来确定存在多少记录，并且当它大于0时显示消息。 –

答

它可能不会是因为你的工作，你rst.FindFirst建立一个比较文本列的SQL字符串，但你不使用周围的参数单引号。

但是，您不应该那样做用户输入，因为这会冒SQL注入的风险。

相反，使用参数查询如图所示here，或标准模块中使用的效用函数是这样的：

' DLookupPar: Look up a value with a parameterized query 
' The first 3 parameters are identical to DLookup, but in Criteria you can use parameters. 
' Then add the exact number of parameters as following arguments, in the exact order as used in <Criteria> 
' 
' Sample call: 
' varUserID = DLookupPar("UserID", "tUsers", "Username = [parUser] AND [Password] = [parPassword]", Me!Username, "aPassword") 
' 
Public Function DLookupPar(Expr As String, Domain As String, Criteria As String, ParamArray arParams() As Variant) As Variant 

    Dim db As DAO.Database 
    Dim qd As DAO.QueryDef 
    Dim rs As DAO.Recordset 
    Dim strSelect As String 
    Dim i As Long 

    strSelect = "SELECT " & Expr & " FROM " & Domain & " WHERE " & Criteria 
    'Debug.Print strSelect 

    Set db = CurrentDb 
    ' Create temporary querydef (no name) and set all parameters from arParams 
    Set qd = db.CreateQueryDef("", strSelect) 
    For i = LBound(arParams) To UBound(arParams) 
     qd.Parameters(i) = arParams(i) 
    Next i 

    Set rs = qd.OpenRecordset(dbOpenSnapshot) 
    If Not rs.EOF Then 
     ' Return the first and only column 
     DLookupPar = rs(0) 
    Else 
     DLookupPar = Null 
    End If 
    rs.Close 

End Function

，并在您的形式使用这样的：

Dim ID As Long 
Dim rs As Recordset 

ID = Nz(DLookupPar("CPDEAID", "yourTable", _ 
    "[CPDEAID] <> [parCPDEAID] AND [Forename] = [parForename] AND [Surname] = [parSurname] AND [EmailAddress] = [parEmailAddress]", _ 
    Me.CPDEAID, Me.Forename, Me.Surname, Me.EmailAddress), 0) 

If ID > 0 Then 
    ' MsgBox etc 
    Set rs = Me.RecordsetClone 
    rs.FindFirst "[CPDEAID] = " & ID 
    Me.Bookmark = rs.Bookmark 
    rs.Close 
End If

防止Access 2013中的重复记录

相关推荐