Re: Data Entry Form with Previous Year's Information

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Tandy" <Tandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:09627A14-7D60-4AAD-BFC8-99FFA2961365@xxxxxxxxxxxxx
> 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)


.

Relevant Pages

  • Re: Andys complaint
    ... those prior years, and up to the date of termination, the regular limits ... contributions solely to the terminated employee, ... just because subsequent facts show that the borrower ...
    (misc.taxes)
  • Re: question for BillB and doggie
    ... morning and fire an employee because he or she does not like the ... cause of action for wrongful termination or wrongful discharge." ... If you fire someone for THAT, they claim unemployment benefits, ... you GIVE a reason to AVOID paying unemployment. ...
    (rec.gambling.poker)
  • Re: question for BillB and doggie
    ... able to fire someone without cause has nothing to do with qualifying ... morning and fire an employee because he or she does not like the ... reason, despite popular misconception, there is generally no claim ... cause of action for wrongful termination or wrongful discharge." ...
    (rec.gambling.poker)
  • Re: Data Entry Form with Previous Years Information
    ... >> short date format. ... > ' Prompt the user for the year to be shown initially. ... >> First, my employee table has the fields employee ID, last name, first ... middle initial and termination date. ...
    (microsoft.public.access.forms)
  • Re: Converting decimal time into hh:nn:ss time in ACCESS
    ... around this issue by changing the format from 'mmmm yyyy' (this is how ... decimal field and it gives me the entire month total for each employee. ... I create a report to bring in all of the ... reformat the decimal time to hh:nn:ss with no problem as long as it is ...
    (microsoft.public.access.formscoding)