Data Entry Form with Previous Year's Information

[Guest]

Hi! I am working on a data entry form to enter employees and their
annual salaries for the year. The Record Source is a table called Annual
Salary with the fields Employee ID, Salary Year and Annual Salary. I also
have a table called Employees with the fields Employee ID, Last Name, First
Name and Middle Initial. Right now I have a form On Open event procedure that
asks for the year I am entering and sets it as the default for my Salary Year
textbox. I also have a combo box in my form that drops down to list the
Employee ID in one column and "Last Name, First Name Middle Initial" in the
other. However, I would like for the form to somehow automatically generate a
continuous data entry form that lists each Employee ID and "Last Name, First
Name Middle Initial", and their Annual Salary for the year before the year
entered for the On Open event procedure (2005 if I enter 2006). This is what
I am thinking:

Salary Year: (Automatic, 2006)
Employee ID/Name: Employee A (2005)
Annual Salary: (Shows default, but can be changed, 2005)

Salary Year: (Automatic, 2006)
Employee ID/Name: Employee A (2005)
Annual Salary: (Shows default, but can be changed, 2005)

All help will be greatly appreciated!

 

[Dirk Goldgar]

Hi! I am working on a data entry form to enter employees and
their annual salaries for the year. The Record Source is a table
called Annual Salary with the fields Employee ID, Salary Year and
Annual Salary. I also have a table called Employees with the fields
Employee ID, Last Name, First Name and Middle Initial. Right now I
have a form On Open event procedure that asks for the year I am
entering and sets it as the default for my Salary Year textbox. I
also have a combo box in my form that drops down to list the Employee
ID in one column and "Last Name, First Name Middle Initial" in the
other. However, I would like for the form to somehow automatically
generate a continuous data entry form that lists each Employee ID and
"Last Name, First Name Middle Initial", and their Annual Salary for
the year before the year entered for the On Open event procedure
(2005 if I enter 2006). This is what I am thinking:

Salary Year: (Automatic, 2006)
Employee ID/Name: Employee A (2005)
Annual Salary: (Shows default, but can be changed, 2005)

Salary Year: (Automatic, 2006)
Employee ID/Name: Employee A (2005)
Annual Salary: (Shows default, but can be changed, 2005)

All help will be greatly appreciated!

I'm not at al sure I understand what you're after. What do you mean by
"I would like for the form to somehow automatically generate a
continuous data entry form"? Are we talking about one form here, or two
different forms? What do you mean by "Automatic" in your field
descriptions above? Are you trying just to show the employees' salaries
from the previous year, or are you trying to set up a form to enter
salaries for the current year, but defaulting those salaries to the
previous year's salaries? Or are you doing something else entirely?

First explain in plain language what you want to do, and then we can get
into the details of how to accomplish it.

 

[Guest]

Dirk,

Ok, let me try to put what I'm thinking into words. Say for 2005 I have
150 employees with 150 annual salaries in my annual salary table. Now when I
first make a form using wizard it would make me a form where I could view the
information in my annual salary table, but if I change any information, such
as the year, it changes that particular record instead of adding a new one. I
would like it if I could view the information from 2005, but re-enter it
under 2006 with the appropriate changes. So yes, I am trying to set up a form
to enter employees and their annual salaries for the current year, but
defaulting those employees and their annual salaries to the previous year's
employees and their annual salaries. I just want my user to have to click
through the information from 2005, make appropriate changes and then be able
to enter it under 2006, instead of having to drop down a combo box for each
150 employees and then enter their annual salary. That's what I meant by
automatic. Also don't even worry about the "continuous data entry form" part.
I hope that makes things clear and thank you for your continuing help!

 

[Dirk Goldgar]

Dirk,

Ok, let me try to put what I'm thinking into words. Say for 2005
I have 150 employees with 150 annual salaries in my annual salary
table. Now when I first make a form using wizard it would make me a
form where I could view the information in my annual salary table,
but if I change any information, such as the year, it changes that
particular record instead of adding a new one. I would like it if I
could view the information from 2005, but re-enter it under 2006 with
the appropriate changes. So yes, I am trying to set up a form to
enter employees and their annual salaries for the current year, but
defaulting those employees and their annual salaries to the previous
year's employees and their annual salaries. I just want my user to
have to click through the information from 2005, make appropriate
changes and then be able to enter it under 2006, instead of having to
drop down a combo box for each 150 employees and then enter their
annual salary. That's what I meant by automatic. Also don't even
worry about the "continuous data entry form" part. I hope that makes
things clear and thank you for your continuing help!

