M
MarkG
Does anyone know how to throw an error message when a
duplicate value is entered in a form?
Rgds
Mark
duplicate value is entered in a form?
Rgds
Mark
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Ken Snell
Duplicate value meaning --- duplicate value in a field that matches one
already in the table? duplicate value for the entire record?
More info please about how you define duplicate and what setup you're using
to enter the value and to handle it.
already in the table? duplicate value for the entire record?
More info please about how you define duplicate and what setup you're using
to enter the value and to handle it.
A
Allen Browne
Open the table in design view, select the field, and in the lower pane set
the Indexed property to:
Yes (No Duplicates)
This will raise an error at the time the record is about to be saved.
If you wanted to allow the user to override the choice and save the record
anyway, you could use the BeforeUpdate event of the text box on your form to
DLookup() the table and see if the value exists in another record as well.
the Indexed property to:
Yes (No Duplicates)
This will raise an error at the time the record is about to be saved.
If you wanted to allow the user to override the choice and save the record
anyway, you could use the BeforeUpdate event of the text box on your form to
DLookup() the table and see if the value exists in another record as well.
M
MarkG
Allen, thanks for your reply but i probably didn't make
myself clear enough. I know how to prevent duplicate
data being added to a field in a table, but what I want
to do is throw a custom error message when a duplicate
value is entered in a field in a form. E.g. 'The User ID
you entered has already been used please choose another
one'
Any help would be greatly appreciated
Rgds
Mark
myself clear enough. I know how to prevent duplicate
data being added to a field in a table, but what I want
to do is throw a custom error message when a duplicate
value is entered in a field in a form. E.g. 'The User ID
you entered has already been used please choose another
one'
Any help would be greatly appreciated
Rgds
Mark
G
Guest
Ken, I replied to Allen's email, if you have any
suggestions it would be greatly appreciated.
Rgds
Mark
suggestions it would be greatly appreciated.
Rgds
Mark
A
Allen Browne
To capture and replace the existing message, use the Error event of the
form. Unfortunately, that event does not tell you which control caused the
problem, and does not fire until there is an attempt to save the record.
Your other option is the BeforeUpdate event of the control itself. Use
DLookup() to see if *another* record has the same value, and if so, cancel
the text box's BeforeUpdate event.
form. Unfortunately, that event does not tell you which control caused the
problem, and does not fire until there is an attempt to save the record.
Your other option is the BeforeUpdate event of the control itself. Use
DLookup() to see if *another* record has the same value, and if so, cancel
the text box's BeforeUpdate event.
M
MarkG
Can you give me an example of the code I need to add for
the event procedure?
Rgds
Mark
the event procedure?
Rgds
Mark
K
Ken Snell
Private Sub txtBoxName_BeforeUpdate(Cancel As Integer)
If Len(DLookup("FieldName", "TableName", "[PrimaryFieldName]=" & _
Me.txtBoxName.Value) & "") > 0 Then
Cancel = True
MsgBox "You've entered a value that is already in the database."
End If
End Sub
If Len(DLookup("FieldName", "TableName", "[PrimaryFieldName]=" & _
Me.txtBoxName.Value) & "") > 0 Then
Cancel = True
MsgBox "You've entered a value that is already in the database."
End If
End Sub