Noobie Question

[James]

Hi everyone. Im very new to Access. Im having trouble creating a cap for
hours in a time sheet database. Here is what I have

I have a SQL Table with Recorded Comp Time and Leave of absence called "Time
Sheet" I also made a query which has the persons Identy, that weeks date and
the total Comp/LOA for that week which looks like this (in design):

Field: Badge FridayDate CompLOA
Table: Time Sheet Time Sheet Time Sheet
Total: Group By Group By Sum
Sort: Ascending

although I dont think the sum does anything.
Well what I need to do is Create a MsgBox that pops up and tells the user
that their accumulated CompLOA time is >60 and prevent them from entering
anything over 60 in the field on the form. I dont know if I should research
doing it in say a criteria field of a query or if there is some other way to
do it in the properties dialog box. VBA maybe? I dont know. Thanks if anyone
has some input. Any questions please ask...dont know if this is the clearest
explination

 

[Keith Wilby]

Hi everyone. Im very new to Access. Im having trouble creating a cap for
hours in a time sheet database. Here is what I have

I have a SQL Table with Recorded Comp Time and Leave of absence called
"Time
Sheet" I also made a query which has the persons Identy, that weeks date
and
the total Comp/LOA for that week which looks like this (in design):

Field: Badge FridayDate CompLOA
Table: Time Sheet Time Sheet Time Sheet
Total: Group By Group By Sum
Sort: Ascending

although I dont think the sum does anything.
Well what I need to do is Create a MsgBox that pops up and tells the user
that their accumulated CompLOA time is >60 and prevent them from entering
anything over 60 in the field on the form. I dont know if I should
research
doing it in say a criteria field of a query or if there is some other way
to
do it in the properties dialog box. VBA maybe? I dont know. Thanks if
anyone
has some input. Any questions please ask...dont know if this is the
clearest
explination

You don't mention using a form but that is what your users should be using.
You then have the luxury of events that you can attach code to, so for
example, in the form's Before Update event you could have something like:

If DSum("MyField","qryMyQuery","MyCriteria") >60 Then
MsgBox "Maximum hours reached."
Cancel = True
Me.Undo
End If

That will undo any changes made if the maximum hours for a particular user
have been reached. Note: the Undo command will undo all pending changes to
the current record, leave out that line if that's not what you want.

Keith.
www.keithwilby.co.uk

 

[James]

Thanks for the reply.
im still a little confused though.
with respect to :
If DSum("MyField","qryMyQuery","MyCriteria") >60 Then

I guess the DSum( ) will restrict "MyField" to only return results with
"MyCriteria" in the field....correct? Well if so, How do I make MyCriteria
(which is Badge also) = TimeSheetForm.Badge(this Badge is the greyed out
Textbox value) what is the syntax?

also, "qryMyQuery" can be a table or a query correct? because i already have
a table with all the information in it, no need for a query .....yes/no??

sorry, I know so little its rediculous. haha. Thanks again for all the help.
This board has been great!

 

[Keith Wilby]

Thanks for the reply.
im still a little confused though.
with respect to :
If DSum("MyField","qryMyQuery","MyCriteria") >60 Then

I guess the DSum( ) will restrict "MyField" to only return results with
"MyCriteria" in the field....correct?
Yes.

Well if so, How do I make MyCriteria
(which is Badge also) = TimeSheetForm.Badge(this Badge is the greyed out
Textbox value) what is the syntax?

Susbstitute "MyField" with the actual field name (Badge?).

also, "qryMyQuery" can be a table or a query correct? because i already
have
a table with all the information in it, no need for a query .....yes/no??

"qryMyQuery" can be your table, yes.

Keith.