How do I refer to one of two values as a default?

[rookiedev]

Can anyone help to steer me in the right direction on this?
I have assigned a default PayType to each employee. Here is the PayType Table.

PayTypeID PayType RegRate OTRate
1 CLRK $10.83 $17.52
2 SOD $8.32 $18.26
3 HKA2 $7.75 $14.40
4 T&P $12.51 $21.54
5 SCAR $20.26 $36.17
6 GMW $16.48 $26.00
7 HVAC $10.89 $29.61

What I need to do is figure out how to tell my timesheet form to look at the default PayType for that employee and based on the PayType to select either the RegRate or OTRate.

Right now I am using a Select Statement to update the various fields and the field I want to update with this information is called Rate. I can't figure out how to reference the correct value based on the PaymentMethodID I select.
Here is a snipit of the Select Case Statement I am using on the AfterUpDate event of the PaymentMethodID field.
Private Sub PaymentMethodID_AfterUpdate()
Select Case Me.PaymentMethodID
Case Is = "25"
Me.Type = "REGULAR HOURS"
Me.Job_ = "729"
Me.LCode = "02"
Me.Note = "$27.29"
Case Is = "26"
Me.Type = "REGULAR HOURS"
Me.Job_ = "729"
Me.LCode = "02"
Case Is = "27"
Me.Type = "REGULAR HOURS"
Me.Job_ = "729"
Me.LCode = "02"
Can anyone tell me how to go about adding to this statement?
I guess what I am looking for is Me.Rate=(RegRate's value) or (OTRate's value) based on the default PayType for the Employee.
This is something that my company just requested of me and I can't seem to wrap my mind around how to do it.
If anyone can help I would appreciate it more than you know.
RookieDev

 

[PaulBricker]

Well you shouldn't need a Select Case statement to get that value if it's in the table. If your Form is built on a query then you should be able to add the PayType Table to the query with your other table(s) and join it on the ID field. Then add the RegRate and OTRate from the PayType Table to the query and then set the control source of your textboxes on the Form to those fields. Then whenever you bring up an ID, that info is right there for you to use.

Paul

 

[rookiedev]

Actually I did add that to the Query but I can't seem to figure out how to tell it when or how to use the RegRate and then the OTRate for the individual employee.
Does that make any sense?
RookieDev

 

[CTOROCK]

Well, you could look up the value and get it:

Dim StrType as String
Dim Db as Databas
Dim Rst as Recordset

Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Tbl_YourTable", dbOpenDynaset)

StrType = TxtType.Value
Rst.FindFirst "Pay Type" = """ & StrType & """"

Then from there you can access the #'s:

