Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    South Dakota
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Desperate - Choosing correct payrate in a form (Access 2000)

    Greetings

    I am trying to create a job entry form where dailiy time slips are entered each week for a group of disabled adults.

    Job codes are used to enter the different types of work done as well as non-work items that are still recorded.

    Different types of jobs can be done on the same day, so this is done in a subform that will allow as many entries as needed for a single employee.

    Some jobs are "Piece Jobs" where everyone gets the same pay for the same work. So infromtion about those jobs is in a PieceJob Table which includes the code, description and pay rate for that work.

    The problem is that some jobs are Hourly Jobs. Pay varies by not only the kind of work, but also by the individual doing it. So a lookup there has to include both the JobCode AND the EmployeeID.

    Now my form has a code box and when a user enters the code, it will fill in a JobType box with the type of job (General, Piece or Hourly) [and as General has no pay rate, I am not worried about it]

    If it is a Piece Code, then I need to form to go to the piece table and get the payrate appropriate to that code.

    If it is an Hourly Code, then I need the form to go to the Hourly table and get the rate for the Code based on who the employee is. (See two things to look up here - Employee AND Code)

    I have been working on this for a week - have a deadline now and still can not get this part done!

    Any suggestions would be extremly appreciated.

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Desperate - Choosing correct payrate in a form (Access 2000)

    There are several ways to approach this, but the simplest is probably to use DLookup in the AfterUpdate event of the control that holds the code and simply lookup and return the appropriate value based on the values in the several controls.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Location
    South Dakota
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desperate - Choosing correct payrate in a form (Access 2000)

    Charlotte,

    Thanks for your responce.

    I've been trying this, but I think I am too much a newbie to figure out how to do the lookup with more than one parameter.

    I've used DLookup to fill in other parts of the form like the job description and I can get it to work when I limit my search to the Piece or General Codes where all I have to match is the JobCode to get the PayRate for that job.

    But when I get to the Hourly, where I have to go to a query to get the PayScale based on both the EmployeeID AND the JobCode, I can't make the thing work.

    Started with an IF statment
    If Me!JobType = "Hourly Code" THEN. . .

    But don't know how to structure the next part.

    The ELSE part is easy because, as I said above, it only needs to relate to the JobCode.

    Sorry to be so dense, but rather new at this. Have been reading the comp.databases.ms-access group, but they are mostly way ahead of my skill level.

    Thank again,

    Claudia

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Desperate - Choosing correct payrate in a form (Access 2000)

    Guessing at your control names, it might be something like this:

    <pre>Me!PayRate = DLookup("[Payscale]","PayscaleQuery","[EmployeeID]=" & Me!EmployeeID _
    & " AND [JobCode]='" & Me!Code & "'")</pre>


    I guessed that your Code was a text value. If it isn't, then take out the single quotes. Substitute whatever the appropriate query name is for PayscaleQuery and the correct field name to return.

    Don't feel bad about not understanding at first. It takes time to get familiar with all the capabilities of any application, and Access is more complicated than most. Just keep asking questions and trying to apply what you learn, and you'll be answering questions yourself in no time. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Location
    South Dakota
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desperate - Choosing correct payrate in a form (Access 2000)

    You guessed right about the Code being a text value (11 times it isn't, but 100 plus of the other times it is so it is stored as text..)

    I tried it as below and while I am not getting an error, noting is happening either. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>


    Private Sub fldJobType_BeforeUpdate(Cancel As Integer)

    If Me!fldJobType = "HourlyCode" Then

    Me!fldPayRate = DLookup("[fldPayRate]", "qryClientHourlyPay", "[fldEmployeeID]=" & Me!fldEmployeeID & " AND [fldCode]='" & Me!fldCode & "'")

    Else

    Me!fldPayRate = DLookup("[fldPayRate]", "tblAllCodes", "[fldCode]='" & Me!fldCode & "'")

    End If

    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Desperate - Choosing correct payrate in a form (Access 2000)

    Put a breakpoint into the code so you can see what's actually being returned. If the user can enter a code freeform, then the may have entered one that doesn't exist and you'll need to handle that in your code.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •