need help with this

[ياسر]

i have a form with sub form
and the first field is date and last field is number
So
i want to make sum for every day so that in last field it must not be more
than number 8
i mean that
when user enter for example in field one (date ) in the sub form
and ended the record with entering in last field number 3
then started new record with the same date but entered new number in last
field the total for the same date must not be more than 8
but if he started new record and entered new date the last field begin new
summation for this record and also not more than 8
etc....
thats all i need
in simple way i have every day 8 hours for each day but the user can input
them in many records but not more than 8 hours for the total of the same date
..
So what can i do?

 

[KARL DEWEY]

DLookup will return the daily total for presentation in a text box on the form.
What do you want to happen or not happen if the total does exceed 8 for the
day?

 

[ياسر]

good day
i want to do this
for every several record with the same date the result must not be more than
8 then if the user started new record with new date the result for the new
record is the same as last record not more than 8

so i think that dlookup is not the right thing to do

 

[ياسر]

where is the answer?

good day
i want to do this
for every several record with the same date the result must not be more than
8 then if the user started new record with new date the result for the new
record is the same as last record not more than 8

so i think that dlookup is not the right thing to do

 

[John W. Vinson]

good day
i want to do this
for every several record with the same date the result must not be more than
8 then if the user started new record with new date the result for the new
record is the same as last record not more than 8

so i think that dlookup is not the right thing to do

I do not understand the question.

Please give an example of the kind of data and the desired result.

 

[ياسر]

how can i calculate the sum of many rows for one day?
i mean :
i have a sub form with the following fields:
date - task- department -work description - Regular Hours - Over Time
by the way regular time must not be more than number 8
what i need is:
when user insert in the date for example:
1/1/2009 and ended with regular time he put 3 then the remained regular time
is 5
then in new record we insert the same date but the regular time must not be
more than the remained number ( 5 )
so
what i need is to sum the total numbers inserted for the same date so that
user can't input more than number 8 in all the sub records for the same date
but in new record with other date the numbers is to begin from 1 to 8
no more
so help me please

 

[John W. Vinson]

how can i calculate the sum of many rows for one day?
i mean :
i have a sub form with the following fields:

A Form does not have fields. A Form does not store data.

A Form is JUST A WINDOW, a way to interact with data stored in Tables. Data is
stored in tables and noplace else!

As requested: please describe *the structure of your tables*.

date - task- department -work description - Regular Hours - Over Time
by the way regular time must not be more than number 8
what i need is:
when user insert in the date for example:
1/1/2009 and ended with regular time he put 3 then the remained regular time
is 5
then in new record we insert the same date but the regular time must not be
more than the remained number ( 5 )
so
what i need is to sum the total numbers inserted for the same date so that
user can't input more than number 8 in all the sub records for the same date
but in new record with other date the numbers is to begin from 1 to 8
no more
so help me please

So what do you want to happen when the user types in 3 for a first record, and
then enters a 7 for the second record? What should Access do? Change the entry
to 5; pop up an error message box; fire the employee???

You would use DSum() - not DLookUp - to sum the previous entries for that day,
probably in the Form's BeforeUpdate event; but since I do not know the
structure of your tables (which DSum must reference), I do not know how to
write the DSum() statement.

I really have to question the design here. If you're recording hours worked,
you should *record the actual hours worked* - minutes, actually, so someone
who works 6 hours 45 minutes gets credit for 6 hours 45 minutes; and the
program should figure out what is regular time and what is overtime.

 

[ياسر]

I want to do this
for every day example
1/1/2009 the user entered some numbers for the regular time he works
he must not enter more than 8 hours for the same day band every day so i
want to do some calculation for this
so the code calculate the total hours for the same employee for the same day
and every day and not more than 8 hours
the employee add date then he add some tasks and work description the
regular time for the current date
so i need to force him not to add more than 8 hours for all records for the
same date the another 8 hours for other date and so on.....
so what can i do to calculate the sum of hours for one day by many records?
please help me

 

[John W. Vinson]

I want to do this
for every day example
1/1/2009 the user entered some numbers for the regular time he works
he must not enter more than 8 hours for the same day band every day so i
want to do some calculation for this
so the code calculate the total hours for the same employee for the same day
and every day and not more than 8 hours
the employee add date then he add some tasks and work description the
regular time for the current date
so i need to force him not to add more than 8 hours for all records for the
same date the another 8 hours for other date and so on.....
so what can i do to calculate the sum of hours for one day by many records?
please help me

