在创建新记录之前，检查记录中是否有重复记录 [英] check a record for Duplicates Records, Before Creating New Records

问题描述

我是这个网站的新手，也是vba代码的新手。另一个用户发布了这个问题，答案代码似乎就是我想要的代码。我想检查几个字段是否有重复的数据，标记一条消息并转到记录。我希望这段代码能做到。除了当我添加自己的字段进行检查时，我收到错误代码3077-语法错误，并且不确定如何解决。任何帮助，将不胜感激。因该错误而升高的线是第3行。我正在使用Access2010。我不确定如何链接到该问题，但被告知我应该问一个有关我的问题的新问题。

I am new to this site and new to vba code. Another user posted this question and the answer code seemed to be the code I was looking for. I want to check several fields for duplicate data, flag a message and go to the record. I was hoping this code would do that. Except when I add my own fields to check I get error code 3077 - syntax error and I am not sure how to fix it. any help would be appreciated. The line highted for this error is the 3rd line down. I am using access 2010. I am not sure how to link to the question but was advised I should ask a new question regarding my problem.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rst As Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "[ID] <> " & Me.ID & " AND [TitleText] = " & Me.TitleText & " AND [UnitCode] = " & Me.UnitCode & " AND  [AcademicYear] = " & Me.AcademicYear & " AND    [Titleofchapterjournalarticle] = " & Me.Titleofchapterjournalarticle
    If Not rst.NoMatch Then
        Cancel = True
        If MsgBox("A record matching these fields already exist", vbYesNo) = vbYes Then
            Me.Undo
            DoCmd.SearchForRecord , , acFirst, "[ID] = " & rst("ID")
        End If
    End If
    rst.Close
End Sub

推荐答案

我认为您的意思是第四行是突出显示的行， rst.FindFirst ... 。

I think you mean that the fourth line down is the one highlighted, rst.FindFirst....

在这种情况下， FindFirst 字符串中存在语法错误。您的 TitleText 或 Titleofchapterjournalarticle 字段可能包含空格或逻辑代码（或，与）。为避免这种情况，请在字符串的所有字符串类型字段中都用‘（单引号）引起来。

If that is the case, there is a syntax error in your FindFirst string. It could be that your TitleText or Titleofchapterjournalarticle fields contained spaces or logic code (OR, AND). To prevent this, surround any string type fields with ' (single quote) inside the string.

最终结果如下：

rst.FindFirst "[ID] <> " & Me.ID & _
 " AND [TitleText] = '" & Me.TitleText & _
 "' AND [UnitCode] = " & Me.UnitCode & _
 " AND [AcademicYear] = " & Me.AcademicYear & _
 " AND [Titleofchapterjournalarticle] = '" & Me.Titleofchapterjournalarticle & "'"

这篇关于在创建新记录之前，检查记录中是否有重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！