I'm not even sure how to ask, let alone what I'm asking, my question.

I have a table/form with a field for the billing rate that invariably will change at some future date. This rate is set in the employee/auditor form and is used in timesheets which in turn relates to specific jobs.

If, at some future point, I change the billing rate in the employee/auditor form, how do I prevent the historical records from changing? i.e., once those jobs are done and billed out I don't want the billing information to change because we may now be charging at a different hourly rate.

Do I use some sort of record locking and if so, where, when, how?

Please keep it simple and in plain english if at all possible.

Thanks for the help.

Probably you should be storing the rate in the job billing. Otherwise you would need to use a variant of my sample here:
http://downloads.btabdevelopment.com/Samples/misc/Sample-MileageRate.zip

I'm not sure I understand what you mean by storing it in the job billing.

I just managed to populate the billing rate field in my subform by returning the rate which displays next to the employee name (combobox) on the main form as the default value. =[Forms]![Time Card]![EmpNameCombo].[column](2)

This employee name combobox, which displays as "EmployeeName/Rate" is pulled from the Employees table.

The hours when entered on the subform are stored in tblHours.

Is this what you are referring to by storing it in the job billing?

The hours when entered on the subform are stored in tblHours.

Is this what you are referring to by storing it in the job billing?
Yes, that's what I meant.

Cool, it seems to work.

Because of the way I had set a default of $85 in the subform I began to think about what would happen to the historical records if the billing rate were to change. Would the amounts billed out change and reflect the new billing rate or would they retain the original historical billing amount? Did some quick testing and it seems to be doing what I need it to do.:p

Again, thanks for the pointer.

the other thing is

- dont let users see the tables directly - give them a form, that locks the invoice date, invoice amount etc, so that users cannot change this info even by accident

Recovering from a stroke is a barrel fun! You don't learn to appreciate just how much work and processing of information your mind does until something like this happens.

I've done a bit of testing on the "solutions" above and have discovered that they really don't work at all. While I can change the hourly rate for any given auditor and have it work sort of ok that still doesn't solve my problem because the individual auditor isn't tied to any single trust fund.

I've been playing with Bob Larson's MileageRate sample db and it works to a certain degree.

I have created tblBillRates to store the rates;
BlgRateID - PK, auto number
BlgRate - currency
StartDate - date/time
EndDate - date/time

Billable hours are entered on TimeHrsSfrm which uses tblHours as the Record Source. In the control BillRate I have the following formula, which poaches from Bob Larson's db, as the Default Value: =Format(GetRate([DateWorked]),"0.000") This is the modified function from MileageRates sample

Public Function GetRate(dteDateWorked As Date) As Single
' The following code was copied from Sample-MileageRate written by Bob Larson

GetRate = DLookup("BlgRate", "tblBillRates", "[StartDate]<= #" & dteDateWorked & "# And Nz([EndDate],#12/31/9999#)> #" & dteDateWorked & "#")
End Function


The field DateWorked uses Date() as the default value

This sort of works. Open the Time Card and the billing rate pops in just like I wanted and even shows up on tblHours as I wanted.

The current problem is that as soon as I open Time Card the formula looks at DateWorked and returns the rate from tblBillRates just like it should however, the problem is that we are not always in the office connected to network where the back end is stored and could be out of town for a week at at time doing jobs. Once back in the office we enter our billable hours and expenses into Time Card. Problem is that if the date worked is before Date() the correct rate doesn't appear.

I tested this by using an old rate with an end date 4/9/10, set up a new date of 4/10/10.

I then opened Time Card, changed DateWorked to 4/10/10 but the BillRate still showed the old rate that ended on 4/9/10. I epxperimented with the table and dates trying to use older dates and stuff. No matter what I did the BillRate would return the rate for 4/9/10 which would be okay if not for the fact that we aren't always in the office to enter our billable time. Does any of this make sense?

What I'm hoping to accomplish at this time is to come up with some method where if the DateWorked is changed and is different than Date(), the formula above will calculate on the date entered and not the default value of Date(). Does that make sense?

As soon as I get this little problem solved I can then move on to the next one which is we deal with different Trust funds and right now the billing rate is the same for all of them but come July 1 at least one of them will be increasing. What I'm seeing is that I will then some method of looking at the FundID and then getting the correct billing rate for that Trust Fund on that particular DateWorked all the while preserving all the historical data. Yippee! I can hardly wait for that! :D

Please keep it very simple and in very plain English. My mind is working real hard to process stuff and make sense out of it all, wished I never had lit up a cigarette, watched my weight, ate properly and listened to my doctor. Had I done that I might not be sitting here all confused today and easily frustrated to boot.

Thanks in advance for helpful answers and heck, even those that aren't, they are appreciated nonetheless.