message box on duplicate value

[gopi]

Hi, im tyring to prompt the user with a message everytime a duplicate value
is entered in a field. But i cant seem to get it working, the code im using
is:
------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
Me.Text228.Value)) Then
MsgBox "Record Exist"
End If

End Sub
-----------------------------------

South_Tracker is the table name,
Upgrade Number is the field name in the table (which is a primark key)
Text228 is the field name in the form.

If anyone can help me with this code, i would much appreciate it.
Thanks

 

[Ofer Cohen]

DLookUp will look for a value in a field withinn a table, so you can't use *.

If Not IsNull(DLookup("[Upgrade Number]", "South_Tracker", "[Upgrade Number]
=" & Me.Text228.Value)) Then


DCount will count how many records you have in the Table so you can use *

If DCount("*", "South_Tracker", "[Upgrade Number] =" & Me.Text228.Value) > 0
Then

 

[davidp]

Hi, im tyring to prompt the user with a message everytime a duplicate value
is entered in a field. But i cant seem to get it working, the code im using
is:
------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
Me.Text228.Value)) Then
MsgBox "Record Exist"
End If

End Sub
-----------------------------------

South_Tracker is the table name,
Upgrade Number is the field name in the table (which is a primark key)
Text228 is the field name in the form.

If anyone can help me with this code, i would much appreciate it.
Thanks

try using DLookup("[Upgrade Number]", "[South_Tracker]",
"[Upgrade Number] =" & Text228)

I found that everything must have [ and ], never tried it with an *.

 

[gopi]

Thanks, that worked.

DLookUp will look for a value in a field withinn a table, so you can't use *.

If Not IsNull(DLookup("[Upgrade Number]", "South_Tracker", "[Upgrade Number]
=" & Me.Text228.Value)) Then


DCount will count how many records you have in the Table so you can use *

If DCount("*", "South_Tracker", "[Upgrade Number] =" & Me.Text228.Value) > 0
Then

--
Good Luck
BS"D

Hi, im tyring to prompt the user with a message everytime a duplicate value
is entered in a field. But i cant seem to get it working, the code im using
is:
------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
Me.Text228.Value)) Then
MsgBox "Record Exist"
End If

End Sub
-----------------------------------

South_Tracker is the table name,
Upgrade Number is the field name in the table (which is a primark key)
Text228 is the field name in the form.

If anyone can help me with this code, i would much appreciate it.
Thanks

 

[Douglas J Steele]

As far as I know, you can't use * in DLookup.

Try replacing * with the name of a field that exists in the table (use
[Upgrade Number] if you like)

You should also set Cancel = True if a duplicate is found. Otherwise, it'll
try to save it:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[Upgrade Number]", _
"South_Tracker", "[Upgrade Number] =" & _
Me.Text228.Value)) Then

MsgBox "Record Exist"
Cancel = True

End If

End Sub

 

[gopi]

Even better. Thanks

As far as I know, you can't use * in DLookup.

Try replacing * with the name of a field that exists in the table (use
[Upgrade Number] if you like)

You should also set Cancel = True if a duplicate is found. Otherwise, it'll
try to save it:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[Upgrade Number]", _
"South_Tracker", "[Upgrade Number] =" & _
Me.Text228.Value)) Then

MsgBox "Record Exist"
Cancel = True

End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, im tyring to prompt the user with a message everytime a duplicate value
is entered in a field. But i cant seem to get it working, the code im using
is:
------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
Me.Text228.Value)) Then
MsgBox "Record Exist"
End If

End Sub
-----------------------------------

South_Tracker is the table name,
Upgrade Number is the field name in the table (which is a primark key)
Text228 is the field name in the form.

If anyone can help me with this code, i would much appreciate it.
Thanks