Current date in last subform record

[BruceM]

I have an employment summary form for keeping a record of employee
employment history. I have a main form based on the Employees table and a
subform based on a query, which is in turn based on a Details table that
stores Employer, StartDate, and EndDate. The Details query contains a
calculated field (using DateDiff) for the amount of time elapsed between
StartDate and EndDate. The Details subform contains a text box in the
footer for adding up the total years in the calculated fields for all
related records:

Employer 1 2/1/03 1/15/04
MyCompany 2/1/04 (Current Date)

If it matters, the DateDiff calculation determines the number of months.
The TotalYears text box in the footer divides the sum by 12 and rounds up to
a whole year. It works as intended. The modification I would like to make
is that if the employer is the current company (MyCompany) I would like
EndDate to be the current date unless the employee no longer works here, in
which case I would like to lock in the date the employee left. There is an
Inactive field (Yes/No) in the Employees table. This last step (locking in
the end date) doesn't need to be automated, although it would be good if it
was. It would be OK to just have a message box pop up when Inactive is
checked, directing the user to take some action to lock in the end date.
I can get the subform record to show the current date as I would like by
using the Current event for the subform, but it updates only when I click
into the subform record that lists MyCompany as the employer. I need to
have the total updated automatically when the user looks at the employee's
employment summary record.

 

[Wayne Morgan]

You don't say what you're doing to calculate the number of days, years, etc.
However, you might be able to use the Nz() function to solve the problem.

Example:
Nz([EndDate], Date())

If the EndDate field is Null, then today's date will be substituted. Use the
statement above in the equation instead of just the EndDate field.

When checking the Inactive checkbox, you could fill in the EndDate, but the
problem with that is if you don't check the box on the same day the employee
leaves, you're going to have to manually fill in the date anyway. Instead, I
would recommend that filling in a date would show the checkbox as checked.
It wouldn't be necessary to store the checkbox's value, it could just be a
calculated control on the form. If there is a value for EndDate in the last
record (unless you keep records for where an employee went to after they
left your place) for the employee then the checkbox would be True, otherwise
it would be False.

To keep what you have and just prompt the user to enter a date try the
following in the AfterUpdate event of the checkbox:

If Me.chkMyCheckbox = True Then
Msgbox "Please enter an EndDate for the employee."
Me.txtEndDate.SetFocus
End If

 

[Wayne Morgan]

PS.

If the checkbox is on the main form, then to refer to the EndDate on the
subform, amend the code as follows:

If Me.chkMyCheckbox = True Then
Msgbox "Please enter an EndDate for the employee."
Me.NameOfSubformControl.SetFocus
Me.NameOfSubformControl.Form.txtEndDate.SetFocus
Me.NameOfSubformControl.Form.Recordset.MoveLast
End If

 

[BruceM]

Thank you very much for your reply. I could have been clearer in my
posting.
To calculate the time I first use DateDiff in the subform record source
query to calculate the number of months:
EmployerTime: DateDiff("m",[StartDate],[EndDate])
Then in the subform footer I have a hidden text box with the Record Source
(the value of which is passed to another unbound text box on the main form):
=-Int(-Sum([EmployerTime])/12)
This rounding up is based on something I found in a group search. I used
months and converted to years by dividing by twelve because I couldn't find
a way to make DateDiff show one year for somebody who has been here for six
months. We are only required to keep track by years, and rounding up is
adequate, but showing zero years experience is not the best thing. This
database fulfills an administrative requirement, and is not intended to be a
comprehensive summary.
It occurred to me that I don't think there is a problem with the EndDate.
The query on which the listing of employees is based includes only Active
employees (i.e. the Inactive check box is not checked). The way it works is
that if the Employer is MyCurrentCompany, EndDate is today's date. This is
set in the subform's Current event. I have added the additional condition
that today's date is used only if the Employee is Active. This will help to
guard against changing the end date if I ever need to modify the record
source to include former (Inactive) employees. If the employee becomes
Inactive, the exact tally of years experience is not important. Once a year
we review the records. The current date is set at that time. If the
employee is Inactive, the last date the record was reviewed becomes the
EndDate. Good enough for an almost meaningless administrative chore. If we
need more accuracy I can have the user modify the record if the Inactive box
is checked on another form.
However, there is an issue with modifying the records that I have not been
able to solve. The subform is continuous view. The DateDiff expression
works only if I sort the records in the subform record source query in
Descending order by StartDate so that MyCurrentCompany is at the top of the
listing. (I discovered that I could get it to work in this manner after I
posted the question.) If I sort in Ascending order, so that the first
employer is at the top of the listing and MyCurrentCompany is at the bottom,
I cannot find a way to use the procedure of using the current date for the
EndDate. The subform's On Current event is along the lines of:
If Me.Employer = "MyCurrentCompany" then
Me.EndDate = Date
End If

