Coding an After Update procedure

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
 
G

Guest

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)
 
J

John Vinson

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

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
 
G

Greg Kraushaar

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)
 
R

Rick

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

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


.
 

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