Access Form - Field input

[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!

 

[Sandra Daigle]

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.

 

[Guest]

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!

.

 

[novice]

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.