Data Change

  • Thread starter Thread starter faapa via AccessMonster.com
  • Start date Start date
F

faapa via AccessMonster.com

Hi

Is there a piece of code i can write on a form to check if duplicate data has
been entered?

Basically, i have a textbox on a form that requires the user to enter a title
(pm_title) i was hoping that if the user types in the same title, an error
will appear, alerting the user that the title can not be used as a previous
entry has that name so they have to create a new one....i hope that makes
sense!

(The PM table has an pm_id which is hidden and only pm_title can be seen by
the user.)

Would anyone know how to do this??
 
A simple way to do this is to:
1. Open the table in design view.
2. Select the pm_title field.
3. In the lower pane of table design, set the Indexed property to:
Yes (No Duplicates.)
Now the user will be unable to save a record if the title is a duplicate.

If you want to give the user a warning, but allow the duplicate anyway, you
can put some code into the BeforeUpdate event procedure of the *form*. This
kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
With Me.pm_title
If (.Value = .OldValue) OR IsNull(.Value) Then
'do nothing
Else
strWhere = "[pm_title] = """ & .Value & """"
varResult = DLookup("pm_id", "PM", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate title. Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Warning") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
end If
End With
End Sub
 
Back
Top