If then and currency

[Guest]

OK, I got it. It took some juggling and a couple of changes to the code, but
it works! Thank you so much to all who input into this discussion. I've
learned a considerable amount in a short time. Here is the completed,
working code:

Private Sub Form_Current()
Dim Int1 As Integer, Int2 As Integer, Int3 As Integer, Int4 As Integer, Int5
As Integer
Int1 = Me.[Engineering Time] * 68.52 + [Engineering Cost]
Int4 = Me.[Manuf cost non recurring]
'Bailing using time:
Dim dteStarted As Date
dteStarted = Now

'code that uses textboxes in calculations here
Do Until Int2 <> 0
Int2 = Me.[Parts to be modified].[Form]![SumofNRE]
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Do
Loop
Do Until Int3 <> 0
Int3 = Me.[Parts to be Added].[Form]![SumofNRE]
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Do
Loop
'code that uses textboxes in calculations here

Int5 = Int1 + Int2 + Int3 + Int4
If Int5 < 5000 Then

Me.[Director Approval].Enabled = False
Me.Text173.Enabled = False
Me.Text175.Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.Text173.Enabled = True
Me.Text175.Enabled = True
End If
End Sub

Good morning Wayne,

Thanks for the input. I've run your code and ended up with the same thing.
One question, though. IsNull is not the same as 0 is it? The calculated
fields from the subforms are producing zeros in my watch window. The totals
on my form have always been correct, but even bumping your loop up to 20
seconds, the form still loads immediately, which tells me that 0 is not being
considered as a null in the code. Below is the module as it stands:

Private Sub Form_Current()
Dim Int1 As Integer, Int2 As Integer, Int3 As Integer, Int4 As Integer, Int5
As Integer
Int1 = Me.[Engineering Time] * 68.52 + [Engineering Cost]
Int2 = Me.[Parts to be modified].[Form]![SumofNRE]
Int3 = Me.[Parts to be Added].[Form]![SumofNRE]
Int4 = Me.[Manuf cost non recurring]
'Bailing using time:
Dim dteStarted As Date
dteStarted = Now
Do Until Not IsNull(Int1)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 20, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here
Do Until Not IsNull(Int2)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 20, dteStarted) Then Exit Sub
Loop
Do Until Not IsNull(Int3)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 20, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here
Do Until Not IsNull(Int4)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here
Int5 = Int1 + Int2 + Int3 + Int4
If Int5 < 5000 Then

Debug.Print "Less"
Me.[Director Approval].Enabled = False
Me.Text173.Enabled = False
Me.Text175.Enabled = False
Else
Debug.Print "More"
Me.[Director Approval].Enabled = True
Me.Text173.Enabled = True
Me.Text175.Enabled = True
End If
Debug.Print Int1
Debug.Print Int2
Debug.Print Int3
Debug.Print Int4
Debug.Print Int5
End Sub

DoEvents is a command in itself. You put it on a line of code by itself. It
releases CPU time to other processes that need handled. By placing this in a
loop, you wouldn't tie up the CPU with your loop and would allow other
process to occur. In the loop, you would check for a value in the textbox
and exit the loop once you had one. If there is a potential for the textbox
to not have a value at times, you would also need a way to bail out of the
loop after a reasonable time or never enter the loop under those conditions.

Example:
Do Until Not IsNull(Me.txtMyTextbox)
DoEvents
Loop
'code that uses textboxes in calculations here

Bailing using time:
Dim dteStarted As Date
dteStarted = Now
Do Until Not IsNull(Me.txtMyTextbox)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here

 

[Wayne Morgan]

Glad you got it working Bryan. To answer your other question; no, Null and
zero are not the same. The Nz function can be used to handle this if in
doubt of which you have. Nz will change a Null to zero (or other value as
specified) and do nothing to a non Null value.

--
Wayne Morgan
MS Access MVP

