Need the ability to override a total taken from a table

[bradp1979]

Hi all,



On the subform here, I've got the WkHrs box, which is the difference between Actual Start Time and Actual End Time (courtesy of ssanfu, in this forum.) What I need to figure out now is how to override the total in the box, preferably with a button next to the box. When the button is clicked, the amount in the box automatically becomes "2."

Sounds simple enough, but my first attempts have not yielded any positive results.

Thanks!

[June7]

Cannot just set a value for a calculated textbox. Would have to modify the textbox ControlSource property.

Me.textbox.ControlSource = "=2"

However, if this is on a continuous or datasheet form, the change will be applied to ALL records.

Can save the alternate value to a field in the form's recordsource but this will not alter the displayed calculation.

[bradp1979]

I figured it might be tricky. If 2 hours were the minimum all the time, I could just set the minimum value as 2, but it's only in cases where the attendant is scheduled to work more than 2 hours, but for some reason didn't complete the shift, either because the shift was canceled, or they were sent home early due to lack of need, or whatever. In that case, they are paid a shift minimum of 2 hours, and the client is billed thusly.

If their shift is scheduled to be less than 2 hours, then they are paid for the time they worked.

I think the best way would instead be to just make that box modifiable. When we're processing reports, if we see that the 2-hour shift minimum applies, we need to be able to change the number in the box to 2 hours.

Can I just change the "locked" value to "False?"

[June7]

No. An expression in ControlSource makes the box not editable.

Could bind textbox to field and then maybe set the value with code when the record gets focus. But don't know db to advise specifics.

[bradp1979]

Thankfully, I've already got the DB ready to upload thanks to my other, as yet unsolved, post regarding these very calculated boxes.

New 3.05.zipCPV DB v.1.7 BE.zip

[bradp1979]

Actually, problem solved. I just realized that I was sent on a wild goose chase for this problem. If the employee is being paid the 2-hour shift minimum, we just put their "Actual End Time" 2 hours after the start time anyway.

However, I do have another question that you can likely answer. For the calculated boxes in question, I tried to enter an end time after midnight, and was given -18 as a response. How can I fix that so it will cross the date line?

[June7]

Need to enter full date/time values and calculate with those.

[bradp1979]

Is there any way to fix it on the programming end so the end users don't have to do anything different when entering in the data?

[June7]

Can possibly add the date and time fields in query and use that constructed date/time value in calcs.

[bradp1979]

I like that idea, but I'm still a bit too much of a newbie to know how to make that happen.

How might I go about doing that?

[June7]

Try just adding the two fields in query.

SELECT *, datefield + timefield AS DateTime FROM tablename;

Now use that query for reporting purposes.

This does assume both fields are date/time data type.

[bradp1979]

Out of curiosity, would it be possible to enter a code in VBA that essentially says that the number can't be negative? Or a way to have the time not take AM or PM into account, so that it always just counts forward? That way 7:00 PM to 12:30 AM would just be read as 7:00 to 12:00..

[June7]

I don't understand. If you want to calculate elapsed time then the data must be a valid date/time.

[bradp1979]

Can it just be entered and read as 07:00 instead of converting that to 7:00:00 AM? I tried to create a new input mask for just that, but it didn't work. Although the mask was 00:00, when I entered a time, it converted it to 00:00:00 AM (or PM)

[bradp1979]

How about this: =IIf([End Time]>[Start Time],[End Time]-[Start Time],([End Time]+1)-[StartTime])