Thanks too for your PS posting. As I mentioned, I don't think I will need
to modify the record's EndDate field, but if I need to add that
functionality I don't think I would have known to switch the focus to the
subform control first, then to the text box on the subform. No doubt I will
use that information over and over.
By the way, I realize that one approach would be to have EndDate (i.e.
CurrentDate) for MyCurrentCompany be in an unbound text box on the subform,
but by storing the EndDate value (and modifying it through code when viewing
the record) I should be able to reduce maintenance in a situation where, as
I mentioned, great precision is not needed. Former employees are not of
much interest to auditors and such. If detail is needed it will be found in
HR, not in my employment summary database.

You don't say what you're doing to calculate the number of days, years,
etc. However, you might be able to use the Nz() function to solve the
problem.

Example:
Nz([EndDate], Date())

If the EndDate field is Null, then today's date will be substituted. Use
the statement above in the equation instead of just the EndDate field.

When checking the Inactive checkbox, you could fill in the EndDate, but
the problem with that is if you don't check the box on the same day the
employee leaves, you're going to have to manually fill in the date anyway.
Instead, I would recommend that filling in a date would show the checkbox
as checked. It wouldn't be necessary to store the checkbox's value, it
could just be a calculated control on the form. If there is a value for
EndDate in the last record (unless you keep records for where an employee
went to after they left your place) for the employee then the checkbox
would be True, otherwise it would be False.

To keep what you have and just prompt the user to enter a date try the
following in the AfterUpdate event of the checkbox:

If Me.chkMyCheckbox = True Then
Msgbox "Please enter an EndDate for the employee."
Me.txtEndDate.SetFocus
End If

--
Wayne Morgan
MS Access MVP

I have an employment summary form for keeping a record of employee
employment history. I have a main form based on the Employees table and a
subform based on a query, which is in turn based on a Details table that
stores Employer, StartDate, and EndDate. The Details query contains a
calculated field (using DateDiff) for the amount of time elapsed between
StartDate and EndDate. The Details subform contains a text box in the
footer for adding up the total years in the calculated fields for all
related records:

Employer 1 2/1/03 1/15/04
MyCompany 2/1/04 (Current Date)

If it matters, the DateDiff calculation determines the number of months.
The TotalYears text box in the footer divides the sum by 12 and rounds up
to a whole year. It works as intended. The modification I would like to
make is that if the employer is the current company (MyCompany) I would
like EndDate to be the current date unless the employee no longer works
here, in which case I would like to lock in the date the employee left.
There is an Inactive field (Yes/No) in the Employees table. This last
step (locking in the end date) doesn't need to be automated, although it
would be good if it was. It would be OK to just have a message box pop
up when Inactive is checked, directing the user to take some action to
lock in the end date.
I can get the subform record to show the current date as I would like by
using the Current event for the subform, but it updates only when I click
into the subform record that lists MyCompany as the employer. I need to
have the total updated automatically when the user looks at the
employee's employment summary record.

 

[Wayne Morgan]

