ACCESS判断字段不能重复录入的方法

1、DCount

文本型数据

If DCount("文本型字段名", "表名称", "文本型字段名='" & Me.文本型字段名 & "'") > 0 Then
MsgBox "你输入的数据已经存在，请重新输入", vbCritical, "警告"
Me.文本型字段名.SetFocus
Exit Sub
End If

文本型数据

‘判断数字型不能重复录入使用DCount函数方法：

If DCount("数值型字段名", "表名称", "数值型字段名=" & Me.数值型字段名) > 0 Then

MsgBox "你输入的数字已经存在，请重新输入", vbCritical, "警告"

Me.数值型字段名.SetFocus

Exit Sub

End If

2、Dlookup

Dim z As Long

z = DLookup("数值型字段名", "表名称", "数值型字段名= " & Me.数值型字段名)

If Not IsNull(z) Then

MsgBox "你输入的数字已经存在，请重新输入", vbCritical, "警告"

Me.数值型字段名.SetFocus

Exit Sub

End If

日期型数据

Dim x As Variant

x = DLookup("日期型字段名", "表名称", "日期型字段名= #" & Me.日期型字段名 & "#")

If Not IsNull(x) Then
MsgBox "你输入的日期已经存在，请重新输入", vbCritical, "警告"
Me.日期型字段名.SetFocus
Exit Sub

End If

3、ADO遍历数据库

Dim Rs As ADODB.Recordset

Dim strtemp As String

Dim i As Integer

Dim Xitong As Boolean

Set Rs = New ADODB.Recordset

strtemp = "select * from 字段所在表"

Rs.Open strtemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Rs.RecordCount <= 0 Then

'判断记录是否存在

Rs.MoveFirst

For i = 0 To Rs.RecordCount - 1

If (Rs("字段名称") = Me![字段名称]) Then

MsgBox "你输入的数据已经存在，请重新输入", vbCritical, "警告"

Me.字段名称.SetFocus
Exit Sub

Else

Rs.MoveNext

End If

Next i

End If