formula VBA help

oxicottin

Learning by pecking away....
Local time
Yesterday, 22:24
Joined
Jun 26, 2007
Messages
889
Hello, I was woundering if this is possible and if so how would I go about doing it. I have a text box named txtEmployeeTime that we enter how many hours in decimals an employee worked for the day (Never exceeds 8.00). I also have a text box named txtDTRegular which we enter that employees machine delay time in decimals. Ok, now here is what I want to do, I want to have a text box where I enter the employees "run time" minutes (We get the number from a PC thats using a PLC) and it will subtract that from txtEmployeeTime and give you a total in txtDTRegular in decimals. I am saving the info in the control sources txtEmployeeTime and txtDTRegular to fields in the forms table.

Thanks!
 
Last edited:
My Access is broken so I can't open your file. Surely all you need to do is divide your minutes by 60 to convert to a decimal and then subtract that. Or am I misunderstanding?
 
Your misunderstanding :p I figured out that im going to have to use VBA but I cant figure out how to do it? I started with this:
Code:
Private Sub txtMinutes_AfterUpdate()
Me.txtDTRegular = (Round(Me.txtMinutes / 60, 2) - Me.txtAccTimeWorked)
Forms!frmMainDB!txtMinutes = Me.txtDTRegular
End Sub
Here is how its supposed to work but the VBA is messed up.
I enter a number into txtMinutes and that number is suposed to be subtracted from the number already in txtAccTimeWorked and then give the total in another text box named txtDTRegular.

Newest file uplaoded....
 
Last edited:
Is messed up a technical term I'm not familiar with?

this is incorrect

Me.txtDTRegular = (Round(Me.txtMinutes / 60, 2) - Me.txtAccTimeWorked)
it could be
Me.txtDTRegular = Round(Me.txtMinutes / 60, 2) - Me.txtAccTimeWorked
or
Me.txtDTRegular = Round(Me.txtMinutes / 60 - Me.txtAccTimeWorked, 2)

dependent upon where you want the rounding to apply.

Brian
 
Brian, Thanks for the help but none of them seem to work? What im looking for is to have hours that would populate into the txtDTRegular text box from the minutes I would enter into the txtMinutes. Example:
1) Enter 1.00 into DT Reason 1
2) This will drop the minutes in the txtAccTimeWorked (Gray txt Box) to 420 (Minutes)
3) Now enter minutes like 120 (2hrs) into the txtMinutes text box and tab.
4) After you tab it should populate the answer in txtDTRegular text box of 5.00 (5hrs)

120 - 420 = 5.00

I must have the incorect formula because it keeps giving me an answer of -418.00 when it should be 5.00? Thanks!
 
Why are you dividing txtminutes by 60?
The formula should be, although yhis gives a negative answer, I presume that's what you want.

Me.txtDTRegular = Round((Me.txtMinutes - Me.txtAccTimeWorked) / 60, 2)


Brian
 
I got the answer that doesnt give a negative number.
Code:
Me.txtDTRegular = Round((txtAccTimeWorked) - (txtMinutes / 60), 2)
Thanks for your help! I included the final test DB....
 
Last edited:
Ah! I see that you are now working in hours. I haven't opened your new DB but assume that the form looks somewhat different.

Glad everything is now ok

Brian
 
Brian, I have another question. If I go and change the time of the DT Reason 1, DT Reason 2 and DT Maintenance. How would I go about updating the form or I mean the Delay text box? It automatically changes the "Green Section" but it wont change the data in the Delay box "txtDTRegular". for everything to change I have to re-enter the minutes in the run minutes box and tick tab and it changes the info in the Delay box. Any ideas? I tried to requery the form but that doesnt work and I tried to refresh the form and that doesnt work. Even if you go to the next record and go back it still never changes the data in the "txtDTRegular".

Thanks,
 

Attachments

The only event that triggers the calculation is the after update of txtminutes, use the Forms afterupdate event for all changes to trigger a recalculation.

Brian
 
What would I add to the afterupdate of the form to trigger a re-calculation?
 
The same as in the afterupdate of txtminutes, it works the same way so whatever field you want calculted you put in the code.

Brian
 
Brian, I got to go to bed "Working Midnight" so i will se if that works when I get up...:D
 
Brian, it works and sorry for the confusion!
 

Users who are viewing this thread

Back
Top Bottom