Record Validation Rule Question

[Guest]

Hi,

Please excuse my innocence, I'm useless with Access.

I am trying to do the following:

In general, for a specific record I want the entries to be filled in in one
specific field to be dependent on the filled in entry of a previous field.

More specifically, I want the entry of my 'OverTime' field to be '0' when
the previous field, 'NormalTime', in the same record is less than 8. I want
the 'OverTime' field to be able to record an entry higher than '0' only when
the 'NormalTime' field in the corressponding record is '8'.

What 'recrod validation rule should I use, what would the exact rule look
like?

thanks so much in advance!

 

[Guest]

Hi David

You could use something like this

Private Sub NormalTime_AfterUpdate()
If Me.NormalTime <= 7 Then
Me.OverTime = 0
End If
End Sub


I don't know what you want to happen if the NormalTime = 9 or 10 or 11 so I
have assumed that you just want 7 or less - but this may wrong.

 

[Guest]

Hi Wayne,

Thanks so much for trying to help!

To indicate how useless I am I clicked that your response was not helpful
while that certainly is not the case!

To answer your question, in the NormalTime Field I have set a rule that no
entries can be more than 8, that much I can handle!

I tried entering your suggestion into the validation rule 'box' in the table
design view and the programme helpfully told me that it contained invalid
syntax. Any Idea what I did wrong?

Basically what I want is this. People in my database can't work more than 8
normal time hours a day. Any hours worked more should be recorded as over
time. Thus, an employee works 6 hours in one day it will be recorded as 6
hours normal time. If an employee works 9 hours it will be recorded as 8
hours normal time and 1 hour over time.

I want to 'idiot proof' this data entry process because I live in Thailand
and the data entry people are bound to enter 6 hours normal time and 2 hours
over time (for which they get paid 50% extra) for their friends.

Cheers Wayne!

David

 

[Guest]

Hi David

The snipet of code does not go in the validation rule row.

Open the form in design view and right click the NormalTime control and open
the properties box. Go to the event column and slect the AfterUpdate row.
Click the build option (...). Select code

You will see this
Private Sub NormalTime_AfterUpdate()

End Sub

You need to add to this small bit of code between the lines

If Me.NormalTime <= 7 Then
Me.OverTime = 0
End If

So that you end up with this

Private Sub NormalTime_AfterUpdate()
If Me.NormalTime <= 7 Then
Me.OverTime = 0
End If
End Sub

Save and close the builder and view the form

You could also add a small message to the code as it is my experience the
users do not like entering data and haveing something else (or nothing)
happening without being told why.

 

[Guest]

Hi Wayne,

Sorry to be a bore...

I followed your instructions to the letter but when I test out the form I
can still enter values greater than 0 in the Over Time field when the value
in the Normal Time field is less than 8.

Does it matter that in Table Design view I have set the default value for
Over Time as 0 and put in a validation rule for Normal Time that ensures that
entries will be 8 or less?

I'm probably just explaining things all wrong. What I would like is that
only when the value of Normal Time is 8 can the value in Over Time be more
than 0.

Out of curiosity, why do you suggest to place these 'restrictions' in Form
Design rather than 'Table Design'? None of this is mentioned in my "Absolute
Beginner's Guide" for Access 2003!

Cheers,