Popup Box! Please Help...

J

JOM

I have a form that has a subform. An employee enters there hours per day
that they have worked in the subform. What i woudl like to do is have a
button on the main form that when the employee presses will tell them as
follows!

Total hours for today = 8
Total Hours for this week = 40

How will I do this?
 
K

Ken Sheridan

Firstly you need to have a way of defining the date range for the current
week, so, assuming your week runs Sunday – Saturday put the following
function in a standard module:

Public Function StartOfWeek() As Date

Dim dtmDate As Date

dtmDate = VBA.Date

Do Until Weekday(dtmDate, vbSunday) = 1
dtmDate = dtmDate - 1
Loop

StartOfWeek = dtmDate

End Function

I assume your main form, frmEmployees say, is based on an Employees table
with something like EmployeeID as its primary key, so on your dialogue form
include two text boxes, txtToday and txtThisWeek say. Lets assume the hours
are recorded in a table TimeSheet which includes fields EmployeeID, WorkDate
and HoursWorked. For the ControlSource property of txtToday put an expression
which references the main form's current EmployeeID:

=DSum("HoursWorked", "TimeSheet", "Workdate = #" &
Format(Date(),"mm/dd/yyyy") & "# And EmployeeID = " &
Forms!frmEmployees!EmployeeID)

For txtThisWeek the ControlSource would be an expression which calls the
above function:

=DSum("HoursWorked", "TimeSheet", "Workdate >= #" &
Format(StartOfWeek(),"mm/dd/yyyy") & "# And Workdate < #" &
Format(StartOfWeek()+7,"mm/dd/yyyy") &"# And EmployeeID = " &
Forms!frmEmployees!EmployeeID)

All you then have to do is open the dialogue form from the main form and it
should show the total hours entered for the current employee for today and
this week.
 
J

JOM

Thanks for the response, however, I am having a problem, you see the form
contains a subform, the subform is where the employee enter there hours the
information is stored in tblTrackHours which contains employeeID as a foreign
key...
The mainform is called tblEmployee... So I applied what you game me on an
unbound textbox on the main form but I am having an error, what could be the
problem?
 
K

Ken Sheridan

The fact that the hours are recorded in a subform doesn't matter as the data
is obtained form the table itself. What the expressions need to do is sum
the values of the field in the table where the EmployeeID value matches the
value of the EmployeeID in the main form's current record. For today it also
needs to restrict the rows summed to where the date value in the table
matches today's date, using the built in Date() function, and for the current
week to where the date is between the start and end of the week, which is
done by calling the custom StartOfWeek() function I gave you.

So the first thing to do is put the function, exactly as I gave it to you
into any standard module in your database. To do this you can use an
existing module or create a new one, so do this from the Modules tab of the
database window. All you have to do then is paste the complete code for the
function which I sent you below the two lines which Access automatically
inserts at the start of a module. Then save the module. If you've created a
new one don't call it StartOfWeek as this will cause confusion with the
function name. I always prefix module names with bas, e.g. basDateUtilities
for a module containing functions or procedures relating to dates.

The ControlSources of the two text boxes use the built in DSum function.
This has three arguments, all string expressions; the first is the field name
whose values you want to; the second is the domain, i.e. the name of the
table or query in question and the third is the criterion which restricts the
count to particular rows. The key thing in your case is to build a string
expression as the criteria for the calls to the DSum function in each text
box's ContolSource.

To get today's rows from the table for the current employee you need to
specify the EmployeeID and today's date, which you can get with the Date()
function. Dates are delimited by the # character and have to be in US format
or otherwise internationally unambiguous. This is why the Format function is
used to put the date in the mm/dd/yyyy format, which is essential for those
of us in Europe who use the dd/mm/yyyy format. The string expression is
built up by concatenating (using the & operator) literal strings (delimited
by quotes) with values from the form's current record or returned by the Date
function. So putting all this together the ControlSource for a text box to
sum today's hours for the current employee would be as follows. I've assumed
that the primary key of the main form's underlying table is also called
EmployeeID and the field in the tblTrackHours table which records the number
of hours worked is called HoursWorked and is a number data type. You'll need
to change this to whatever your field is actually called. I've also assumed
the field in the table which records the date on which the hours are worked
is called WorkDate and is a date/time data type. Again you'll need to change
the code to the actual name of your field:

=DSum("HoursWorked", "tblTrackHours", "WorkDate = #" &
Format(Date(),"mm/dd/yyyy") & "# And EmployeeID = " &
Forms!tblEmployee!EmployeeID)

For the week the principle is much the same, but this time the criterion for
the DSum function in this cases looks for dates between the start and end of
the week, using the StartOfWeek function. Again the dates need to be
formatted as mm/dd/yyyy to internationalise it, so the ControlSource for the
second text box would be as follows. In this case :

=DSum("HoursWorked", "tblTrackHours", "WorkDate >= #" &
Format(StartOfWeek(),"mm/dd/yyyy") & "# And WorkDate < #" &
Format(StartOfWeek()+7,"mm/dd/yyyy") &"# And EmployeeID = " &
Forms!tblEmployee!EmployeeID)

