问MS访问:查找(不包括已选定的值)；在记录输入完成之前检查唯一键

EN

Private Sub MyControl_AfterUpdate()
    newval = Me.MyControl.Value
    oldval = Me.MyControl.OldValue
    If newval = oldval Then Exit Sub ' everything's okay
    Dim rs As Object
    Set rs = Me.Form.Recordset
    whereclause = "MyControl = '" & newval & "'"
    qry = "SELECT COUNT(*) as c FROM MyQuery WHERE " & whereclause
    qrs = CurrentDb.OpenRecordset(qry)
    If qrs.Count = 1 Then cnt = qrs(0).Value
    If cnt >= 1 Then
        selval = MsgBox("Would you like to keep your selection for this record?" & vbCrLf & "[yes = change previous record's MyField; no = change MyField for this record]", vbYesNo Or VbMsgBoxStyle.vbExclamation Or vbSystemModal Or vbDefaultButton2 Or vbMsgBoxSetForeground, "Duplicate MyField selection encountered")
        If selval = vbYes Then
            ' set focus to the other entry, preserving selection here
            thissel = Me.MyControl.ListIndex
            Me.MyControl.Value = "temp" ' if a string is okay & so we can jump away from this record
            thisloc = Me.Form.CurrentRecord ' record number
            rs.Findfirst (whereclause)
            thatloc = Me.Form.CurrentRecord
            Debug.Print (thisloc & "now  ;  was" & thatloc)
            Me.MyControl.Value = "invalid"
            DoCmd.GoToRecord , , acGoTo, thisloc  ' jump to the new row
            Me.MyControl.Value = newval
            DoCmd.GoToRecord , , acGoTo, thatloc  ' jump to the one to change
            If thissel <= 0 Then thissel = 1 ' may not be useful, given the error handling below
            On Error Resume Next
            Me.MyControl.ListIndex = thissel - 1
            On Error GoTo 0
            Me.MyControl.Dropdown
        ElseIf selval = vbNo Then
            Me.MyControl.Value = Me.MyControl.OldValue
            Me.MyControl.Undo ' for some reason this doesn't clear the "dirty" bit, resulting in the edit pencil showing up for the row.
            Me.MyControl.SetFocus
        End If
    Else
        Debug.Print ("There were no matches! Das ist gut")
    End If
End Sub