validation rule for unique number


G

Guest

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!
 
Ad

Advertisements

A

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
 
T

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
 
G

Guest

Great - thanks Tine

tina said:
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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top