Calculation code keeps re-calculating...

[misscrf]

I have a form where a user can enter a start date (dtDateStart) and end date (dtDateEnd) or a length of time in hours (intLength). The users that work in this application will typically only know 1 or 2 pieces of this at a time, so I use code to calculate the rest. We are having an issue right now where a user may have 2 parts filled in, and the 3rd calculates, which is fine.

The problem comes in when a user wants to wipe out 1 value, because they get updated information. When a user goes to wipe out, say a start date or end date, the minute they leave the field, it re-calculates and fills the old value back in. This makes it impossible to change values.

This is my code. If anyone can help me figure out a way to run this calc. but also let the users clear out a value and add one in later, it would really help.

I was thinking of a button to clear all values, or one to run the calc, but I am not sure what would be most user friendly.

Option Compare Database

Public flgChange As Byte
Private Sub dtDateEnd_AfterUpdate()
    Call CalcMissing
End Sub

Private Sub dtDateStart_AfterUpdate()
   Call CalcMissing
End Sub

Private Sub intLength_AfterUpdate()
   Call CalcMissing
End Sub

Private Sub dtDateEnd_Change()
   flgChange = 2
End Sub
Private Sub dtDateStart_Change()
   flgChange = 1
End Sub

Private Sub intLength_Change()
   flgChange = 4
End Sub

Public Function CalcMissing()
   Dim BinVal As Byte

   'Note Binary Bit Positions: dtDateStart=1, dtDateEnd=2, intLength=4
   BinVal = Abs(IsDate(Me.dtDateStart) = True) + _
            Abs(IsDate(Me.dtDateEnd) = True) * 2 + _
            Abs(IsNumeric(Me.intLength) = True) * 4
            
   'Set BinVal by deleting sought value from all three!
   If BinVal = 7 Then 'All data present but edit was made
      If flgChange = 1 Or flgChange = 2 Then
'         Me.intLength = Null
         BinVal = 3
      ElseIf flgChange = 4 Then
'         Me.dtDateEnd = Null
         BinVal = 5
      End If
   End If
   
   flgChange = 0
         
   'Calculations Here
   If BinVal = 3 Then 'calculate hours
      Me.intLength = DateDiff("h", Me.dtDateStart, Me.dtDateEnd)
   ElseIf BinVal = 5 Then 'calculate dtDateEnd
      Me.dtDateEnd = DateAdd("h", Me.intLength, Me.dtDateStart)
   ElseIf BinVal = 6 Then 'calculate dtDateStart
      Me.dtDateStart = DateAdd("h", (Me.intLength * -1), Me.dtDateEnd)
   End If

End Function

Private Sub dtDateEnd_Click()
   flgChange = 2
   DoCmd.OpenForm "frmMiniDateTime", , , , , acDialog
   Call CalcMissing

End Sub

Private Sub dtDateStart_Click()
   flgChange = 1
   DoCmd.OpenForm "frmMiniDateTime", , , , , acDialog
   Call CalcMissing
End Sub

Thanks for any help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[sxschech]

What about storing the existing value or creating a bin value such as 99 that would be defined to mean that field was already filled out and storing that value and then when the user exits the field, the calcmissing checks to see if there was a previous value there and then puts up a message box asking if the user wants to recalculate or not, if not, then it would exit the sub (or you could create a binval that is not one of the existing binvals so that it wont execute the "if" statements.

 

[randy700]

The problem you're having, I think, is because you have the code in the AfterUpdate event. Removing data from the field is an update.
I would add a button that calls the calculation function. This way your users can make multiple changes and execute the calculation when ready.


Randy

 

[misscrf]

Thank you both for your responses. I appreciate you taking the time to help me out.

Sxs, I am not sure I understand what you are suggesting. If I store 99 as a bin value, I don't get how that would help clear out the field.

Randy, The issue with letting the user re-calculate would be that if they forget to, and they have 2 dates with the wrong hours length, then the report will be wrong.

I started working on a button to clear all values as a solution, so that they can choose to clear them out and re-enter information if they want, but the calculation will still happen once they put new info in. I got that set with a me.control.value = ""

Thanks again for all your help.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[randy700]

The issue with letting the user re-calculate would be that if they forget to, and they have 2 dates with the wrong hours length, then the report will be wrong.

And if they enter the invalid data and the recalc happens your way, won't it still result in an inaccurate repoort?
How about disabling the CLOSE button if the form is dirty and no recalc has occurred?


Randy

 

[misscrf]

I just made a delete button, which when they click it, removes all info from all 3 fields. That way they can enter in new data, if what is in there is wrong. The main user loves it.

The main goal is not usually what I know would be best db design, but how the user wants to use the app. Hence my design is not always how a db is best layed out, but best layed out for their needs. Thanks again for all your help. I appreciate you responding and talking this out with me.

misscrf

It is never too late to become what you could have been ~ George Eliot