I will help you...

IF you will cooperate.

As I have requested twice now, please post the structure of your table (table
name, fieldnames and datatypes).

I cannot help you in a vacuum. I CANNOT SEE YOUR DATABASE.

 

[ياسر]

ok
table name is
Employee Weekly Hours
fields names
Idnumber text
Task text
Event ID text
Work description text
Discipline text
Dates date and time
regular time number fixed
department text
thats all
and i said what need before
so i'm waiting for your help
i need for each employee to enter many rows for the same date but different
regular time and not more than 8 hours for all rows for the same date but in
other date user can also enter new date with new task and other fields i
mention them above and with many hours also but not more than 8 hours
it doesn't matter if he enter in first row in regular time number 3 or 4 or
else but total rows for the current date for the same user not more than 8
hours foe one day

 

[John W. Vinson]

ok
table name is
Employee Weekly Hours
fields names
Idnumber text
Task text
Event ID text
Work description text
Discipline text
Dates date and time
regular time number fixed
department text
thats all
and i said what need before
so i'm waiting for your help
i need for each employee to enter many rows for the same date but different
regular time and not more than 8 hours for all rows for the same date but in
other date user can also enter new date with new task and other fields i
mention them above and with many hours also but not more than 8 hours
it doesn't matter if he enter in first row in regular time number 3 or 4 or
else but total rows for the current date for the same user not more than 8
hours foe one day

Then: put the following code in the Form's BeforeUpdate event. Open the form
in design view; view its Properties. I'll assume that Idnumber is the
*employee's* ID number since there does not seem to be any other appropriate
fieldname; if it's not, how do you know which record belongs to which
employee? And you refer to "new task" - there is nothing (that I can see) in
your table to identify a "task"; is that perhaps the EventID, or does it
matter?

On the Events Tab find the Before update event and click the ... icon by it;
choose Code Builder. Edit in the following code:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

Again, I'm really concerned about the design. Are you REALLY only entering
whole numbers, integers, for hours? If someone works 7 hours, 58 minutes you
only pay them for 7 hours? You could get into legal trouble!

 

[ياسر]

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the code you
write to me
i did this in sub form
-----------------------------------------------------------------------
date task event work description
regular time
------------------------------------------------------------------------
8/9/2009 projects BnP Bank Project manager 3
8/9/2009 projects BnP Bank Project director 5
8/9/2009 projects BnP Bank Project manager 4

----

12


9/9/2009 projects BnP Bank Project manager 4
9/9/2009 projects BnP Bank Project manager 5

------

9
----------------------------------------------------------------------
this is what i need to do
for the same date
the sum of all regular time for the same idnumber with different work
descriptions and different regular times can't be more than 8 for all the day
not for one row
and when user change date its value also is no more than 8 hours for the new
day with different rows for the same idnumber
Waiting for your replay and helps
It doesn't matter for the company for the fraction of hours
and this time sheet is not for payroll its calculate only how many time the
employee works on different projects and total time for the company for the
different projects thats all
so Wish you understand what i need and sorry for interrupting you but
please help its important to me to do this .

ok
table name is
Employee Weekly Hours
fields names
Idnumber text
Task text
Event ID text
Work description text
Discipline text
Dates date and time
regular time number fixed
department text
thats all
and i said what need before
so i'm waiting for your help
i need for each employee to enter many rows for the same date but different
regular time and not more than 8 hours for all rows for the same date but in
other date user can also enter new date with new task and other fields i
mention them above and with many hours also but not more than 8 hours
it doesn't matter if he enter in first row in regular time number 3 or 4 or
else but total rows for the current date for the same user not more than 8
hours foe one day

Then: put the following code in the Form's BeforeUpdate event. Open the form
in design view; view its Properties. I'll assume that Idnumber is the
*employee's* ID number since there does not seem to be any other appropriate
fieldname; if it's not, how do you know which record belongs to which
employee? And you refer to "new task" - there is nothing (that I can see) in
your table to identify a "task"; is that perhaps the EventID, or does it
matter?

On the Events Tab find the Before update event and click the ... icon by it;
choose Code Builder. Edit in the following code:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

Again, I'm really concerned about the design. Are you REALLY only entering
whole numbers, integers, for hours? If someone works 7 hours, 58 minutes you
only pay them for 7 hours? You could get into legal trouble!

 