The expressions for the ControlSource properties of the two text boxes
should be entered as a single line in the properties sheet. They are spaced
over several lines here due to word wrapping within this window. To make it
easier, when you've selected the ControlSource property in the control's
properties sheet Press Shift+F2 to open the zoom window.

If you use text boxes on the main tblEmployee form you don't actually have
to refer to the form specifically in the code, you can just refer to the
EmployeeID field. If you do it on a separate dialogue form then you need the
full reference Forms!tblEmployee!EmployeeID. It doesn't matter if you use
the full reference if the controls are on the main form, however.
 
K

Ken Sheridan

I don't think JOM's problem is how to present the information to the user,
its how to compute it.
 
J

JOM

Thanks alot, that helped and it worked...

Ken Sheridan said:
The fact that the hours are recorded in a subform doesn't matter as the data
is obtained form the table itself. What the expressions need to do is sum
the values of the field in the table where the EmployeeID value matches the
value of the EmployeeID in the main form's current record. For today it also
needs to restrict the rows summed to where the date value in the table
matches today's date, using the built in Date() function, and for the current
week to where the date is between the start and end of the week, which is
done by calling the custom StartOfWeek() function I gave you.

So the first thing to do is put the function, exactly as I gave it to you
into any standard module in your database. To do this you can use an
existing module or create a new one, so do this from the Modules tab of the
database window. All you have to do then is paste the complete code for the
function which I sent you below the two lines which Access automatically
inserts at the start of a module. Then save the module. If you've created a
new one don't call it StartOfWeek as this will cause confusion with the
function name. I always prefix module names with bas, e.g. basDateUtilities
for a module containing functions or procedures relating to dates.

The ControlSources of the two text boxes use the built in DSum function.
This has three arguments, all string expressions; the first is the field name
whose values you want to; the second is the domain, i.e. the name of the
table or query in question and the third is the criterion which restricts the
count to particular rows. The key thing in your case is to build a string
expression as the criteria for the calls to the DSum function in each text
box's ContolSource.

To get today's rows from the table for the current employee you need to
specify the EmployeeID and today's date, which you can get with the Date()
function. Dates are delimited by the # character and have to be in US format
or otherwise internationally unambiguous. This is why the Format function is
used to put the date in the mm/dd/yyyy format, which is essential for those
of us in Europe who use the dd/mm/yyyy format. The string expression is
built up by concatenating (using the & operator) literal strings (delimited
by quotes) with values from the form's current record or returned by the Date
function. So putting all this together the ControlSource for a text box to
sum today's hours for the current employee would be as follows. I've assumed
that the primary key of the main form's underlying table is also called
EmployeeID and the field in the tblTrackHours table which records the number
of hours worked is called HoursWorked and is a number data type. You'll need
to change this to whatever your field is actually called. I've also assumed
the field in the table which records the date on which the hours are worked
is called WorkDate and is a date/time data type. Again you'll need to change
the code to the actual name of your field:

=DSum("HoursWorked", "tblTrackHours", "WorkDate = #" &
Format(Date(),"mm/dd/yyyy") & "# And EmployeeID = " &
Forms!tblEmployee!EmployeeID)

For the week the principle is much the same, but this time the criterion for
the DSum function in this cases looks for dates between the start and end of
the week, using the StartOfWeek function. Again the dates need to be
formatted as mm/dd/yyyy to internationalise it, so the ControlSource for the
second text box would be as follows. In this case :

=DSum("HoursWorked", "tblTrackHours", "WorkDate >= #" &
Format(StartOfWeek(),"mm/dd/yyyy") & "# And WorkDate < #" &
Format(StartOfWeek()+7,"mm/dd/yyyy") &"# And EmployeeID = " &
Forms!tblEmployee!EmployeeID)

The expressions for the ControlSource properties of the two text boxes
should be entered as a single line in the properties sheet. They are spaced
over several lines here due to word wrapping within this window. To make it
easier, when you've selected the ControlSource property in the control's
properties sheet Press Shift+F2 to open the zoom window.

If you use text boxes on the main tblEmployee form you don't actually have
to refer to the form specifically in the code, you can just refer to the
EmployeeID field. If you do it on a separate dialogue form then you need the
full reference Forms!tblEmployee!EmployeeID. It doesn't matter if you use
the full reference if the controls are on the main form, however.
Click to expand...
 
J

JOM

I have a question, how will apply it to the month that is to get totals for
this month?
 
K

Ken Sheridan

You can use the built in Year and Month functions to do this, so the
expression would be:

=DSum("HoursWorked", "TimeSheet", "Year(Workdate) = " & Year(Date()) & " And
Month(Workdate) = " & Month(Date()) & " And EmployeeID = " &
Forms!frmEmployees!EmployeeID)

:
 
J

JOM

Thanks alot for your help, This worked.

Ken Sheridan said:
You can use the built in Year and Month functions to do this, so the
expression would be:

=DSum("HoursWorked", "TimeSheet", "Year(Workdate) = " & Year(Date()) & " And
Month(Workdate) = " & Month(Date()) & " And EmployeeID = " &
Forms!frmEmployees!EmployeeID)

:
Click to expand...
 
Top