EmployerTime: DateDiff("m",[StartDate],[EndDate])

What happens if you change the above to

EmployerTime: DateDiff("m",[StartDate],Nz([EndDate], Date()))

--
Wayne Morgan
MS Access MVP

Thank you very much for your reply. I could have been clearer in my
posting.
To calculate the time I first use DateDiff in the subform record source
query to calculate the number of months:
EmployerTime: DateDiff("m",[StartDate],[EndDate])
Then in the subform footer I have a hidden text box with the Record Source
(the value of which is passed to another unbound text box on the main
form):
=-Int(-Sum([EmployerTime])/12)
This rounding up is based on something I found in a group search. I used
months and converted to years by dividing by twelve because I couldn't
find a way to make DateDiff show one year for somebody who has been here
for six months. We are only required to keep track by years, and rounding
up is adequate, but showing zero years experience is not the best thing.
This database fulfills an administrative requirement, and is not intended
to be a comprehensive summary.
It occurred to me that I don't think there is a problem with the EndDate.
The query on which the listing of employees is based includes only Active
employees (i.e. the Inactive check box is not checked). The way it works
is that if the Employer is MyCurrentCompany, EndDate is today's date.
This is set in the subform's Current event. I have added the additional
condition that today's date is used only if the Employee is Active. This
will help to guard against changing the end date if I ever need to modify
the record source to include former (Inactive) employees. If the employee
becomes Inactive, the exact tally of years experience is not important.
Once a year we review the records. The current date is set at that time.
If the employee is Inactive, the last date the record was reviewed becomes
the EndDate. Good enough for an almost meaningless administrative chore.
If we need more accuracy I can have the user modify the record if the
Inactive box is checked on another form.
However, there is an issue with modifying the records that I have not been
able to solve. The subform is continuous view. The DateDiff expression
works only if I sort the records in the subform record source query in
Descending order by StartDate so that MyCurrentCompany is at the top of
the listing. (I discovered that I could get it to work in this manner
after I posted the question.) If I sort in Ascending order, so that the
first employer is at the top of the listing and MyCurrentCompany is at the
bottom, I cannot find a way to use the procedure of using the current date
for the EndDate. The subform's On Current event is along the lines of:
If Me.Employer = "MyCurrentCompany" then
Me.EndDate = Date
End If

Thanks too for your PS posting. As I mentioned, I don't think I will need
to modify the record's EndDate field, but if I need to add that
functionality I don't think I would have known to switch the focus to the
subform control first, then to the text box on the subform. No doubt I
will use that information over and over.
By the way, I realize that one approach would be to have EndDate (i.e.
CurrentDate) for MyCurrentCompany be in an unbound text box on the
subform, but by storing the EndDate value (and modifying it through code
when viewing the record) I should be able to reduce maintenance in a
situation where, as I mentioned, great precision is not needed. Former
employees are not of much interest to auditors and such. If detail is
needed it will be found in HR, not in my employment summary database.

You don't say what you're doing to calculate the number of days, years,
etc. However, you might be able to use the Nz() function to solve the
problem.

Example:
Nz([EndDate], Date())

If the EndDate field is Null, then today's date will be substituted. Use
the statement above in the equation instead of just the EndDate field.

When checking the Inactive checkbox, you could fill in the EndDate, but
the problem with that is if you don't check the box on the same day the
employee leaves, you're going to have to manually fill in the date
anyway. Instead, I would recommend that filling in a date would show the
checkbox as checked. It wouldn't be necessary to store the checkbox's
value, it could just be a calculated control on the form. If there is a
value for EndDate in the last record (unless you keep records for where
an employee went to after they left your place) for the employee then the
checkbox would be True, otherwise it would be False.

To keep what you have and just prompt the user to enter a date try the
following in the AfterUpdate event of the checkbox:

If Me.chkMyCheckbox = True Then
Msgbox "Please enter an EndDate for the employee."
Me.txtEndDate.SetFocus
End If

