Setting Values based on date range for calculated fields

[Troxs]

I'm working on a Database for a buddy, and they need to perform a test to calibrate their equipment. There is a standard calibration number which changes every so often (once, or twice, a month). I need to figure out a way to create an admin side screen for him to input that number so that when his employees calibrate the equipment it'll show a calculated % error as a report when needed. I'm not really sure how to accomplish this. I do not want them to always have to enter the standard number when inputing the data, but I want to make sure that if he were to run a report 6 months from now he would be able to see the %error from that time frame based on the standard calibration number at that time...



I'll try to explain this in another way incase that is confusing...

Table
Date || Shift || Reading || Standard# || %Error

I need to have the standard number automatically entered into this table based on a number set by the admin, but not have to be typed in daily or every time a record is entered. The number must be set via a form.

I hope this makes sense.

[ranman256]

The employee form would have the admin field locked.
the admin form allows for change.
Make a table of admins, tAdmins, with all admin user IDs.

you can enable this lock by looking at the userId when the form opens....

sub form_load()
dim vUser
dim bIsAdmin as bBoolean
VUser = Environ("username")
BIsAdmin =not IsNull(dlookup("[userId]","tAdmins","[userId]='" & vUser & "'"))

TxtRate.locked =not IsAdmin
end sub

[Troxs]

Sorry, I have the admin screen set up. What I'm trying to figure out is how to get the number from a text box on the admin form (even if not open) and place it into the table [the table that the employees would be editing] automatically when a new record is made.

[ranman256]

Use DLOOKUP to pull that value into the field on new record event.

txtBox = Dlookup(field,table)

[Troxs]

If I used the DLOOKUP would it change the value on that record when the source changes? I'm looking to place the latest value from another table into each new record and it stay that value for that date.

[ranman256]

No ,the field on the form is an unbound text box. It can't affect the source table.

[ssanfu]

Date || Shift || Reading || Standard# || %Error

"Date" is a reserved word in Access and shouldn't be used for object names. Plus, it is not very descriptive... "Date" of what?
Object names should only be letters and numbers..... NO spaces, special characters or punctuation.

I need to have the standard number automatically entered into this table based on a number set by the admin, but not have to be typed in daily or every time a record is entered. The number must be set via a form.

Have a form to edit the standard number.

In the main readings form, use the before update event to add the standard number before the save.



This dB is to demonstrate how to update a value in a form from a table using the FROM before update event.

[Troxs]

Thank you! This worked, for my needs! Sorry, my email client decided to move the notification to spam and I'm just now seeing the reply.