No duplicates

K

Ken

Hi all
I need to check for no duplicates as soon as I leave the first field and not
as I try to save the record. Is there any way of doing this.
Cheers
Ken
 
A

Allen Browne

Use the BeforeUpdate (or AfterUpdate) event of the control instead of that
of the form.

Use DLookup() to see if the value is already in the table.

This kind of thing:

Private Sub Num1_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
If (Me.Num1 = Me.Num1.OldValue) OR IsNull(Me.Num1) Then
'do nothing
Else
strWhere = "[Num1] = " & Me.Num1
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Already used in ID " & varResult
End If
End If
End Sub
 
A

Arvin Meyer [MVP]

Here's 1 that I posted about 10 years ago:

Private Sub txtPersonID_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

Dim db As Database
Dim rst As Recordset
Dim strSQL As String

Set db = CurrentDb

' Add quotes to Me!PersonID for a text type
' strSQL = "SELECT [PersonID] FROM tblPeople WHERE [PersonID] = """ &
Me![txtPersonID] & """"

strSQL = " SELECT [PersonID] FROM tblPeople WHERE [PersonID] = " &
Me![txtPersonID]
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "This Person ID is already allocated, please select another",
vbOKOnly, "Duplicate PersonID"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Top