Field Restriction in forms

[Arun]

Hi

Please advise me in the following

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 the program should
give a message that the employee has already been paid,
and duplication should not allow, on the other had the
system should allow the user to enter the daily allowance
for same employee( that means no restrictions in daily
allowance)

How can we do this please advise

Thanks
Arun

 

[Marshall Barton]

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 the program should
give a message that the employee has already been paid,
and duplication should not allow, on the other had the
system should allow the user to enter the daily allowance
for same employee( that means no restrictions in daily
allowance)

Generally, multifield validation should be done in the
form's BeforeUpdate event. You might want to use a DCount
to check if another record meets the cirteria. Maybe
something like this:

If Not IsNull(DCount("*", "table", "Emp_ID=" & Me.txtEmpID _
& " And Year([Date])=" & Year(Me.txtDate) _
& " And House_Allowance Is Not Null") Then
MsgBox "Duplicate House Allowance"
Cancel = True
End If

 

[Arun]

Thanks a lot

I've used this method, but now it is not allowing me any
record to add. as soon I finish entering it doesnot allow
me to save. I need the system to allow once, only the
duplication to be avoided. Please help

-----Original Message-----

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 the program should
give a message that the employee has already been paid,
and duplication should not allow, on the other had the
system should allow the user to enter the daily allowance
for same employee( that means no restrictions in daily
allowance)

Generally, multifield validation should be done in the
form's BeforeUpdate event. You might want to use a DCount
to check if another record meets the cirteria. Maybe
something like this:

If Not IsNull(DCount("*", "table", "Emp_ID=" & Me.txtEmpID _
& " And Year([Date])=" & Year(Me.txtDate) _
& " And House_Allowance Is Not Null") Then
MsgBox "Duplicate House Allowance"
Cancel = True
End If

 

[Marshall Barton]

What does your code look like now?

Are you sure that a record has not been added (and you are
being prevented from modifying it)?

It may be better if the code were placed in the BeforeInsert
event, but then you may be able to modify an existing record
in such a way to create a "duplicate".
--
Marsh
MVP [MS Access]

I've used this method, but now it is not allowing me any
record to add. as soon I finish entering it doesnot allow
me to save. I need the system to allow once, only the
duplication to be avoided. Please help

-----Original Message-----

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 the program should
give a message that the employee has already been paid,
and duplication should not allow, on the other had the
system should allow the user to enter the daily allowance
for same employee( that means no restrictions in daily
allowance)

Generally, multifield validation should be done in the
form's BeforeUpdate event. You might want to use a DCount
to check if another record meets the cirteria. Maybe
something like this:

If Not IsNull(DCount("*", "table", "Emp_ID=" & Me.txtEmpID _
& " And Year([Date])=" & Year(Me.txtDate) _
& " And House_Allowance Is Not Null") Then
MsgBox "Duplicate House Allowance"
Cancel = True
End If