form help plz - control source limits

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hi everyone. Im very very new at access so be patient. haha

this database was designed by a previous person and I need to limit this
addition to 60. If it goes over 60 then a msgbox should pop up that doesnt
allow them to add any more time.
I have this as the control source:

=nz([time sheet projects subform].[Form]![weektotal])+nz([time sheet
non-projects subform].[Form]![weektotal])

I guess i should mention that It just displays in a greyed out textbox.
Thanks for any help, Much appreciated!
 
James,

Your TotalTime control is a calculated value based on the projects subform
time and the non-project time. There isn't a lot you can do with this
field. It is going to display the total of what's entered in the two
subforms.

What you should look at is putting some code into each of the subforms to
lookup the other subform's entry and then either warn or stop the user if
the two entries together exceed your cutoff. Here's some sample code.
You'll have to modify it for your application.

In the Form_BeforeUpdate event of each subform put something like the
following:

Dim f as Form
Dim rs as Recordset
Dim intHours as Integer 'Use sngHours as Single if
you need fractions.

Set f = Parent.OtherSubform.Form 'You want to look at the other
subform.
Set rs = f.RecordsetClone

rs.FindFirst "EmployeeID = " & Me!EmployeeID
If rs.NoMatch Then
intHours = 0
Else
intHours = rs!HoursWorked
End If

If Me!HoursWorked + intHours > 60 Then 'Or whatever criteria you
want
MsgBox "You need a vacation", vbExclamation
Cancel = True 'Setting Cancel to True cancels the
addition/update of the record.
End If

Set rs = Nothing
Set f = Nothing


Hope this helps
Scott
 
Back
Top