在模块中自定义函数:ExistDuplicateRecords(Expr As String, Domain As String) As Boolean
调用时第一个参数是要查重复值的字段名,第二个参数是表或查询名称。有重复值时返回True。
以下是第一版本的代码:
'检查一个表中某个字段是否有重复值
Public Function ExistDuplicateRecords(Expr As String, Domain As String) As Boolean
On Error Resume Next
Dim strQueryName As String
Dim strSQL As String
Dim QDf As QueryDef
strQueryName = "~重复值查询"
strSQL = "Select * FROM " & Domain & _
" Where " & Expr & _
" In (Select " & Expr & _
" From " & Domain & _
" Group By " & Expr & _
" Having Count(*)>1)"
Set QDf = CurrentDb.CreateQueryDef(strQueryName, strSQL)
If DCount(Expr, strQueryName) > 0 Then
ExistDuplicateRecords = True
End If
'DoCmd.DeleteObject acQuery,strQueryName
End Function
上面的代码中生成了一个临时的查询,效率不高而且程序文件容易增肥,今天本人又做了修改,新版本代码地址在:http://www.accessoft.com/blog/article-show.asp?userid=29924&Id=9522