Access Form - Field input

  • Thread starter Thread starter Lynemc
  • Start date Start date
L

Lynemc

I would like to be able to enter data in a specific field
on my form and have Access check whether the entry already
exists as a primary key. I know that if a duplicate entry
occurs, a warning message will occur upon save. What I
want is for this to happen after entry into this one
field. For example, the first data entered is "Claim
Number". If what is entered is a Claim Number that already
exists, I want the user to receive a warning message
saying so, and then not be allowed to continue entering
data into the form. Thanks for any help!
 
In the BeforeUpdate event of the control try something like the following:


if not isnull(dlookup("Field1","myTable","PK1=" & me.pk1)) then
msgbox "Record Already exists!"
cancel=true
endif

The Dlookup will return a null if there is no match so you are looking
for a Not null condition to indicate a duplicate. If any of your
fields are text you will have to wrap the value in Quotes so that the
value is passed to the Dlookup as a literal string. There are many
posts in this newsgroup that describe how to do this if you aren't
sure - or post again and I'll give you the syntax.
 
Thanks Sandra I'll give that a try!
-----Original Message-----
In the BeforeUpdate event of the control try something like the following:


if not isnull(dlookup("Field1","myTable","PK1=" & me.pk1)) then
msgbox "Record Already exists!"
cancel=true
endif

The Dlookup will return a null if there is no match so you are looking
for a Not null condition to indicate a duplicate. If any of your
fields are text you will have to wrap the value in Quotes so that the
value is passed to the Dlookup as a literal string. There are many
posts in this newsgroup that describe how to do this if you aren't
sure - or post again and I'll give you the syntax.



--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I would like to be able to enter data in a specific field
on my form and have Access check whether the entry already
exists as a primary key. I know that if a duplicate entry
occurs, a warning message will occur upon save. What I
want is for this to happen after entry into this one
field. For example, the first data entered is "Claim
Number". If what is entered is a Claim Number that already
exists, I want the user to receive a warning message
saying so, and then not be allowed to continue entering
data into the form. Thanks for any help!
Click to expand...
.
Click to expand...
 
I'm trying to do the exact same thing and tried your code... but got the error ".pk1 - Method or data member not found." Any suggestions on how to correct this?

Thanks.
 
Back
Top