[John W. Vinson]

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the code you
write to me

OK, try this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") _
+ NZ(Me![regular time] > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

 

[ياسر]

Sorry Not Working What can i do ?
its continue working with any number entered for one row and many rows!

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the code you
write to me

OK, try this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") _
+ NZ(Me![regular time] > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

 

[John W. Vinson]

Sorry Not Working What can i do ?

"Not working" is vague. What's happening?

What is the Recordsource of the Form upon which you're doing this?

Does the form have any Subforms? If so, which Subform has [Employee Weekly
Hours] in its Recordsource? Is it *that* subform which has this code? Does
this subform have controls named Idnumber and Dates?

its continue working with any number entered for one row and many rows!

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the code you
write to me

OK, try this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") _
+ NZ(Me![regular time] > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

 

[ياسر]

the record source is:
SELECT [Employee Weekly Hours].Idnumber, [Employee Weekly Hours].Task,
[Employee Weekly Hours].[Event ID], [Employee Weekly Hours].Working,
[Employee Weekly Hours].Discipline, [Employee Weekly Hours].departement,
[Employee Weekly Hours].Work_desc, [Employee Weekly Hours].Dates, [Employee
Weekly Hours].Regular_time, [Employee Weekly Hours].Over_time, [Employee
Weekly Hours].Approveing FROM [Employee Weekly Hours] WHERE ((([Employee
Weekly Hours].Approveing)=0));
and the name of the sub form i need to work on it is:
Employee Weekly Hours Subform
and the name of the main form which the sub form belongs to is Emp timesheet
thats all

Sorry Not Working What can i do ?

"Not working" is vague. What's happening?

What is the Recordsource of the Form upon which you're doing this?

Does the form have any Subforms? If so, which Subform has [Employee Weekly
Hours] in its Recordsource? Is it *that* subform which has this code? Does
this subform have controls named Idnumber and Dates?

its continue working with any number entered for one row and many rows!

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the code you
write to me

OK, try this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") _
+ NZ(Me![regular time] > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

 

[ياسر]

Where is the help
i'm still waiting for your kindly help so please replay me

Sorry Not Working What can i do ?

"Not working" is vague. What's happening?

What is the Recordsource of the Form upon which you're doing this?

Does the form have any Subforms? If so, which Subform has [Employee Weekly
Hours] in its Recordsource? Is it *that* subform which has this code? Does
this subform have controls named Idnumber and Dates?

its continue working with any number entered for one row and many rows!

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the code you
write to me

OK, try this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") _
+ NZ(Me![regular time] > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

 

[Guest]

Sorry Not Working What can i do ?
its continue working with any number entered for one row and many rows!

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the
code you
write to me

OK, try this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") _
+ NZ(Me![regular time] > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

 

[ياسر]

Thank You Very much
I'm so Happy That Its at end worked well
But without using Nz at the end of the statement
I'm soooooo proud of your good help and many many thanks
i hope that i wasn't making any trouble for you
once again Thank you

Sorry Not Working What can i do ?

"Not working" is vague. What's happening?

What is the Recordsource of the Form upon which you're doing this?

Does the form have any Subforms? If so, which Subform has [Employee Weekly
Hours] in its Recordsource? Is it *that* subform which has this code? Does
this subform have controls named Idnumber and Dates?

its continue working with any number entered for one row and many rows!

Thanks but thats not working
i have entered about three records for the same idnumber with different
tasks for the same time but different hours but i didn't active the code you
write to me

OK, try this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DSum("[regular time]", "[Employee Weekly Hours]", _
"[Idnumber] = '" & Me!Idnumber] & "' AND DateValue([Dates]) = #" _
& DateValue(Me![Dates]) & "#") _
+ NZ(Me![regular time] > 8 Then
MsgBox "No more than 8 hours regular time allowed", vbOKOnly
Cancel = True
End If
End Sub

 

[MR.GADGET]

i have a form with sub form
and the first field is date and last field is number
So
i want to make sum for every day so that in last field it must not be more
than number 8
i mean that
when user enter for example in field one (date ) in the sub form
and ended the record with entering in last field number 3
then started new record with the same date but entered new number in last
field the total for the same date must not be more than 8
but if he started new record and entered new date the last field begin new
summation for this record and also not more than 8
etc....
thats all i need
in simple way i have every day 8 hours for each day but the user can input
them in many records but not more than 8 hours for the total of the same
date
.
So what can i do?