Stop duplicate entry

  • Thread starter Thread starter Alex Martinez
  • Start date Start date
A

Alex Martinez

Hello,

I am using Access 2002 and I have a form which contains the following
fields: Claim agent, which is a 4 digit code and another field called
weekly production, which is a date field. Each Friday the user will input a
pre-define 4 digit agent code and enter the date for weekly production.
For example a user will input BF11 for the claim agent and enter a date
10/14/2005. I don't want the user to enter twice the same claims agent with
the same weekly production date. How can stop the duplication? As also,
thank you in advance.
 
Suppose:
1) the name of the form is "MyForm"
2) the source of the records for "MyForm" is table "MyTable"
3) "MyTable" has key-field "RecordID" with unique numbers for each record
"MyTable" has fields "UserID" (for storing agent codes) and "Date" (for
storing production dates)
4) the control for the agent code is "UserID"
5) the control for the production date is "Date"

Set the AfterUpdate property of the "Date" control as follows:


If IsNull(Me.UserID)=False Then
If
IsNull(DLookup("[RecordID]","MyTable","[UserID]=[Forms]![MyForm]![UserID] And
[Date]=[Forms]![MyForm]![Date]"))=False Then
MsgBox "Such record already exixts! Enter another date!",,"Duplicate record"
Me.Date.SetFocus
Exit Sub
End If
End If

Set the AfterUpdate property of the "UserID" control as follows:


If IsNull(Me.Date)=False Then
If
IsNull(DLookup("[RecordID]","MyTable","[UserID]=[Forms]![MyForm]![UserID] And
[Date]=[Forms]![MyForm]![Date]"))=False Then
MsgBox "Such record already exixts! Enter another User ID!",,"Duplicate
record"
Me.UserID.SetFocus
Exit Sub
End If
End If

The above code is not optimal, but it can give you an idea...
 
Back
Top