Duplicate record restriction

  • Thread starter Thread starter Arun
  • Start date Start date
A

Arun

Hi

Can any one please help me in the follwing

I've a table which has the following fields

Date
Emp_ID
MediCal_allowance
Ticket-Fare
House_Allowace
Daily_Allowance

I've the form having the above mentioned fields

What I'm looking for is the following
I want to avoid duplication that means, in a year , an
employee entitled for the medical,ticket and
House_Allowace is once . if the user try to enter the
above allowance again in the same year for the same
employee the program should give a message that the
employee has already been paid, and duplication should not
allow, on the other hand the system should allow the user
to enter the daily allowance for same employee( that means
no restrictions in daily allowance) if the entry for next
year ( 2005) then the system should allow.

How can we do this please advise

I've used the following code but, it's not giving the
proper result. If I change the year from 2004 to 2005,
then it is restricting from 2005 onwards, but it is
allowing the user to enter as many records for the year
2004 for the same employee

CODE I'VE USED

Private Sub Medical_AfterUpdate()
Dim RecDate As Variant
Dim bMed As Boolean
RecDate = Year(Nz(DLookup
("InputDate", "MasterData", "[Medical] > 0 And [Emp_ID]
=" & Me.Emp_ID), #1/1/1900#))
bMed = (RecDate = Year(VBA.Date))
If bMed Then
MsgBox ("Medical paid for current year.")
Medical = 0
End If
End Sub
 
Arun,

One problem here is that DLookup will find *a* record that meets your
criteria, which is not necessarily the one you want. One alternative is
to use a query which is sorted, and then use DFirst. Another, if you
want to use a Domain Aggreggate function, is DCount, like this (your
post is confusing - I will assume that the date field in the table is
called InputDate) ...

Private Sub Medical_AfterUpdate()
Dim bMed As Boolean
bMed = DCount("*","MasterData","[Medical]>0 And [Emp_ID]=" &
Me.Emp_ID & " And Year([InputDate])=Year(Date())")>0
If bMed Then
MsgBox ("Medical paid for current year.")
Medical = 0
End If
End Sub

Having said that, however, I should mention that your table design is
not ideal. A better approach would be...

Table: MasterData
AllowanceID
InputDate
Emp_ID
PaymentAmount
AllowanceType (here is entered Medical or Ticket or House or Daily)

This will make a lot of things easier, including the type of data
validation you are currently trying to do.
 
Back
Top