--
Wayne Morgan
MS Access MVP

I have an employment summary form for keeping a record of employee
employment history. I have a main form based on the Employees table and
a subform based on a query, which is in turn based on a Details table
that stores Employer, StartDate, and EndDate. The Details query contains
a calculated field (using DateDiff) for the amount of time elapsed
between StartDate and EndDate. The Details subform contains a text box
in the footer for adding up the total years in the calculated fields for
all related records:

Employer 1 2/1/03 1/15/04
MyCompany 2/1/04 (Current Date)

If it matters, the DateDiff calculation determines the number of months.
The TotalYears text box in the footer divides the sum by 12 and rounds
up to a whole year. It works as intended. The modification I would
like to make is that if the employer is the current company (MyCompany)
I would like EndDate to be the current date unless the employee no
longer works here, in which case I would like to lock in the date the
employee left. There is an Inactive field (Yes/No) in the Employees
table. This last step (locking in the end date) doesn't need to be
automated, although it would be good if it was. It would be OK to just
have a message box pop up when Inactive is checked, directing the user
to take some action to lock in the end date.
I can get the subform record to show the current date as I would like by
using the Current event for the subform, but it updates only when I
click into the subform record that lists MyCompany as the employer. I
need to have the total updated automatically when the user looks at the
employee's employment summary record.

 

[BruceM]

Thanks for responding. I see how this works. When I tried it the query
returned the count in months even if EndDate is null. I would be viewing
the calculated value each time the query is run, but not rewriting the
stored valued each time. In the case of a current employee there is no end
date, so I could modify the EndDate text box to show the word "Current" if
EndDate is null, which would be clearer than the way it is now.
The thing I can't quite figure out is how to avoid storing the value. I
want to see the stored EndDate value for former employers, since it is not
changing, but would want to keep it to the current date for the current
employer. If txtEndDate is an unbound text box I would need another way to
see (and input, when I create a record for a new employee) the stored
EndDate values for past employers. If txtEndDate is bound to EndDate then
the Nz test will apply only once, after which there will be a value in the
field. Or am I completely missing your point?
On another point, I still am limited to sorting in descending date order, so
that the current employer is the first record in the listing of employers.
Otherwise I need to click into the record in order for the code to run. I
tried setting the Current event to go to the last record, but then of course
I can never go to any other record. Perhaps there is a way of using the
Current event for the main form to send the subform to the last record, at
which time the Date code will run and the current date will appear, but I
have something that is functional enough, and I need to move on to other
projects for the next few days. Still, I would like to find a way of
solving some of the issues that have arisen here.

EmployerTime: DateDiff("m",[StartDate],[EndDate])

What happens if you change the above to

EmployerTime: DateDiff("m",[StartDate],Nz([EndDate], Date()))

--
Wayne Morgan
MS Access MVP

Thank you very much for your reply. I could have been clearer in my
posting.
To calculate the time I first use DateDiff in the subform record source
query to calculate the number of months:
EmployerTime: DateDiff("m",[StartDate],[EndDate])
Then in the subform footer I have a hidden text box with the Record
Source (the value of which is passed to another unbound text box on the
main form):
=-Int(-Sum([EmployerTime])/12)
This rounding up is based on something I found in a group search. I used
months and converted to years by dividing by twelve because I couldn't
find a way to make DateDiff show one year for somebody who has been here
for six months. We are only required to keep track by years, and
rounding up is adequate, but showing zero years experience is not the
best thing. This database fulfills an administrative requirement, and is
not intended to be a comprehensive summary.
It occurred to me that I don't think there is a problem with the EndDate.
The query on which the listing of employees is based includes only Active
employees (i.e. the Inactive check box is not checked). The way it works
is that if the Employer is MyCurrentCompany, EndDate is today's date.
This is set in the subform's Current event. I have added the additional
condition that today's date is used only if the Employee is Active. This
will help to guard against changing the end date if I ever need to modify
the record source to include former (Inactive) employees. If the
employee becomes Inactive, the exact tally of years experience is not
important. Once a year we review the records. The current date is set at
that time. If the employee is Inactive, the last date the record was
reviewed becomes the EndDate. Good enough for an almost meaningless
administrative chore. If we need more accuracy I can have the user modify
the record if the Inactive box is checked on another form.
However, there is an issue with modifying the records that I have not
been able to solve. The subform is continuous view. The DateDiff
expression works only if I sort the records in the subform record source
query in Descending order by StartDate so that MyCurrentCompany is at the
top of the listing. (I discovered that I could get it to work in this
manner after I posted the question.) If I sort in Ascending order, so
that the first employer is at the top of the listing and MyCurrentCompany
is at the bottom, I cannot find a way to use the procedure of using the
current date for the EndDate. The subform's On Current event is along
the lines of:
If Me.Employer = "MyCurrentCompany" then
Me.EndDate = Date
End If

