validation rule for unique number

[Jo]

Field for ID is set as primary key so entries must be unique, but, when
tested, an error message only comes up when all the fields have been entered.
How can I make the validation alert appear as soon as a duplicate number is
entered to prevent moving to the next field? I guess it's a simple
validation rule but I can't seem to get any help from the paper clip!

 

[Allen Browne]

Use the AfterUpdate event procedure of the ID text box to DLookup() the
table and see if the value already exists. If you want to block the entry
(not merely warn the user), use the text box's BeforeUpdate event procedure.

This kind of thing:

Private Sub ID_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.ID) OR (Me.ID = Me.ID.OldValue) Then
'Do nothing: it's not a duplicate of itself.
Else
If Not IsNull(DLookup("ID", "Table1", "ID = " & Me.ID)) Then
Cancel = True
strMsg = "You already have that value." & vbCrLf & _
"Enter another value, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Duplicate value!"
End If
End If
End Sub

 

[tina]

AFAIK, you can't do it at the table level, because the index isn't validated
until the record is about to be written to disk (either by moving to another
record, or by closing the table). the same applies at the form level, of
course, but you can write code to validate the value when the form
*control's* BeforeUpdate event fires, as

If DCount(1, "TableName", "IDFieldName = " _
& Me!IDFieldName) > 0 Then
Cancel = True
Msgbox "Sorry, this is a duplicate ID number, try again."
End If

if the id field is Text data type rather than Number data type, modify the
DCount() function to put single quotes around the id value, as

DCount(1, "TableName", "IDFieldName = '" _
& Me!IDFieldName & "'")

replace TableName and IDFieldName with the correct names in your database,
of course.

hth

 

[Jo]

I LOVE Microsoft Communities - thanks for sharing with me.

 

[Jo]

Great - thanks Tine

AFAIK, you can't do it at the table level, because the index isn't validated
until the record is about to be written to disk (either by moving to another
record, or by closing the table). the same applies at the form level, of
course, but you can write code to validate the value when the form
*control's* BeforeUpdate event fires, as

If DCount(1, "TableName", "IDFieldName = " _
& Me!IDFieldName) > 0 Then
Cancel = True
Msgbox "Sorry, this is a duplicate ID number, try again."
End If

if the id field is Text data type rather than Number data type, modify the
DCount() function to put single quotes around the id value, as

DCount(1, "TableName", "IDFieldName = '" _
& Me!IDFieldName & "'")

replace TableName and IDFieldName with the correct names in your database,
of course.

hth

 

[tina]

you're welcome

Great - thanks Tine