I think I understand it now. Your purpose, it seems, is to generate the
salaries for the new year. Hence, this wouldn't be something you would
normally do more than once a year. It would be possible, though a bit
complicated, to do it the way you're proposing, but let me first suggest
a different way.

The trouble with your idea, from my point of view, is that it makes the
user at least visit every record, whether the record needs to be changed
or not. I think what I would do is have a continuous form for viewing
and editing employee salary information for a specified year. There
would be a way to click a single button to copy all the salary
information from the previous year, after which the user would just go
and change those records that needed changing.

To do this, I'd have a text box in the form's Form Header section, for
specifying the year to be viewed, and also have a command button in the
Form Header that automatically copies all the salary information from
the previous year. The form's recordsource would use the text box as a
criterion on the [Salary Year] field, so that it only shows the records
for the year specified in that text box. The recordsource query might
look like this:

SELECT
[Annual Salary].[Employee ID],
[Annual Salary].[Salary Year],
Employees.[Last Name],
Employees.[First Name],
Employees.[Middle Initial],
[Annual Salary].[Annual Salary]
FROM
[Annual Salary]
INNER JOIN
[Employees]
ON
[Annual Salary].[Employee ID] = Employees.[Employee ID]
WHERE
[Annual Salary].[Salary Year] =
[Forms]![frmAnnualSalaries]![txtShowYear];

That's assuming the name of the form is "frmAnnualSalaries" and the text
box in the header is named "txtShowYear".

In the detail section of the form, you would probably hide the [Employee
ID] and [Salary Year] fields, so they'd be on the form but not visible
to the user. You can give them Width = 0 and Height = 0, for
convenience, as well as setting their Visible property to No.

Now, you'd need a teeny bit of code in the AfterUpdate event of
txtShowYear to requery the form:

'----- start of code -----
Private Sub txtShowYear_AfterUpdate()

Me.Requery

End Sub
'----- end of code -----

And finally, you'd need code for the command button to copy the
information from the previous year to the year specified by txtShowYear.
It would look something like this:

'----- start of code -----
Private Sub cmdCopyYear_Click()

Dim strSQL As String
Dim intPrevYear As Integer

If IsNull(Me.txtShowYear) Then
MsgBox "First enter the salary year you're updating."
Me.txtShowYear.SetFocus
Exit Sub
End If

intPrevYear = Me.txtShowYear - 1

If MsgBox( _
"Are you sure you want to copy salary data from " & _
intPrevYear & " to " & Me.txtShowYear & "?", _
vbYesNo, _
"Please Confirm") _
= vbNo _
Then
Exit Sub
End If

' Okay, at this point we really want to do it.

strSQL = _
"INSERT INTO [Annual Salary] " & _
"([Employee ID], [Salary Year], [Annual Salary]) " & _
"SELECT "
"([Employee ID], " & _
Me.txtShowYear & _
", [Annual Salary]) " & _
"FROM [Annual Salary] " &
"WHERE [Salary Year] = " & intPrevYear

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'----- end of code -----

Note: the above code doesn't make any attempt to determine whether
records for the current Salary Year already exist, nor will it overwrite
them if they do. If you want to do those things, a little extra code is
needed.

So, that's my proposed alternative. If you don't like it, we can go
back and pursue your original idea.

 

[Guest]

Dirk,

I am not quite sure what "the form's recordsource would use the text
box as a
criterion on the [Salary Year] field" means. I save the form as
frmAnnualSalaries and the text box as txt ShowYear. Then do I just press the
"..." next to record source and copy and paste the code. Is this right?

Dirk,

Ok, let me try to put what I'm thinking into words. Say for 2005
I have 150 employees with 150 annual salaries in my annual salary
table. Now when I first make a form using wizard it would make me a
form where I could view the information in my annual salary table,
but if I change any information, such as the year, it changes that
particular record instead of adding a new one. I would like it if I
could view the information from 2005, but re-enter it under 2006 with
the appropriate changes. So yes, I am trying to set up a form to
enter employees and their annual salaries for the current year, but
defaulting those employees and their annual salaries to the previous
year's employees and their annual salaries. I just want my user to
have to click through the information from 2005, make appropriate
changes and then be able to enter it under 2006, instead of having to
drop down a combo box for each 150 employees and then enter their
annual salary. That's what I meant by automatic. Also don't even
worry about the "continuous data entry form" part. I hope that makes
things clear and thank you for your continuing help!

I think I understand it now. Your purpose, it seems, is to generate the
salaries for the new year. Hence, this wouldn't be something you would
normally do more than once a year. It would be possible, though a bit
complicated, to do it the way you're proposing, but let me first suggest
a different way.

The trouble with your idea, from my point of view, is that it makes the
user at least visit every record, whether the record needs to be changed
or not. I think what I would do is have a continuous form for viewing
and editing employee salary information for a specified year. There
would be a way to click a single button to copy all the salary
information from the previous year, after which the user would just go
and change those records that needed changing.

To do this, I'd have a text box in the form's Form Header section, for
specifying the year to be viewed, and also have a command button in the
Form Header that automatically copies all the salary information from
the previous year. The form's recordsource would use the text box as a
criterion on the [Salary Year] field, so that it only shows the records
for the year specified in that text box. The recordsource query might
look like this:

SELECT
[Annual Salary].[Employee ID],
[Annual Salary].[Salary Year],
Employees.[Last Name],
Employees.[First Name],
Employees.[Middle Initial],
[Annual Salary].[Annual Salary]
FROM
[Annual Salary]
INNER JOIN
[Employees]
ON
[Annual Salary].[Employee ID] = Employees.[Employee ID]
WHERE
[Annual Salary].[Salary Year] =
[Forms]![frmAnnualSalaries]![txtShowYear];

That's assuming the name of the form is "frmAnnualSalaries" and the text
box in the header is named "txtShowYear".

In the detail section of the form, you would probably hide the [Employee
ID] and [Salary Year] fields, so they'd be on the form but not visible
to the user. You can give them Width = 0 and Height = 0, for
convenience, as well as setting their Visible property to No.

Now, you'd need a teeny bit of code in the AfterUpdate event of
txtShowYear to requery the form:

'----- start of code -----
Private Sub txtShowYear_AfterUpdate()

Me.Requery

End Sub
'----- end of code -----

And finally, you'd need code for the command button to copy the
information from the previous year to the year specified by txtShowYear.
It would look something like this:

'----- start of code -----
Private Sub cmdCopyYear_Click()

Dim strSQL As String
Dim intPrevYear As Integer

If IsNull(Me.txtShowYear) Then
MsgBox "First enter the salary year you're updating."
Me.txtShowYear.SetFocus
Exit Sub
End If

intPrevYear = Me.txtShowYear - 1

If MsgBox( _
"Are you sure you want to copy salary data from " & _
intPrevYear & " to " & Me.txtShowYear & "?", _
vbYesNo, _
"Please Confirm") _
= vbNo _
Then
Exit Sub
End If

' Okay, at this point we really want to do it.

strSQL = _
"INSERT INTO [Annual Salary] " & _
"([Employee ID], [Salary Year], [Annual Salary]) " & _
"SELECT "
"([Employee ID], " & _
Me.txtShowYear & _
", [Annual Salary]) " & _
"FROM [Annual Salary] " &
"WHERE [Salary Year] = " & intPrevYear

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'----- end of code -----

Note: the above code doesn't make any attempt to determine whether
records for the current Salary Year already exist, nor will it overwrite
them if they do. If you want to do those things, a little extra code is
needed.

So, that's my proposed alternative. If you don't like it, we can go
back and pursue your original idea.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

I am not quite sure what "the form's recordsource would use the
text box as a
criterion on the [Salary Year] field" means. I save the form as
frmAnnualSalaries and the text box as txt ShowYear. Then do I just
press the "..." next to record source and copy and paste the code. Is
this right?

That's basically right, but:

1. Don't put a space in the name of the text box. It should not be "txt
ShowYear", but "txtShowYear". Putting spaces in your object names, as
you appear to have done rather a lot, makes coding cumbersome because
you have to wrap all references to such names in square brackets {[]).
If I were you, I'd plan to make a project of going back and changing
them all -- but that project could be fairly complicated, so we'll just
soldier on, for now.

2. I don't think the SQL statement I posted for the recordsource has the
right table names, and may not have the right field names. A later post
of yours suggests that your table names are "Annual Salaries Table" and
"Employees Table". You should check out the table and field names I
used, and correct them. Copy and paste the corrected SQL into the SQL
view of the form's RecordSource query.

 

[Guest]

Dirk,

I made the form you proposed, but I am sorry to say it is not what I
need. Once a year my user will need to look at every employee and either
check to see if their previous annual salary is the same or, in most cases,
change their annual salary. Then my user will want to all of these employees
and their salaries to be put into the table. But, they do not want the
current employees and salaries to replace the previous year's employees and
salaries. Thus, the Annual Salary table should continue to list 150 employees
and their salaries from 2005 and add 150 employees and their salaries for
2006. It is also important both the salaries AND the employees from the
previous year can be seen on the form. I am really sorry for the
misunderstanding, but I would really appreciate your continuing help!

 

[Dirk Goldgar]

Dirk,

I made the form you proposed, but I am sorry to say it is not
what I need. Once a year my user will need to look at every employee
and either check to see if their previous annual salary is the same
or, in most cases, change their annual salary. Then my user will want
to all of these employees and their salaries to be put into the
table. But, they do not want the current employees and salaries to
replace the previous year's employees and salaries. Thus, the Annual
Salary table should continue to list 150 employees and their salaries
from 2005 and add 150 employees and their salaries for 2006. It is
also important both the salaries AND the employees from the previous
year can be seen on the form. I am really sorry for the
misunderstanding, but I would really appreciate your continuing help!

Just to make sure you understand, the approach I suggested doesn't
delete or overwrite the records from the previous year. The salary
table still holds those records. The form I proposed just lets you
choose which year you want to see at the moment, and lets you start a
new year by copying -- not replacing -- the records from a previous
year.

If you understand that, but it's still not what you want, we can discard
that approach and create a form that shows each employee and salary from
last year, and allows the user to either accept that salary for the new
year, or enter a new salary. It just seems to me that this creates a
greater burden on the user than generating all the initial salaries for
the new year with one button click, and then changing only those that
need to be changed -- given the understanding that the salary records
for previous years aren't deleted or replaced.

 

[Guest]

Dirk,

Hi! I understand your reasoning, but I would like to create the form
that shows each employee and salary from the previous year, then has the user
to either accept that salary for the employee and new year or enter a
different salary for the employee and new year for two reasons. First, the
salary for every employee needs to be checked. And second, a majority of
employee's salaries will be changed, not just one or two, which is what I
think you were thinking. I'm sorry for so much inconvience. Please stick with
me!

 

[Dirk Goldgar]

Dirk,

Hi! I understand your reasoning, but I would like to create the
form that shows each employee and salary from the previous year, then
has the user to either accept that salary for the employee and new
year or enter a different salary for the employee and new year for
two reasons. First, the salary for every employee needs to be
checked. And second, a majority of employee's salaries will be
changed, not just one or two, which is what I think you were
thinking. I'm sorry for so much inconvience. Please stick with me!

Don't worry, I'm still on the case. <g>

I've now created a form that does what I think you want. It's more
complicated than what I proposed, but I hope it will meet your needs.
It was a little tricky, so follow closely.

The first step is to create three queries that involve your tables
"Employees Table" and "Annual Salaries Table". (Did I mention that
these spaces in the table and field names are a pain in the neck?)
These queries are as follows, though you may have to correct field
names:

------------QUERIES ------------

qryAnnualSalariesThisYear
---------------------------
SELECT [Employee ID], [Salary Year], [Annual Salary]
FROM [Annual Salaries Table]
WHERE
[Salary Year]=[Forms]![frmAnnualSalaries]![txtShowYear];

qryAnnualSalariesLastYear
----------------------------
SELECT [Employee ID], [Annual Salary] AS SalaryLastYear
FROM [Annual Salaries Table]
WHERE [Salary Year]=[Forms]![frmAnnualSalaries]![txtShowYear]-1;

qryAnnualSalaries
------------------
SELECT
[Employees Table].[Employee ID] AS EmpTableEmpID,
qryAnnualSalariesThisYear.[Employee ID],
[Employees Table].[First Name],
[Employees Table].[Middle Initial],
[Employees Table].[Last Name],
qryAnnualSalariesThisYear.[Salary Year],
qryAnnualSalariesLastYear.SalaryLastYear,
qryAnnualSalariesThisYear.[Annual Salary]
FROM
(
[Employees Table]
LEFT JOIN
qryAnnualSalariesThisYear
ON [Employees Table].[Employee ID] =
qryAnnualSalariesThisYear.[Employee ID]
)
LEFT JOIN
qryAnnualSalariesLastYear
ON [Employees Table].[Employee ID] =
qryAnnualSalariesLastYear.[Employee ID];

------------End QUERIES ------------

Having created the above queries, tell the Form Wizard you want to
create a new tabular form (i.e., a continuous form) based on
qryAnnualSalaries. Include all the fields. The wizard will prompt you
for a value for [Forms]![frmAnnualSalaries].[txtShowYear] -- just click
OK on the prompt. Edit the wizard-created form in design view, and save
it as "frmAnnualSalaries".

Modify the design of this form, frmAnnualSalaries, as follows:

1. Change the Recordset Type property (on the Data tab of the form's
property sheet) to "Dynaset (Inconsistent Updates)".

2. Place an unbound text box in the Form Header section, name it
"txtShowYear", and set its Format property to "Fixed" and its Decimal
Places property to 0.

3. For the text boxes named "Employee ID" and "SalaryYear", set these
properties:

Visible = No
Left = 0
Width = 0

and delete the corresponding labels in the form header. You may want to
do the same for the text box named "EmpTableEmpID", depending on whether
you want the employee ID to be displayed or not.

Move the other controls around to fill up the blank space and make
things look good. Leave some space between the text boxes
"SalaryLastYear" and "Annual Salary". In this space, place an
appropriately sized command button, and named if "cmdCopySalary". In my
form, I set the button's Picture property to the "Right Arrow (blue)"
picture from the stock set of pictures, but you could choose some other
picture or give it a text caption if you want. The picture or caption
should convey the idea that clicking the button will copy SalaryLastYear
to [Annual Salary].

Set the Locked property to Yes for the controls [First Name], [Middle
Initial], [Last Name], and [SalaryLastYear]. If you've left
[EmpTableEmpID] visible, lock that, too.

Put the following code in the form's code module:

'----- start of code for form module -----
Option Compare Database
Option Explicit

Private Sub cmdCopySalary_Click()

If Not IsNull(Me![Annual Salary]) Then
If MsgBox( _
"You've already entered a salary for " & _
Me.txtShowYear & _
". Do you want to overwrite it?", _
vbQuestion + vbYesNo, _
"Overwrite Salary?") _
= vbNo _
Then
Exit Sub
End If
End If

Me![Annual Salary] = Me!SalaryLastYear

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me![Employee ID]) Then
Me![Employee ID] = Me!EmpTableEmpID
End If

If IsNull(Me![Salary Year]) Then
Me![Salary Year] = Me!txtShowYear
End If

End Sub


Private Sub Form_Open(Cancel As Integer)

' Default to current year. Change this if you like.
Me.txtShowYear = Year(Date)

Me.Requery

End Sub


Private Sub txtShowYear_AfterUpdate()

Me.Requery

End Sub
'----- end of code for form module -----

I think that covers everything, though I may have forgotten some detail
that went into the working form I have. You'll have to check the field
and table names, of course, and make appropriate changes throughout.
Note that I have the form set up to open showing salaries for the
current year, as determined by the system date. The code for the form's
Open event takes care of this. You can modify that if you want some
other behavior; for example, if you want to prompt the user for the
year to show.

Note also that the form, with the queries as written, will show a record
for every employee in [Employees Table]. If you want to apply some
further criterion, such as excluding employees with termination dates
before the year being shown, or even just employees with no salary
recorded for the previous year (but then what about new employees?),
then you could accomplish that by modifying qryAnnualSalaries.

 

[Guest]

Dirk,

Thank you for all of your help. There is one thing I would like to
change. I would like my user to be prompted to enter the year they will be
entering. I would also like the year to be entered to be in short date
format.
As for applying further criterion, you pretty much read my mind. First,
my employee table has the fields employee ID, last name, first name, middle
initial and termination date. How can I make a form where all the user has to
do type in the employee ID and termination date and it will enter it with the
employees record? When I tried to do this earlier, it would enter the
employee ID and termination date as a new record instead of just filling in
the termination date field for the employee.
Second, I will need the form to exclude employees with termination
dates before the year being entered.
Third, it would be nice if the user could add new employees. Right now
I have it so they click on a button which takes them to a new employee form
and new employee annual salary form, but I would like to hear if you have a
better idea.
It is very important that all of the dates are in short date format.
This is because my database uses the annual salaries to calculate the monthly
premiums for the first month of the year. These premiums will need to be
updated monthly the same way the annual salaries are updated yearly. I will
most likely need a little help on that, but let's try to work our way through
this form first.
Again, thank you so much for your help, I appreciate it more than I can
express.

 

[Dirk Goldgar]

Dirk,

Thank you for all of your help. There is one thing I would like
to change. I would like my user to be prompted to enter the year they
will be entering. I would also like the year to be entered to be in
short date format.

This doesn't make any sense, Tandy. A year isn't a date, and a date
isn't a year. I can't grasp the idea behind wanting the year to be
entered in "short date" format. Either the value being stored in
[Salary Year] is an integer value representing a particular year, or
it's a date value representing a specific day of a specific month of a
specific year -- and that wouldn't fit in with the meaning of the field
as I have come to understand it. You'll have to explain.

To prompt the user for the year to be worked on when you open the form,
change the Open event procedure I gave you to look like this:

'----- start of revised Open event proc -----
Private Sub Form_Open(Cancel As Integer)

Dim strShowYear As String
Dim blnDone As Boolean

' Prompt the user for the year to be shown initially.
' We'll suggest next year as a default.
' In case of erroneous entry, continue prompting until
' a valid year is given, or the prompt is cancelled.

Do
strShowYear = InputBox( _
"What year do you want to edit?", _
"Enter Year", _
CStr(Year(Date) + 1))

Select Case True
Case (Len(strShowYear) = 0)
Cancel = True
blnDone = True

Case (Not IsNumeric(strShowYear)), _
(Val(strShowYear) < 1800), _
(Val(strShowYear) > 5000)
MsgBox "That's not a valid year!", _
vbExclamation, "Invalid Entry"

Case (Val(strShowYear) < (Year(Date) - 10)), _
(Val(strShowYear) > (Year(Date) + 10))
If MsgBox( _
"You entered " & strShowYear & _
", which seems odd. " & _
"Are you sure?", _
vbQuestion + vbYesNo, _
"Please Confirm") _
= vbYes _
Then
blnDone = True
End If

Case Else
blnDone = True

End Select

Loop Until blnDone

If Cancel <> True Then
Me.txtShowYear = strShowYear
Me.Requery
End If

End Sub
'----- end of revised Open event proc -----

As for applying further criterion, you pretty much read my mind.
First, my employee table has the fields employee ID, last name, first
name, middle initial and termination date. How can I make a form
where all the user has to do type in the employee ID and termination
date and it will enter it with the employees record? When I tried to
do this earlier, it would enter the employee ID and termination date
as a new record instead of just filling in the termination date field
for the employee.

The simplest, most natural way would be to bind that form to the
Employees Table, and show the termination date along with the other
fields on the form. Then the user would locate the employee's record,
type in the termination date in the text box provided, and it would be
done. If you want, you can put unbound combo boxes on the form to help
the user lookup (by ID or by name) and move to a particular employee's
record. The Combo Box Wizard will help you build such a combo box --
just tell it you want to "find a record on my form". Whatever you did
before, it must have been something different from this.

If you want, you can add the [Termination Date] field to the fields
selected by qryAnnualSalaries, and add a text box on frmAnnualSalaries
to show and edit it.

Second, I will need the form to exclude
employees with termination
dates before the year being entered.

Modify the query "qryAnnualSalaries" to have SQL like this:

--------- qryAnnualSalaries, revised ----------

SELECT
[Employees Table].[Employee ID] AS EmpTableEmpID,
qryAnnualSalariesThisYear.[Employee ID],
[Employees Table].[First Name],
[Employees Table].[Middle Initial],
[Employees Table].[Last Name],
qryAnnualSalariesThisYear.[Salary Year],
qryAnnualSalariesLastYear.SalaryLastYear,
qryAnnualSalariesThisYear.[Annual Salary]
FROM
(
[Employees Table]
LEFT JOIN
qryAnnualSalariesThisYear
ON [Employees Table].[Employee ID] =
qryAnnualSalariesThisYear.[Employee ID]
)
LEFT JOIN
qryAnnualSalariesLastYear
ON [Employees Table].[Employee ID] =
qryAnnualSalariesLastYear.[Employee ID]
WHERE
([Termination Date] Is Null)
OR
(Year([Termination Date]) >=
[Forms]![frmAnnualSalaries]![txtShowYear]);

--------- end qryAnnualSalaries ----------

Third, it would be nice if the user could add new employees.
Right now I have it so they click on a button which takes them to a
new employee form and new employee annual salary form, but I would
like to hear if you have a better idea.

If you unlock the [First Name], [Middle Initial], and [Last Name] text
boxes on the form, you'll be able to enter values for them in a new
record on this form. If the Employee ID field in the Employees Table is
not an autonumber field, so that it must be entered for each new
employee, then you'll also have to unlock the EmpTableEmpID field on
this form, make it visible, and set its position and size so that the
user can enter values in it. Voilá, you can add employees via
frmAnnualSalaries. Doing so will also create a record in the Annual
Salaries Table for that employee.

It is very important that all of the dates are in short date
format. This is because my database uses the annual salaries to
calculate the monthly premiums for the first month of the year. These
premiums will need to be updated monthly the same way the annual
salaries are updated yearly. I will most likely need a little help on
that, but let's try to work our way through this form first.

As I said above, I don't understand what you're getting at when you talk
about dates being in short date format, because you have only been
talking about years, not dates, and they are very different things.
Also, even if we *were* talking about date fields, the format of a date
field has no bearing on its value. A field's format only influences the
way the field is displayed, not what it contains internally, and all
calculations using dates are performed on their values, not their
formats. Clearly, we have to get our terminology straight, as well as
come to an understanding of what you mean by this last request.

Again, thank you so much for your help, I appreciate it more
than I can express.

You're welcome. I should warn you, this is starting to get beyond the
kind of support you can reasonably get from a newsgroup. I hope we can
resolve your last few questions quickly, as I can't afford to spend too
much more time on this. I hope you understand.

 

[Guest]

Dirk,

I set up the form this morning and it is exactly what I need. Makes me
wonder what kind of form I would have come up with on my own... Don't worry
about the short date format thing, I believe I have already figured out what
I want to do with that. Anyway, I think I have got it from here. I apologize
for any inconvience I may have caused you. I really appreciate all of your
help on this form!

Tandy

Dirk,

Thank you for all of your help. There is one thing I would like
to change. I would like my user to be prompted to enter the year they
will be entering. I would also like the year to be entered to be in
short date format.

This doesn't make any sense, Tandy. A year isn't a date, and a date
isn't a year. I can't grasp the idea behind wanting the year to be
entered in "short date" format. Either the value being stored in
[Salary Year] is an integer value representing a particular year, or
it's a date value representing a specific day of a specific month of a
specific year -- and that wouldn't fit in with the meaning of the field
as I have come to understand it. You'll have to explain.

To prompt the user for the year to be worked on when you open the form,
change the Open event procedure I gave you to look like this:

'----- start of revised Open event proc -----
Private Sub Form_Open(Cancel As Integer)

Dim strShowYear As String
Dim blnDone As Boolean

' Prompt the user for the year to be shown initially.
' We'll suggest next year as a default.
' In case of erroneous entry, continue prompting until
' a valid year is given, or the prompt is cancelled.

Do
strShowYear = InputBox( _
"What year do you want to edit?", _
"Enter Year", _
CStr(Year(Date) + 1))

Select Case True
Case (Len(strShowYear) = 0)
Cancel = True
blnDone = True

Case (Not IsNumeric(strShowYear)), _
(Val(strShowYear) < 1800), _
(Val(strShowYear) > 5000)
MsgBox "That's not a valid year!", _
vbExclamation, "Invalid Entry"

Case (Val(strShowYear) < (Year(Date) - 10)), _
(Val(strShowYear) > (Year(Date) + 10))
If MsgBox( _
"You entered " & strShowYear & _
", which seems odd. " & _
"Are you sure?", _
vbQuestion + vbYesNo, _
"Please Confirm") _
= vbYes _
Then
blnDone = True
End If

Case Else
blnDone = True

End Select

Loop Until blnDone

If Cancel <> True Then
Me.txtShowYear = strShowYear
Me.Requery
End If

End Sub
'----- end of revised Open event proc -----

As for applying further criterion, you pretty much read my mind.
First, my employee table has the fields employee ID, last name, first
name, middle initial and termination date. How can I make a form
where all the user has to do type in the employee ID and termination
date and it will enter it with the employees record? When I tried to
do this earlier, it would enter the employee ID and termination date
as a new record instead of just filling in the termination date field
for the employee.

The simplest, most natural way would be to bind that form to the
Employees Table, and show the termination date along with the other
fields on the form. Then the user would locate the employee's record,
type in the termination date in the text box provided, and it would be
done. If you want, you can put unbound combo boxes on the form to help
the user lookup (by ID or by name) and move to a particular employee's
record. The Combo Box Wizard will help you build such a combo box --
just tell it you want to "find a record on my form". Whatever you did
before, it must have been something different from this.

If you want, you can add the [Termination Date] field to the fields
selected by qryAnnualSalaries, and add a text box on frmAnnualSalaries
to show and edit it.

Second, I will need the form to exclude
employees with termination
dates before the year being entered.

Modify the query "qryAnnualSalaries" to have SQL like this:

--------- qryAnnualSalaries, revised ----------

SELECT
[Employees Table].[Employee ID] AS EmpTableEmpID,
qryAnnualSalariesThisYear.[Employee ID],
[Employees Table].[First Name],
[Employees Table].[Middle Initial],
[Employees Table].[Last Name],
qryAnnualSalariesThisYear.[Salary Year],
qryAnnualSalariesLastYear.SalaryLastYear,
qryAnnualSalariesThisYear.[Annual Salary]
FROM
(
[Employees Table]
LEFT JOIN
qryAnnualSalariesThisYear
ON [Employees Table].[Employee ID] =
qryAnnualSalariesThisYear.[Employee ID]
)
LEFT JOIN
qryAnnualSalariesLastYear
ON [Employees Table].[Employee ID] =
qryAnnualSalariesLastYear.[Employee ID]
WHERE
([Termination Date] Is Null)
OR
(Year([Termination Date]) >=
[Forms]![frmAnnualSalaries]![txtShowYear]);

--------- end qryAnnualSalaries ----------

Third, it would be nice if the user could add new employees.
Right now I have it so they click on a button which takes them to a
new employee form and new employee annual salary form, but I would
like to hear if you have a better idea.

If you unlock the [First Name], [Middle Initial], and [Last Name] text
boxes on the form, you'll be able to enter values for them in a new
record on this form. If the Employee ID field in the Employees Table is
not an autonumber field, so that it must be entered for each new
employee, then you'll also have to unlock the EmpTableEmpID field on
this form, make it visible, and set its position and size so that the
user can enter values in it. Voilá, you can add employees via
frmAnnualSalaries. Doing so will also create a record in the Annual
Salaries Table for that employee.

It is very important that all of the dates are in short date
format. This is because my database uses the annual salaries to
calculate the monthly premiums for the first month of the year. These
premiums will need to be updated monthly the same way the annual
salaries are updated yearly. I will most likely need a little help on
that, but let's try to work our way through this form first.

As I said above, I don't understand what you're getting at when you talk
about dates being in short date format, because you have only been
talking about years, not dates, and they are very different things.
Also, even if we *were* talking about date fields, the format of a date
field has no bearing on its value. A field's format only influences the
way the field is displayed, not what it contains internally, and all
calculations using dates are performed on their values, not their
formats. Clearly, we have to get our terminology straight, as well as
come to an understanding of what you mean by this last request.

Again, thank you so much for your help, I appreciate it more
than I can express.

You're welcome. I should warn you, this is starting to get beyond the
kind of support you can reasonably get from a newsgroup. I hope we can
resolve your last few questions quickly, as I can't afford to spend too
much more time on this. I hope you understand.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

I set up the form this morning and it is exactly what I need.

Excellent! I'm very glad to hear it.

Makes me wonder what kind of form I would have come up with on my
own... Don't worry about the short date format thing, I believe I
have already figured out what I want to do with that. Anyway, I think
I have got it from here. I apologize for any inconvience I may have
caused you. I really appreciate all of your help on this form!

You're welcome. I'm happy to have been of help.

 

[Guest]

Dirk,

I don't know if you will get this message, but if you do I would really
appreciate your help on a form that is almost identical to the one you helped
me make a few weeks ago. I promise to not take up nearly as much time!

 

[Dirk Goldgar]

Dirk,

I don't know if you will get this message, but if you do I would
really appreciate your help on a form that is almost identical to the
one you helped me make a few weeks ago. I promise to not take up
nearly as much time!

Hi, Tandy. You should post your question in a new thread, rather than
tacking it on to this one. I'll keep an eye out for it, or you can post
a last reply here stating the subject line and date/time of the post. I
don't have a whole lot of time at the moment, so you may be better
served if someone else helps you with it -- which is another good reason
to start a new thread for the new question. Be sure to give all the
necessary information. Don't assume that I'll be looking at it, with
knowledge of what went on in this thread. I will have a look, as I
said, but I can't promise anything.

 

[Guest]

Dirk,

I posted my question as a new thread with the subject line "Data Entry
Form with Previous Month's Information on Thursday, July 7 at about 1:30.
Your help would be greatly appreciated, but I understand if you do not have
very much time. Thanks!