I have a form that keeps record of check numbers.



What validation procedure can I write to prevent duplicate entries.



Thanks

Howard,



<< I have a form that keeps record of check numbers.  What validation 

procedure can I write to prevent duplicate entries. >>



You don't have to write anything.  In the table's design view, click the 

field that will contain the unique records, then click the "Primary Key" 

button on the Table Design toolbar.  This will set the field as a primary 

key and will set it as "indexed, no duplicates" in the index control box.  

If someone tries to duplicate a value, Access itself will issue an error 

message.



Be careful with choosing check number as a primary key.  Two people may 

have different checking accounts but may coincidentally be using the same 

check numbers in their respective series.  A better primary key would be a 

combination of checking account number + check number (perhaps separated 

by a dash), e.g. "1234567-0274".



Greg

Greg:



Thanks for your response but I use primary key to do that.



Ialready have the database setup with AutoNumber as the primary key.

Can you suggest any other way?



I would realy like to have a procedure that I van re-use.  As I develop 

more databases I am sure there will be the necessity to validate to 

prevent duplicates as described and I would like to learn it now.



Thanks again for your quick input to assist me



Howard

Howard



Greg's answer still stands mostly.



Go into the table design and change the indexed property of your cheque 

number field to 'Yes (No Duplicates)'. 



This will prevent duplicates whilst preserving your original primary key.



-Brian



> Greg:

> 

> Thanks for your response but I use primary key to do that.

> 

> Ialready have the database setup with AutoNumber as the primary key.

> Can you suggest any other way?

> 

> I would realy like to have a procedure that I van re-use.  As I develop 

> more databases I am sure there will be the necessity to validate to 

> prevent duplicates as described and I would like to learn it now.

> 

> Thanks again for your quick input to assist me

> 

> Howard

1) Open table containing Check Number field  in design mode.

2) Click on Check Number to select it.

3( Under General Tab click Indexed and select Yes (No Duplicates)

4) Write code to trap duplicate key in your Form_Error event and display 

user-friendly message.



For example:



Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const conDuplicateKey =3D 3022

=09

    Select Case DataErr



        Case conSpellingCheckComplete

              'Do something



       Case conInvalidFormat

               'Do something else



       Case conInvalidMask

               'Do another thing.....

      

       Case conDuplicateKey

            Response =3D acDataErrContinue

            MsgBox "Record already exists for selected person. " _

                & "Please choose another person or press Esc button to 

cancel " _

                & "the selection to continue...", vbCritical, "DATA ERROR: 

Duplicate record detected!"

             DoCmd.CancelEvent

      

        Case Else

            MsgBox "Error no: " & DataErr & " occured >>> " & Error _

            & " Please write this message down and notify programming " _

            & "staff about this error.", vbCritical, "Error on Your Form 

Error"

    End Select



End Sub



Hope that helps...



____________________________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

____________________________________________________



The doors we open and close each day decide the lives we live

____________________________________________________



CONFIDENTIALITY & PRIVILEGE NOTICE

The information contained in this email message is intended for the named 

addressee only.  If you are not the intended recipient you must not copy, 

distribute, take any action reliant on, or disclose any details of the 

information in this email to any other person or organisation.  If you 

have received this email in error please notify us immediately.



>>> ququmber@h... 12/12/2001 1:19:27 >>>

Greg:



Thanks for your response but I use primary key to do that.



Ialready have the database setup with AutoNumber as the primary key.

Can you suggest any other way?



I would realy like to have a procedure that I van re-use.  As I develop

more databases I am sure there will be the necessity to validate to

prevent duplicates as described and I would like to learn it now.



Thanks again for your quick input to assist me



Howard






This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient,
please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily
the views of Hunter Health.

Preventing duplicates does not require a field to be the promary key.

Just index the field setting the AllowDuplicates property to No.



>I have a form that keeps record of check numbers.



What validation procedure can I write to prevent duplicate entries.<



Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx