Prevent duplicate record through form.

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I have seen this question many times, but I cant get it to work. I have a
tble named "Accounts" with a fld of "Cert_ID". The field "Cert_ID" will
have some null values, but what ever data is there can not be duplicated.
This is a text field. The forms name is "Account" and the textbox name is
"Cert_ID" with the record source of "Cert_ID" I have seen an several
examples of preventing duplicates such as below but cant get it to work.
Any help is appreciated...Thanks..Randy

If Not IsNull(DLookup("Cert_ID", "Accounts", _
"[Cert_ID]=" & "'" &
Me.Cert_ID & "'")) _
Then
MsgBox "Record Already exists!"
' Cancel = True
Else: MsgBox "Looks Good"
End If
 
The first problem is "" is not the same as Null. I think you may want to try
this:
Dlookup returns a Null vaule if it does not find a match.

If IsNull(DLookup("Cert_ID", "Accounts", "[Cert_ID]= " _
& [forms]![Account![Cert_ID]) Then
' It is a new value for this field
MsgBox "Looks Good"
Else: MsgBox"Record Already exists!"
Cancel = True
End If
 
I tried this but I'm getting a Syntax error. "If to Then" is highlighted.
The form name was "Accounts" and table was "Account" my error, I corrected
the code, but I still get syntax error....Thanks..Randy
Klatuu said:
The first problem is "" is not the same as Null. I think you may want to
try
this:
Dlookup returns a Null vaule if it does not find a match.

If IsNull(DLookup("Cert_ID", "Account", "[Cert_ID]= " _
& [forms]![Accounts]![Cert_ID]) Then
' It is a new value for this field
MsgBox "Looks Good"
Else: MsgBox"Record Already exists!"
Cancel = True
End If


Randy said:
I have seen this question many times, but I cant get it to work. I have
a
tble named "Accounts" with a fld of "Cert_ID". The field "Cert_ID" will
have some null values, but what ever data is there can not be duplicated.
This is a text field. The forms name is "Account" and the textbox name
is
"Cert_ID" with the record source of "Cert_ID" I have seen an several
examples of preventing duplicates such as below but cant get it to work.
Any help is appreciated...Thanks..Randy

If Not IsNull(DLookup("Cert_ID", "Accounts", _
"[Cert_ID]=" & "'" &
Me.Cert_ID & "'")) _
Then
MsgBox "Record Already exists!"
' Cancel = True
Else: MsgBox "Looks Good"
End If
Click to expand...
Click to expand...
 
Randy, not suprised by your syntax error. It was just Air code. I always
have to play with the syntax in this situation. Without all the stuff I
need, I can't debug it. I believe it will work if you can figure out the
syntax.

Randy said:
I tried this but I'm getting a Syntax error. "If to Then" is highlighted.
The form name was "Accounts" and table was "Account" my error, I corrected
the code, but I still get syntax error....Thanks..Randy
Klatuu said:
The first problem is "" is not the same as Null. I think you may want to
try
this:
Dlookup returns a Null vaule if it does not find a match.

If IsNull(DLookup("Cert_ID", "Account", "[Cert_ID]= " _
& [forms]![Accounts]![Cert_ID]) Then
' It is a new value for this field
MsgBox "Looks Good"
Else: MsgBox"Record Already exists!"
Cancel = True
End If


Randy said:
I have seen this question many times, but I cant get it to work. I have
a
tble named "Accounts" with a fld of "Cert_ID". The field "Cert_ID" will
have some null values, but what ever data is there can not be duplicated.
This is a text field. The forms name is "Account" and the textbox name
is
"Cert_ID" with the record source of "Cert_ID" I have seen an several
examples of preventing duplicates such as below but cant get it to work.
Any help is appreciated...Thanks..Randy

If Not IsNull(DLookup("Cert_ID", "Accounts", _
"[Cert_ID]=" & "'" &
Me.Cert_ID & "'")) _
Then
MsgBox "Record Already exists!"
' Cancel = True
Else: MsgBox "Looks Good"
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top