Duplicate Value Question

ecupirate

Registered User.
Local time
Today, 08:47
Joined
Apr 19, 2002
Messages
14
I have two combobox(fields) Period and Year
I want to keep any duplicate combinations out of my tblResults table. I do not want the same employee to have 2 entries for March 2002, etc.. My relationship from my tblEmplyees is EmployeeID and my primary key in the tblResults is ResultCount. I think I need a dlookup, but I'm not quite sure how to write using criteria in two fields..

Would appreciate any help.....
 
Couple options here:
Foolproof method: Define Period (Month?) and Year and EmployeeID as a unique multifield index of tblResults (or even the Primary Key), no duplicates. Before the record is finished Access will forbid you from updating the record. There is no way around this, but the downside is that it does not warn you until the end of the record. Soooo...
In the AfterUpdate event of the last of those three fields, put a spot of code in that says:
Code:
If DCount("*","tblResults","[PeriodFieldInTable] = '" & Me.PeriodComboOnForm & "' AND [YearFieldInTable] = " _
 & Me.YearComboOnForm & " AND [EmployeeID] = " & Me.EmployeeIDControlOnForm) Then
   MsgBox "You cannot enter this period and year for this employee twice. Please choose another."
   Me.WhicheverFieldYouWant.SetFocus
   Cancel = True
End If
Note this assumes Period is a text field and the other two are numeric, hence the single quotes.

Dlookup would work but since you only are interested in how many, not what, Dcount is marginally faster.

Why are you storing Month and Year separately, by the way?

[This message has been edited by David R (edited 05-01-2002).]
 

Users who are viewing this thread

Back
Top Bottom