Coding an After Update procedure

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Hi,
After a user enters data into a field on a form, how would
I check to see if the exact same data already exists in
another record (same field same table)? Assume that the
field is not the primary key field. Thanks,
-Rick
 
Depending on your approach you could do some coding .. or simply define the field as No Duplicates in the database - this way you can trap the error and display something about the duplicate field (The entire record will be disallowed)
 
Hi,
After a user enters data into a field on a form, how would
I check to see if the exact same data already exists in
another record (same field same table)? Assume that the
field is not the primary key field. Thanks,
-Rick
Click to expand...

Use the BeforeUpdate event instead - the AfterUpdate is too late, the
data has (as the name suggests) already been loaded.

YOu can use DLookUp to search the table for the entered value and set
the Cancel argument of the BeforeUpdate event to True to cancel the
addition (after warning the user).

Frex:

Private Sub txtXYZ_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("XYZ", "yourtable", "[XYZ] = " & txtXYZ) Then
MsgBox "This value already exists"
Cancel = True
End If
End Sub
 
Set rst = me.recordsetCLone
Searcrh rst for a match
Do what you want with the information

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)
 
Thanks John, that is what I was looking for.
-Rick
-----Original Message-----
Hi,
After a user enters data into a field on a form, how would
I check to see if the exact same data already exists in
another record (same field same table)? Assume that the
field is not the primary key field. Thanks,
-Rick
Click to expand...

Use the BeforeUpdate event instead - the AfterUpdate is too late, the
data has (as the name suggests) already been loaded.

YOu can use DLookUp to search the table for the entered value and set
the Cancel argument of the BeforeUpdate event to True to cancel the
addition (after warning the user).

Frex:

Private Sub txtXYZ_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("XYZ", "yourtable", "[XYZ] = " & txtXYZ) Then
MsgBox "This value already exists"
Cancel = True
End If
End Sub


.
Click to expand...
 
Back
Top