Solved Want MS Access to notify me if a record already exists (1 Viewer)

jg_1981

New member
Local time
Today, 12:13
Joined
Oct 7, 2021
Messages
19
Hello Everyone and Happy New Year for You!

I'm working on in a new project in MS Access.

I need that MS Access notify me if a record already exists when I insert new data in the form, in this case the field "Cod".

I attached an simple database example to show you the error that Ms Access gives me.

In the form "Table1" when I try to insert a new record, Ms Access gives me the following error:

"Run-time error '3464' Data type mismatch in criteria expression"

I'm using the folowing code in the form "Table1:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[Cod]", "Table1", "[Cod]= '" & Me![Cod] & "'") > 0 Then
MsgBox "Name Is Already In Database!"
End If
End Sub

How can I turn this around?

Thank You For Your Help.
 

Attachments

  • Recorded already exists.accdb
    1.4 MB · Views: 308

Ranman256

Well-known member
Local time
Today, 08:13
Joined
Apr 9, 2015
Messages
4,337
I don't think [COD] is a string, if numeric then remove the quotes:

=DCount("[Cod]", "Table1", "[Cod]=" & Me![Cod] )
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:13
Joined
May 7, 2009
Messages
19,169
you also Need to Cancel it if already exists:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = (DCount("1", "Table1", "[Cod]= " & Me![Cod] ) <> 0)
If Cancel Then
MsgBox "Name Is Already In Database!"
End If
End Sub
 

jg_1981

New member
Local time
Today, 12:13
Joined
Oct 7, 2021
Messages
19
I don't think [COD] is a string, if numeric then remove the quotes:

=DCount("[Cod]", "Table1", "[Cod]=" & Me![Cod] )
Thank you very much Ranman256. It worked like you said. Best regards
 

jg_1981

New member
Local time
Today, 12:13
Joined
Oct 7, 2021
Messages
19
you also Need to Cancel it if already exists:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = (DCount("1", "Table1", "[Cod]= " & Me![Cod] ) <> 0)
If Cancel Then
MsgBox "Name Is Already In Database!"
End If
End Sub
you also Need to Cancel it if already exists:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = (DCount("1", "Table1", "[Cod]= " & Me![Cod] ) <> 0)
If Cancel Then
MsgBox "Name Is Already In Database!"
End If
End Sub
Thank you very much arnelgp. Thank you very much for the tip. Nice touch. Best regards
 

Users who are viewing this thread

Top Bottom