Re: Data Entry Form with Previous Year's Information

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

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 Goldgar" wrote:

> "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
    ... entered in "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)