Preventing duplication of records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a table "Sales", where I have the fields "date", "location" and "points". The field "date" internally tracks the date of entry using the function date(). The other two fields have to be entered by the user. I need help whereby on a particular date; for a particular location the points can be entered only once. If the user tries to enter the points for the same location on the same day; it should show an error message. He can enter points for that location only the next day. That is suppose for a location "Texas" for date "1/27/2004" there can be only one entry. Can anyone please help?
 
Jack,

One way is to set a Unique Index on the combination of Date and Location
fields. In the design view of the table, select Indexes from the View
menu. Enter a name for your index (doesn't really matter what), enter
both the fields one under the other, and put Unique to Yes.

By the way, as an aside, the word Date has a special meaning (it is
called a 'reserved word'), and as such it is not a good idea to use it
as the name of a field or control or database object.
 
Thanks for the reply Steve, I have changed the field name "date" to "entry_date". Also with regard to setting a Unique index for the two fields, is there any way I can check this through a code since I have a button cmdSave to save the record. Now when the user enters a duplicate value and clicks on the Save button, it should check if the "points" for that particular "location" on that particular date already exists or not. I hope this makes sense.
 
Jack

Yes, if you have set up the unique index correctly, when you try to save
the record, Access should give you an error message, something like...
"The changes you requested to the table were not successful because they
would create duplicate values ..."
 
Thanks Steve, but is there any way whereby I can overwrite this message from Access and display a error message defined by me.
 
Back
Top