Duplicate Values

[mare]

I want to prevent users from keying in duplicate values in 2 fields in an Access 97 database that holds stock numbers used in our department's printed materials (numbers are used for others to order the materials). As our materials are printed in English and French, the fields are called ENGLISH and FRENCH.

ENGLISH was set as the Primary Key - the numbers are unique it each item (no duplicates allowed). I've set the following:
Data Type: Number (Long Integer)
Required: Yes
Indexed: Yes (No Duplicates)
Yet, when I test the form to input new materials, I can key in a stock number that already exists in ENGLISH - a duplicate!. How can a prevent this from happening?

A further problem. FRENCH - the numbers are also unique to each item, is Required, but is not indexed (should it?). In some cases, where the material is bilingual (one stock number required - English to carry for both), or material does not exist is French, the field in the table for that particular record (material) is left blank. If I Index it, I'm alerted to duplicate values in the FRENCH field (the blanks).

I'm an Access novice, but I'm learning FAST!!!
Any help, suggestions will be greatly appreciated.

 

[Elana]

Obviously if you set the required property to True, then you must enter a number even if none exists, so you would enter 0, which then violates the Indexed(no duplicates) property of the field.

I tried this and it worked though - do not set the required property to True and do not set the default value to 0 for either field. Set the Indexed property of both fields to Indexed-no duplicates.

Then, in your entry form, you can place code to validate whether one or both of the fields contains data and not let the user update the record unless there is data in either field.

The BeforeUpdate event of the form is where you would place this code - for example,

If isNull(Me.English) and isnull(me.french) then

msgbox "You must enter something in either the English or French Fields.", vbokonly, "No Data Entered"
cancel=true
exit sub

else
'do nothing, update the form
end if

You can also place code in the OnError event of the form to give the user a meaningful error message in case they are entering a duplicate number in either field. If you want some help with this, let me know.

HTH
E

 

[mare]

Elana,
Thank you for your assistance. The suggestions you made were most helpful regarding entry validation. However, that was not quite what I was looking for.

The Primary Key, ENGLISH, is set to
Index: Yes (no duplicates)
There will always be an entry (stock number) in this field, because everything we create is in English.

FRENCH is not indexed and the entries are optional. It is set to
Index: no

ExampleUnder Normal Circumstances - There is an English version and a French version)
English #: 19007
French #: 19008
Title: Testing new products

Example: (No French document created, because there is no demand for it)
English #: 19813
French #: (field is left blank)
Title: Outlet stores

Example: (Document is bilingual- 1 side English/1 side French)
English #: 19466
French#: (field is left blank)
Title: Guest Book

Now, when I'm testing the New Item Entry Form (where all new entries to the database is made), and I type in the ENGLISH field an English stock number that I know exists, the form accepts that number. While the table will not save this "new" entry, it doesn't give a warning of any type.

Ditto in the FRENCH field with an existing French stock number.

What I would really like is to check for a duplicate as soon as the entry is made into the English or French field (perhaps when tabbing to the next field to enter data in?), and send up a warning message that the user has entered data that already exists, and to return to the Form to correct the error.

Any ideas?

 

[Elana]

Yes - I have done this before.

Please take a look at the MS Knowledge Base Articles 102527 and 103135 for information on how to do what you want to do.

HTH

E

 

[mare]

Elana,

I took your advice and read those 2 articles from the MS Knowledge Base. I ran the sample database to see what these macros do - it's exactly what I wanted.

I applied the principles to my database in the English and French fields ... AND IT WORKS!!!!!!

Thank you so much to pointing me in the right direction. You were a great help to me.