TxtPay.Value = Rst.Fields("OTRate&quot.VAlue

Or you can set some if statements to choose which rate to get.
Let me know if this is what you were looking for.

"The greatest risk, is not taking one."

 

[rookiedev]

CTOROCK,
Let me give you a little more information.Maybe that will help. The PayType actually describes if the employee is a plumber or painter or whatever. Here is the situation. When I put in hours worked the home office wants the PayType to display and then the correct rate of pay. My timesheet would actually display like this:
Date Job# RateCode Hours Ledger Note Rate PayType
4/7/03 728 55-100 8 01 $0.00 Clrk

What I am trying to do is figure out how to fill in that rate field with the RegRate of $10.83 if the RateCode is
55-100 and use the OTRate of $17.52 if the RateCode is 55-100-OT. Here is the problem I am having....It needs to act the same way when I move to a Plumber only display the correct rates for that craft.

Does that make any more sense?
RookieDev

 

[CTOROCK]

Try this:

Dim StrType as String
Dim Db as Databas
Dim Rst as Recordset

Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Tbl_YourTable", dbOpenDynaset)

StrType = TxtType.Value
Rst.FindFirst "Pay Type" = """ & StrType & """"

If TxtRateCode.Value = "55-100" then
TxtPay.Value = Rst.Fields("Reg Rate&quot.Value
end if

If TxtRateCode.Value = "55-100-OT" then
TxtPay.Value = Rst.Fields("OT Rate&quot.Value
End if

Then you can do some calculations there:

TxtPay.value = TxtHours * TxtPay

Is this what you are looking for? "The greatest risk, is not taking one."

 

[rookiedev]

Yes! This is looking more like what I need! How do I reference the Employee's default paytype though? I'm thinking that I will need to be able to change that Paytype if they work in another classification so at some point I am going to have to link in the PayTypes and their underlying rates.
Thanks,
I'll see if I can implement this and get back to you.
RookieDev

 

[rookiedev]

Where am I suppose to put this by the way?????
Just want to make sure I do it correctly.
RookieDev

 

[CTOROCK]

You can put this once the TxtType has been updated. so go to the AfterUpdate Event in the properties and place it there, so everytime you make a change in there, it will bring everything up again. "The greatest risk, is not taking one."

 

[rookiedev]

I am assuming that Txttype.value would be my field named Rate correct? And I should place the code on the same place as my current select case statement is?
Thanks
RookieDev

 

[CTOROCK]

The TxtType.value is the field where the type of position is. So after you update that field say, "Plumer", then depending on what is in the rate code box, it will retreive the correct pay amount in the other box, as well as do some caluculations. Is this not what you had in mind? "The greatest risk, is not taking one."

 

[rookiedev]

Sorry for the delay..I could not get on the web site all day an started at 5am. It just wouldn't display for some reason.
At any rate...What I was hoping to do was this...When I am entering time if the Rate code is 55-100 then the value for that person's default PayType at RegRate would display in the Rate field but if the Rate code was 55-100-ot it would display that person's overtime rate. I have entered the PayType for each employee already but I have not entered the RegRate and OTRate as a part of the Employees table. Do you think I will have to do that instead of leaving the values in a different table? It is possible on any given day that an employee will be paid at both a plumber's rate and another rate.
I hope this helps you understand.
Thanks!
RookieDev

 

[CTOROCK]

Well you could have a table with only wage information for reference without anything else so you'll only have to make any changes there. You could create a table with say, a small example:

PayType RegRate OTRate
Plummer $17.00 $25.50
Clerk $12.00 $18.00
Janitor $6.00 $9.00

Then you can leave that table alone unless you want to add types or change rates.

On your form, regardless if it is bound or unbound, so with:

Date Job# RateCode Hours Ledger Note Rate PayType
4/7/03 728 55-100 8 01 $0.00 Clrk

Once you enter info into the PayType, it will then take the value you entered in the text box, and Find the record with the match. It will then either retrieve the reg rate or OT rate depending on what you entered or what is in the RateCode textbox back on the form.

I hope this is clear, or even what you are seeking.??

"The greatest risk, is not taking one."

 

[rookiedev]

If I store the values in a table and then those values change the next year will the original rates stay as they were or will the fields become blank after I put the new rates in?
RookieDev

 

[CTOROCK]

You will have two tables 1) for the current rates 2)to archinve data. If the current rate changes, just change it in table 1. everything written in table 2 from then on will be the current rate, and the old records should be archived in table 2. Table 2 is where the main data is, but table 1 is how table 2 knows what the current rate is.

"The greatest risk, is not taking one."

 

[rookiedev]

Is there a way to get the values not to change once they are entered into the second table and the underlying values are changed?
RookieDev

 

[CTOROCK]

I'm not sure if I understand your question. Say Table1 is where the current rates are, and table2 is where all your data is being archived. If today the rate of a plummer is $14.00, the data in table2 will reflect $14.00 for today. If tomorrow the rate is $16.00, then then yesterdays record in table2 will still reflect $14.00 for that time, but any NEW record added will then reflect $16.00, until changed again. table1 only gives the current rate of pay, but archived data will reflect the rate of pay during the time entered. Did this answer you question? Do you want table2 to change also?


"The greatest risk, is not taking one."

 

[rookiedev]

No actually I did not want the information to change. I have a database that is linked to a table in another database and if anything is changed in the original table it either shows a blank record or is missing information and I was wondering if there was a way to keep it from disappearing.The main field is Product Name and I want it to remain after the record is entered dispite what happeneds in the Products table. Does that make sense?
RookieDev

 

[CTOROCK]

nothing will change. have you tried it? The action will not edit any records, but rather specify the data for a "new" entry.

"The greatest risk, is not taking one."