problem code

i have this database that monitors holiday entitlement, i have just been testing it through, and have noticed something very strange.

if i use the facility on the first few employee's it seems to work ok, but when i move throught the records to about number 6 or 7 i keep getting the folowing message.

Run Time Error '94'
Invalid Use Of Null

when i click on debug the following code is visible

Private Sub Holiday_End_Date_BeforeUpdate(Cancel As Integer)
If CInt(Me.txtTotal_Taken) + CInt(Me.Holidays_Duration) > CInt(Me.Parent.Entitlement) Then
MsgBox "Total Holidays Cannot Exceed Entitlement!", vbExclamation, "Holiday Entitlement Exceeded"
Cancel = True
End If
End Sub

but it is this line that is highlighted in yellow

If CInt(Me.txtTotal_Taken) + CInt(Me.Holidays_Duration) > CInt(Me.Parent.Entitlement) Then

i have attached the database for you to have a look, you will need to scroll through to employee number 6, 7, or 8ish

then click on the holiday tab

Please help

Andy

I scroll toi the 6th, 7th, and 8th with no problems. I tab over to holidays no problem.

Where / what / when / how?

Jon

It also should be:

If CInt(Me.txtTotal_Taken) + CInt(Me.Holidays_Duration) > CInt(Me.Parent.txtEntitlement) Then

If you're getting a null error...just use the IsNull function

If IsNull(Whatever field you want to check) then
'its null
exit sub
else
'do whatever
end if

Jon

have you tried entering some dates into the boxes............cos if you have that is very weird

Chimp8471 said:

have you tried entering some dates into the boxes............cos if you have that is very weird

Click to expand...

Yes and this is because txtTaken cannot be null so access is right. You cannot pass Null to CInt..since CInt is expecting some sort of string to convert to integer. A null isn't a string. So you need to check

without sounding stupid or rude, i have no idea wot you are on about.......any chance you could explain this for me please

Chimp8471 said:

without sounding stupid or rude, i have no idea wot you are on about.......any chance you could explain this for me please

Click to expand...

Solution: See Pat.

Jon

Pat Hartman said:

Try this instead:

If Nz(Me.txtTotal_Taken,0) + Nz(Me.Holidays_Duration,0) > Nz(Me.Parent.Entitlement,0) Then

Click to expand...

If Nz(Me.txtTotal_Taken,0) + Nz(Me.Holidays_Duration,0) > Nz(Me.Parent.txtEntitlement,0) Then

Its txtEntitlement Chimp.

Jon

Thank You

I have just been running some final test data on my database before i hand it in, and have found that one of the eliments isn't working.

the database i have created is a personnel database that monitors holidays.

i need my database to flag up if the employee has taken all of their entitlement, with a message box.

this worked up until a day or two ago.

i have a form which has a series of txtbox's in it :
they are:

holiday entitlement
holiday taken
holidays remaining - (txtentitlement]-[holidaytaken]

these are update as a result of the holidays booked

then i have a subform which the holidays are booked through,

date booked
startdate
enddate
duration - automatically calculated( end - start)

how it has worked upuntil recently is behind the enddate box on the subform i have had some code that basically states if txt entitlement is below 0 then "msgbox" "you cannot excede your entitlement"

but a day or so ago i needed to modify the code to sort out another problem and my action to prevent holidays remaining going negative ( not exceded employee entitlement)

the code i believe is responsible for the above is this :

Private Sub Holiday_End_Date_BeforeUpdate(Cancel As Integer)
If Nz(Me.txtTotal_Taken, 0) + Nz(Me.Holidays_Duration, 0) > Nz(Me.Parent.Entitlement, 0) Then
MsgBox "Total Holidays Cannot Exceed Entitlement!", vbExclamation, "Holiday Entitlement Exceeded"
Cancel = True
End If
End Sub

please help,

Andy

i have updated the code and attached a later version with the new code in it.

just need to sord this problem with the holidays remaining going negative, and should it try then a "msgbox" "you cannot excede your entitlement" to appear


Andy