Preventing duplicate values

[sahil]

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?

 

[dan-cat]

You could have a validation check on the before update event of your form.

Something like:

Private Sub frmSales_BeforeUpdate(Cancel As Integer)


If DCount("Id","sales","Location=Forms!frmSales!Location And Date=Forms!frmSales!Date And Points=Forms!frmSales!Points") >0 Then

MsgBox "You already have a blah blah blah..."
Cancel = True
End If
End Sub

Where frmSales is the name of your form.
By the way I would change the name of your date field. Will be confused with the function date()

 

[dcx693]

Why not change the structure of the table so that the combination of the Date and Location fields becomes your compound primary key? Access will then prevent you from entering in other records with the same combination automatically.

FYI: If your date field is called "Date", you might want to change that since the word "Date" is a reserved VBA word.

 

[sahil]

Thanks for your suggestion, I have changed the field name "date" to "entry_date". Please advise how do I make the combination of Date and Location fields as compound primary key. And I believe Access will show its own error message if this combination is already existing, Is there any way I can overwrite the error message which Access gives and use the one which I define.

 

[dcx693]

The procedure to define a compound primary key is pretty much the same as doing it for just one field. In table design view, click the first field, hold down the Control key and click on the second one to highlight the two of them, then click the little key icon in the toolbar.

Yes, you can change the error message that Access shows by specifying your own. There are lots of posts on this topic like:
Custom error messages?

 

[sahil]

Thank you very much. It worked.

 

[dcx693]

Cool. You're welcome.