Thanks too for your PS posting. As I mentioned, I don't think I will
need to modify the record's EndDate field, but if I need to add that
functionality I don't think I would have known to switch the focus to the
subform control first, then to the text box on the subform. No doubt I
will use that information over and over.
By the way, I realize that one approach would be to have EndDate (i.e.
CurrentDate) for MyCurrentCompany be in an unbound text box on the
subform, but by storing the EndDate value (and modifying it through code
when viewing the record) I should be able to reduce maintenance in a
situation where, as I mentioned, great precision is not needed. Former
employees are not of much interest to auditors and such. If detail is
needed it will be found in HR, not in my employment summary database.

You don't say what you're doing to calculate the number of days, years,
etc. However, you might be able to use the Nz() function to solve the
problem.

Example:
Nz([EndDate], Date())

If the EndDate field is Null, then today's date will be substituted. Use
the statement above in the equation instead of just the EndDate field.

When checking the Inactive checkbox, you could fill in the EndDate, but
the problem with that is if you don't check the box on the same day the
employee leaves, you're going to have to manually fill in the date
anyway. Instead, I would recommend that filling in a date would show the
checkbox as checked. It wouldn't be necessary to store the checkbox's
value, it could just be a calculated control on the form. If there is a
value for EndDate in the last record (unless you keep records for where
an employee went to after they left your place) for the employee then
the checkbox would be True, otherwise it would be False.

To keep what you have and just prompt the user to enter a date try the
following in the AfterUpdate event of the checkbox:

If Me.chkMyCheckbox = True Then
Msgbox "Please enter an EndDate for the employee."
Me.txtEndDate.SetFocus
End If

--
Wayne Morgan
MS Access MVP


I have an employment summary form for keeping a record of employee
employment history. I have a main form based on the Employees table and
a subform based on a query, which is in turn based on a Details table
that stores Employer, StartDate, and EndDate. The Details query
contains a calculated field (using DateDiff) for the amount of time
elapsed between StartDate and EndDate. The Details subform contains a
text box in the footer for adding up the total years in the calculated
fields for all related records:

Employer 1 2/1/03 1/15/04
MyCompany 2/1/04 (Current Date)

If it matters, the DateDiff calculation determines the number of
months. The TotalYears text box in the footer divides the sum by 12 and
rounds up to a whole year. It works as intended. The modification I
would like to make is that if the employer is the current company
(MyCompany) I would like EndDate to be the current date unless the
employee no longer works here, in which case I would like to lock in
the date the employee left. There is an Inactive field (Yes/No) in the
Employees table. This last step (locking in the end date) doesn't need
to be automated, although it would be good if it was. It would be OK
to just have a message box pop up when Inactive is checked, directing
the user to take some action to lock in the end date.
I can get the subform record to show the current date as I would like
by using the Current event for the subform, but it updates only when I
click into the subform record that lists MyCompany as the employer. I
need to have the total updated automatically when the user looks at the
employee's employment summary record.