OK, I got it. It took some juggling and a couple of changes to the code,
but
it works! Thank you so much to all who input into this discussion. I've
learned a considerable amount in a short time. Here is the completed,
working code:

Private Sub Form_Current()
Dim Int1 As Integer, Int2 As Integer, Int3 As Integer, Int4 As Integer,
Int5
As Integer
Int1 = Me.[Engineering Time] * 68.52 + [Engineering Cost]
Int4 = Me.[Manuf cost non recurring]
'Bailing using time:
Dim dteStarted As Date
dteStarted = Now

'code that uses textboxes in calculations here
Do Until Int2 <> 0
Int2 = Me.[Parts to be modified].[Form]![SumofNRE]
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Do
Loop
Do Until Int3 <> 0
Int3 = Me.[Parts to be Added].[Form]![SumofNRE]
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Do
Loop
'code that uses textboxes in calculations here

Int5 = Int1 + Int2 + Int3 + Int4
If Int5 < 5000 Then

Me.[Director Approval].Enabled = False
Me.Text173.Enabled = False
Me.Text175.Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.Text173.Enabled = True
Me.Text175.Enabled = True
End If
End Sub

Good morning Wayne,

Thanks for the input. I've run your code and ended up with the same
thing.
One question, though. IsNull is not the same as 0 is it? The calculated
fields from the subforms are producing zeros in my watch window. The
totals
on my form have always been correct, but even bumping your loop up to 20
seconds, the form still loads immediately, which tells me that 0 is not
being
considered as a null in the code. Below is the module as it stands:

Private Sub Form_Current()
Dim Int1 As Integer, Int2 As Integer, Int3 As Integer, Int4 As Integer,
Int5
As Integer
Int1 = Me.[Engineering Time] * 68.52 + [Engineering Cost]
Int2 = Me.[Parts to be modified].[Form]![SumofNRE]
Int3 = Me.[Parts to be Added].[Form]![SumofNRE]
Int4 = Me.[Manuf cost non recurring]
'Bailing using time:
Dim dteStarted As Date
dteStarted = Now
Do Until Not IsNull(Int1)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 20, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here
Do Until Not IsNull(Int2)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 20, dteStarted) Then Exit Sub
Loop
Do Until Not IsNull(Int3)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 20, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here
Do Until Not IsNull(Int4)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here
Int5 = Int1 + Int2 + Int3 + Int4
If Int5 < 5000 Then

Debug.Print "Less"
Me.[Director Approval].Enabled = False
Me.Text173.Enabled = False
Me.Text175.Enabled = False
Else
Debug.Print "More"
Me.[Director Approval].Enabled = True
Me.Text173.Enabled = True
Me.Text175.Enabled = True
End If
Debug.Print Int1
Debug.Print Int2
Debug.Print Int3
Debug.Print Int4
Debug.Print Int5
End Sub

DoEvents is a command in itself. You put it on a line of code by
itself. It
releases CPU time to other processes that need handled. By placing this
in a
loop, you wouldn't tie up the CPU with your loop and would allow other
process to occur. In the loop, you would check for a value in the
textbox
and exit the loop once you had one. If there is a potential for the
textbox
to not have a value at times, you would also need a way to bail out of
the
loop after a reasonable time or never enter the loop under those
conditions.

Example:
Do Until Not IsNull(Me.txtMyTextbox)
DoEvents
Loop
'code that uses textboxes in calculations here

Bailing using time:
Dim dteStarted As Date
dteStarted = Now
Do Until Not IsNull(Me.txtMyTextbox)
DoEvents
'bail after 5 seconds
If Now >= DateAdd("s", 5, dteStarted) Then Exit Sub
Loop
'code that uses textboxes in calculations here

--
Wayne Morgan
MS Access MVP


The recalc did not work. I even tried running it through a loop. I
tried
running the calculations through a subroutine and got the same
results.
Any
suggestions on what DoEvents I can run on this to help out with the
timing?
I have been thus far unsuccessful in creating a query that works.