If then and currency

[Guest]

I am having trouble with the code to gray out fields if my total is less than
$5000. The graying out part works, but for some reason the number doesn't.
I've tried it a dozen different ways. Also, would I place this in On
current, On Open or what? The field is a calculated field and my present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Wayne Morgan]

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the value of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to put
the equation from the textbox into the If statement instead. It takes a few
seconds for the calculated controls to show a value and it may be that the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change the If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a variable
then use the variable in the If statement to make things easier to read.

 

[Guest]

Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the value of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to put
the equation from the textbox into the If statement instead. It takes a few
seconds for the calculated controls to show a value and it may be that the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change the If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a variable
then use the variable in the If statement to make things easier to read.

--
Wayne Morgan
MS Access MVP

I am having trouble with the code to gray out fields if my total is less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in On
current, On Open or what? The field is a calculated field and my present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Wayne Morgan]

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used '!'
instead of '.'. This may cause you a problem in some cases if the field and
control have the same name.

Are any of the values Null? If so, then adding them together will give Null
as a result. Null propagates through an expression. Try changing the If line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0)) < 5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed out the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP

Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead. It takes a
few
seconds for the calculated controls to show a value and it may be that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a variable
then use the variable in the If statement to make things easier to read.

--
Wayne Morgan
MS Access MVP

I am having trouble with the code to gray out fields if my total is less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in On
current, On Open or what? The field is a calculated field and my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Guest]

They continue to remain gray. However, if I switch to < they are always
enabled. That tells me that it is seeing only a 0. Is there any way I can
see this in debug to know what access is seeing? I've tried 5,000,000 and
I've tried 5 with the same results. At least I may be able to see what's
happening!

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used '!'
instead of '.'. This may cause you a problem in some cases if the field and
control have the same name.

Are any of the values Null? If so, then adding them together will give Null
as a result. Null propagates through an expression. Try changing the If line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0)) < 5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed out the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP

Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead. It takes a
few
seconds for the calculated controls to show a value and it may be that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a variable
then use the variable in the If statement to make things easier to read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my total is less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in On
current, On Open or what? The field is a calculated field and my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Wayne Morgan]

You can put a break point in at the start of the If statement and hold the
mouse cursor over each item and the value of the item will popup. You can
also put in some Debug.Print statements to send them to the immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP

They continue to remain gray. However, if I switch to < they are always
enabled. That tells me that it is seeing only a 0. Is there any way I
can
see this in debug to know what access is seeing? I've tried 5,000,000 and
I've tried 5 with the same results. At least I may be able to see what's
happening!

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used
'!'
instead of '.'. This may cause you a problem in some cases if the field
and
control have the same name.

Are any of the values Null? If so, then adding them together will give
Null
as a result. Null propagates through an expression. Try changing the If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0)) <
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed out
the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP

Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I
have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

:

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead. It takes
a
few
seconds for the calculated controls to show a value and it may be that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a
variable
then use the variable in the If statement to make things easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my total is
less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in
On
current, On Open or what? The field is a calculated field and my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Guest]

Wayne, i'm sorry to be such a bother on this one, but I'm becoming more
puzzled by the moment. I ran the debug and watched my numbers, (individual
and total,) in my watch window. They were perfect and while I was using the
breakpoint and stepping through it, it worked exactly like it should. I did
it several times back and forth, above and below 5000, through two different
records and could not get it to fail. (I would break the running code at the
end sub and switch to my form.) However, as soon as I cleared my breakpoint
and worked directly from the form it no longer worked. Help!

You can put a break point in at the start of the If statement and hold the
mouse cursor over each item and the value of the item will popup. You can
also put in some Debug.Print statements to send them to the immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP

They continue to remain gray. However, if I switch to < they are always
enabled. That tells me that it is seeing only a 0. Is there any way I
can
see this in debug to know what access is seeing? I've tried 5,000,000 and
I've tried 5 with the same results. At least I may be able to see what's
happening!

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used
'!'
instead of '.'. This may cause you a problem in some cases if the field
and
control have the same name.

Are any of the values Null? If so, then adding them together will give
Null
as a result. Null propagates through an expression. Try changing the If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0)) <
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed out
the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I
have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

:

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead. It takes
a
few
seconds for the calculated controls to show a value and it may be that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a
variable
then use the variable in the If statement to make things easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my total is
less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in
On
current, On Open or what? The field is a calculated field and my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Rob Oldfield]

Try going for Wayne's suggestion of dropping some debug.print commands in
there, and run it without breakpoints. That should at least show whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

Wayne, i'm sorry to be such a bother on this one, but I'm becoming more
puzzled by the moment. I ran the debug and watched my numbers, (individual
and total,) in my watch window. They were perfect and while I was using the
breakpoint and stepping through it, it worked exactly like it should. I did
it several times back and forth, above and below 5000, through two different
records and could not get it to fail. (I would break the running code at the
end sub and switch to my form.) However, as soon as I cleared my breakpoint
and worked directly from the form it no longer worked. Help!

You can put a break point in at the start of the If statement and hold the
mouse cursor over each item and the value of the item will popup. You can
also put in some Debug.Print statements to send them to the immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP

They continue to remain gray. However, if I switch to < they are always
enabled. That tells me that it is seeing only a 0. Is there any way I
can
see this in debug to know what access is seeing? I've tried 5,000,000 and
I've tried 5 with the same results. At least I may be able to see what's
happening!

:

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used
'!'
instead of '.'. This may cause you a problem in some cases if the field
and
control have the same name.

Are any of the values Null? If so, then adding them together will give
Null
as a result. Null propagates through an expression. Try changing the If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0)) <
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed out
the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I
have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

:

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead. It takes
a
few
seconds for the calculated controls to show a value and it may be that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a
variable
then use the variable in the If statement to make things easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my total is
less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in
On
current, On Open or what? The field is a calculated field and my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Guest]

Rob, thanks for the input. By looking back at the watch window without the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

Try going for Wayne's suggestion of dropping some debug.print commands in
there, and run it without breakpoints. That should at least show whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

Wayne, i'm sorry to be such a bother on this one, but I'm becoming more
puzzled by the moment. I ran the debug and watched my numbers, (individual
and total,) in my watch window. They were perfect and while I was using the
breakpoint and stepping through it, it worked exactly like it should. I did
it several times back and forth, above and below 5000, through two different
records and could not get it to fail. (I would break the running code at the
end sub and switch to my form.) However, as soon as I cleared my breakpoint
and worked directly from the form it no longer worked. Help!

You can put a break point in at the start of the If statement and hold the
mouse cursor over each item and the value of the item will popup. You can
also put in some Debug.Print statements to send them to the immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they are always
enabled. That tells me that it is seeing only a 0. Is there any way I
can
see this in debug to know what access is seeing? I've tried 5,000,000 and
I've tried 5 with the same results. At least I may be able to see what's
happening!

:

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used
'!'
instead of '.'. This may cause you a problem in some cases if the field
and
control have the same name.

Are any of the values Null? If so, then adding them together will give
Null
as a result. Null propagates through an expression. Try changing the If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0)) <
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed out
the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I
have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

:

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead. It takes
a
few
seconds for the calculated controls to show a value and it may be that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a
variable
then use the variable in the If statement to make things easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my total is
less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in
On
current, On Open or what? The field is a calculated field and my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Rob Oldfield]

Well that is very strange.

Try this...


debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

'Pause for 5 seconds
Dim t As Date
t = Now
Do While DateDiff("s", t, Now) < 5
Loop

debug.print "123_postpause="&me.text123
debug.print "125_postpause="&me.text125
debug.print "126_postpause="&me.text126


Just to check... you're running this in the current event?

Rob, thanks for the input. By looking back at the watch window without the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

Try going for Wayne's suggestion of dropping some debug.print commands in
there, and run it without breakpoints. That should at least show whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

Wayne, i'm sorry to be such a bother on this one, but I'm becoming more
puzzled by the moment. I ran the debug and watched my numbers, (individual
and total,) in my watch window. They were perfect and while I was

using
the

breakpoint and stepping through it, it worked exactly like it should.

I
did

it several times back and forth, above and below 5000, through two different
records and could not get it to fail. (I would break the running code

at
the

end sub and switch to my form.) However, as soon as I cleared my breakpoint
and worked directly from the form it no longer worked. Help!

:

You can put a break point in at the start of the If statement and

hold
the

mouse cursor over each item and the value of the item will popup.

You
can

also put in some Debug.Print statements to send them to the immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they are always
enabled. That tells me that it is seeing only a 0. Is there any

way
I

can
see this in debug to know what access is seeing? I've tried

5,000,000
and

I've tried 5 with the same results. At least I may be able to see what's
happening!

:

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used
'!'
instead of '.'. This may cause you a problem in some cases if the field
and
control have the same name.

Are any of the values Null? If so, then adding them together will give
Null
as a result. Null propagates through an expression. Try changing

the
If

line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126],

0))
<

5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've

grayed
out

the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what

total
I

have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

:

You would need to run this in the form's Current event and

also in
the

AfterUpdate event of any control that, if changed, would

affect
the

value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead.

It
takes

a
few
seconds for the calculated controls to show a value and it may

be
that

the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a
variable
then use the variable in the If statement to make things easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my

total
is

less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place

this
in

On
current, On Open or what? The field is a calculated field

and
my

present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Marshall Barton]

Let me jump in here to say I think Wayne has put his finger
on the problem that this is typical of trying to use
caclulated text box values in VBA. The code runs at a
higher priority than control updating so the code might(?)
get the value before it's updated.

Are you sure that text123, text125 and text126 are bound to
record source fields? It sure looks like they are also
calculated controls.

Sometimes(?) you can give the control calculations enough
time by using DoEvents before retrieving their values in
code, but it's best to avoid trying to coordinate code with
control calculations by using the original field values.
--
Marsh
MVP [MS Access]

Rob, thanks for the input. By looking back at the watch window without the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

Try going for Wayne's suggestion of dropping some debug.print commands in
there, and run it without breakpoints. That should at least show whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

Wayne, i'm sorry to be such a bother on this one, but I'm becoming more
puzzled by the moment. I ran the debug and watched my numbers, (individual
and total,) in my watch window. They were perfect and while I was using the
breakpoint and stepping through it, it worked exactly like it should. I did
it several times back and forth, above and below 5000, through two different
records and could not get it to fail. (I would break the running code at the
end sub and switch to my form.) However, as soon as I cleared my breakpoint
and worked directly from the form it no longer worked. Help!

:

You can put a break point in at the start of the If statement and hold the
mouse cursor over each item and the value of the item will popup. You can
also put in some Debug.Print statements to send them to the immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they are always
enabled. That tells me that it is seeing only a 0. Is there any way I
can
see this in debug to know what access is seeing? I've tried 5,000,000 and
I've tried 5 with the same results. At least I may be able to see what's
happening!

:

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an Else is
all you need. I noticed also that for [Dir Current Date] you have used
'!'
instead of '.'. This may cause you a problem in some cases if the field
and
control have the same name.

Are any of the values Null? If so, then adding them together will give
Null
as a result. Null propagates through an expression. Try changing the If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0)) <
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed out
the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total I
have
in the box.
Do you see anything wrong with the statement? I quite puzzled here!

:

You would need to run this in the form's Current event and also in the
AfterUpdate event of any control that, if changed, would affect the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may need to
put
the equation from the textbox into the If statement instead. It takes
a
few
seconds for the calculated controls to show a value and it may be that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a
variable
then use the variable in the If statement to make things easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my total is
less
than
$5000. The graying out part works, but for some reason the number
doesn't.
I've tried it a dozen different ways. Also, would I place this in
On
current, On Open or what? The field is a calculated field and my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Guest]

I see your point and yes, I'm sorry I didn't clarify that, but 2 of the
fields are calculated. What can I do, then, to accomplish my goal of graying
out those fields if my total is less than $5000? I'm enclosing the controls
of the the three fields in case I can somehow write them into one calculated
statement. I've tried, but I can't get the sytax right.

=Sum([engineering time]*68.52+[engineering cost])

=NZ([Parts to be modified].[Form]![SumofNRE])+NZ([Parts to be
Added].[Form]![SumofNRE])

Manuf cost non recurring

I really appreciate your patience and help!

Let me jump in here to say I think Wayne has put his finger
on the problem that this is typical of trying to use
caclulated text box values in VBA. The code runs at a
higher priority than control updating so the code might(?)
get the value before it's updated.

Are you sure that text123, text125 and text126 are bound to
record source fields? It sure looks like they are also
calculated controls.

Sometimes(?) you can give the control calculations enough
time by using DoEvents before retrieving their values in
code, but it's best to avoid trying to coordinate code with
control calculations by using the original field values.
--
Marsh
MVP [MS Access]

Rob, thanks for the input. By looking back at the watch window without the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

Try going for Wayne's suggestion of dropping some debug.print commands in
there, and run it without breakpoints. That should at least show whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If





Wayne, i'm sorry to be such a bother on this one, but I'm becoming more
puzzled by the moment. I ran the debug and watched my numbers,
(individual
and total,) in my watch window. They were perfect and while I was using
the
breakpoint and stepping through it, it worked exactly like it should. I
did
it several times back and forth, above and below 5000, through two
different
records and could not get it to fail. (I would break the running code at
the
end sub and switch to my form.) However, as soon as I cleared my
breakpoint
and worked directly from the form it no longer worked. Help!

:

You can put a break point in at the start of the If statement and hold
the
mouse cursor over each item and the value of the item will popup. You
can
also put in some Debug.Print statements to send them to the immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they are
always
enabled. That tells me that it is seeing only a 0. Is there any way
I
can
see this in debug to know what access is seeing? I've tried 5,000,000
and
I've tried 5 with the same results. At least I may be able to see
what's
happening!

:

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so an
Else is
all you need. I noticed also that for [Dir Current Date] you have
used
'!'
instead of '.'. This may cause you a problem in some cases if the
field
and
control have the same name.

Are any of the values Null? If so, then adding them together will
give
Null
as a result. Null propagates through an expression. Try changing the
If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) + Nz(Me.[Text126], 0))
<
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've grayed
out
the
boxes, they will stay that way until you close and open the form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what total
I
have
in the box.
Do you see anything wrong with the statement? I quite puzzled
here!

:

You would need to run this in the form's Current event and also in
the
AfterUpdate event of any control that, if changed, would affect
the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you may
need to
put
the equation from the textbox into the If statement instead. It
takes
a
few
seconds for the calculated controls to show a value and it may be
that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then
change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result to a
variable
then use the variable in the If statement to make things easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my total
is
less
than
$5000. The graying out part works, but for some reason the
number
doesn't.
I've tried it a dozen different ways. Also, would I place this
in
On
current, On Open or what? The field is a calculated field and
my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Wayne Morgan]

If you can't get them into one calculated statement, then you can assign
each individual calculation to a variable then combine those variables into
a calculation; essentially, just as you are doing on the form.

Example:
Dim int1 As Integer, int2 As Integer, dbl3 As Double
int1 = Me!Field1 + Me!Field2
int2 = Me!Field3 * Me!Field4
dbl3 = int1 / int2

--
Wayne Morgan
MS Access MVP

I see your point and yes, I'm sorry I didn't clarify that, but 2 of the
fields are calculated. What can I do, then, to accomplish my goal of
graying
out those fields if my total is less than $5000? I'm enclosing the
controls
of the the three fields in case I can somehow write them into one
calculated
statement. I've tried, but I can't get the sytax right.

=Sum([engineering time]*68.52+[engineering cost])

=NZ([Parts to be modified].[Form]![SumofNRE])+NZ([Parts to be
Added].[Form]![SumofNRE])

Manuf cost non recurring

I really appreciate your patience and help!

Let me jump in here to say I think Wayne has put his finger
on the problem that this is typical of trying to use
caclulated text box values in VBA. The code runs at a
higher priority than control updating so the code might(?)
get the value before it's updated.

Are you sure that text123, text125 and text126 are bound to
record source fields? It sure looks like they are also
calculated controls.

Sometimes(?) you can give the control calculations enough
time by using DoEvents before retrieving their values in
code, but it's best to avoid trying to coordinate code with
control calculations by using the original field values.
--
Marsh
MVP [MS Access]

Rob, thanks for the input. By looking back at the watch window without
the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

:

Try going for Wayne's suggestion of dropping some debug.print commands
in
there, and run it without breakpoints. That should at least show
whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If





Wayne, i'm sorry to be such a bother on this one, but I'm becoming
more
puzzled by the moment. I ran the debug and watched my numbers,
(individual
and total,) in my watch window. They were perfect and while I was
using
the
breakpoint and stepping through it, it worked exactly like it
should. I
did
it several times back and forth, above and below 5000, through two
different
records and could not get it to fail. (I would break the running
code at
the
end sub and switch to my form.) However, as soon as I cleared my
breakpoint
and worked directly from the form it no longer worked. Help!

:

You can put a break point in at the start of the If statement and
hold
the
mouse cursor over each item and the value of the item will popup.
You
can
also put in some Debug.Print statements to send them to the
immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they
are
always
enabled. That tells me that it is seeing only a 0. Is there
any way
I
can
see this in debug to know what access is seeing? I've tried
5,000,000
and
I've tried 5 with the same results. At least I may be able to
see
what's
happening!

:

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so
an
Else is
all you need. I noticed also that for [Dir Current Date] you
have
used
'!'
instead of '.'. This may cause you a problem in some cases if
the
field
and
control have the same name.

Are any of the values Null? If so, then adding them together
will
give
Null
as a result. Null propagates through an expression. Try
changing the
If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) +
Nz(Me.[Text126], 0))
<
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've
grayed
out
the
boxes, they will stay that way until you close and open the
form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what
total
I
have
in the box.
Do you see anything wrong with the statement? I quite
puzzled
here!

:

You would need to run this in the form's Current event and
also in
the
AfterUpdate event of any control that, if changed, would
affect
the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you
may
need to
put
the equation from the textbox into the If statement instead.
It
takes
a
few
seconds for the calculated controls to show a value and it
may be
that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then
change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result
to a
variable
then use the variable in the If statement to make things
easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my
total
is
less
than
$5000. The graying out part works, but for some reason
the
number
doesn't.
I've tried it a dozen different ways. Also, would I place
this
in
On
current, On Open or what? The field is a calculated field
and
my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Marshall Barton]

Bryan wrote:
Ohh boy, the calculations include aggregate functions.
Unfortunately, there's no way to directly use Sum in VBA.
There may be a way to use DSum to (re)calculate the total,
but that can get very messy in some cases.

I think I would try to bail out of this by using one or more
DoEvents and call it quits if it works most(?) of the time
:-(

The only serious alternative I can think of is to perform
the aggregate calculations in the form's Record source
query. This will probably require you to create another
query with the Sum calculation(s?) and Joining that back
into your current record source table/query.

It may be even messier than that if the subform calculations
also use an aggregate function.

Hopefully, someone else (Wayne?) will have a flash of genius
and come up with a slick way to do all this.
--
Marsh
MVP [MS Access]

I see your point and yes, I'm sorry I didn't clarify that, but 2 of the
fields are calculated. What can I do, then, to accomplish my goal of graying
out those fields if my total is less than $5000? I'm enclosing the controls
of the the three fields in case I can somehow write them into one calculated
statement. I've tried, but I can't get the sytax right.

=Sum([engineering time]*68.52+[engineering cost])

=NZ([Parts to be modified].[Form]![SumofNRE])+NZ([Parts to be
Added].[Form]![SumofNRE])

Manuf cost non recurring

Let me jump in here to say I think Wayne has put his finger
on the problem that this is typical of trying to use
caclulated text box values in VBA. The code runs at a
higher priority than control updating so the code might(?)
get the value before it's updated.

Are you sure that text123, text125 and text126 are bound to
record source fields? It sure looks like they are also
calculated controls.

Sometimes(?) you can give the control calculations enough
time by using DoEvents before retrieving their values in
code, but it's best to avoid trying to coordinate code with
control calculations by using the original field values.

Rob, thanks for the input. By looking back at the watch window without the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

:
Try going for Wayne's suggestion of dropping some debug.print commands in
there, and run it without breakpoints. That should at least show whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

 

[Guest]

Thanks Wayne. I redid my module and now the first and the third numbers show
up, (though rounded,) but not the 2nd one which is based on totals from 2
subforms. I works great if I step through it, but not in realtime. Any
suggestions as to what I can do to rectify the timing.

Here's what I have:

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.[Manuf cost non recurring]
int5 = Me.[Parts to be added].[Form].[SumofNRE]
int4 = int1 + int2 + int3 + int5
If int4 < 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
Debug.Print int1
Debug.Print int2
Debug.Print int3
Debug.Print int5
Debug.Print int4
End Sub

If you can't get them into one calculated statement, then you can assign
each individual calculation to a variable then combine those variables into
a calculation; essentially, just as you are doing on the form.

Example:
Dim int1 As Integer, int2 As Integer, dbl3 As Double
int1 = Me!Field1 + Me!Field2
int2 = Me!Field3 * Me!Field4
dbl3 = int1 / int2

--
Wayne Morgan
MS Access MVP

I see your point and yes, I'm sorry I didn't clarify that, but 2 of the
fields are calculated. What can I do, then, to accomplish my goal of
graying
out those fields if my total is less than $5000? I'm enclosing the
controls
of the the three fields in case I can somehow write them into one
calculated
statement. I've tried, but I can't get the sytax right.

=Sum([engineering time]*68.52+[engineering cost])

=NZ([Parts to be modified].[Form]![SumofNRE])+NZ([Parts to be
Added].[Form]![SumofNRE])

Manuf cost non recurring

I really appreciate your patience and help!

Let me jump in here to say I think Wayne has put his finger
on the problem that this is typical of trying to use
caclulated text box values in VBA. The code runs at a
higher priority than control updating so the code might(?)
get the value before it's updated.

Are you sure that text123, text125 and text126 are bound to
record source fields? It sure looks like they are also
calculated controls.

Sometimes(?) you can give the control calculations enough
time by using DoEvents before retrieving their values in
code, but it's best to avoid trying to coordinate code with
control calculations by using the original field values.
--
Marsh
MVP [MS Access]



Bryan wrote:
Rob, thanks for the input. By looking back at the watch window without
the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

:

Try going for Wayne's suggestion of dropping some debug.print commands
in
there, and run it without breakpoints. That should at least show
whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If





Wayne, i'm sorry to be such a bother on this one, but I'm becoming
more
puzzled by the moment. I ran the debug and watched my numbers,
(individual
and total,) in my watch window. They were perfect and while I was
using
the
breakpoint and stepping through it, it worked exactly like it
should. I
did
it several times back and forth, above and below 5000, through two
different
records and could not get it to fail. (I would break the running
code at
the
end sub and switch to my form.) However, as soon as I cleared my
breakpoint
and worked directly from the form it no longer worked. Help!

:

You can put a break point in at the start of the If statement and
hold
the
mouse cursor over each item and the value of the item will popup.
You
can
also put in some Debug.Print statements to send them to the
immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they
are
always
enabled. That tells me that it is seeing only a 0. Is there
any way
I
can
see this in debug to know what access is seeing? I've tried
5,000,000
and
I've tried 5 with the same results. At least I may be able to
see
what's
happening!

:

There is a syntax error in the If statement at the Else part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to, so
an
Else is
all you need. I noticed also that for [Dir Current Date] you
have
used
'!'
instead of '.'. This may cause you a problem in some cases if
the
field
and
control have the same name.

Are any of the values Null? If so, then adding them together
will
give
Null
as a result. Null propagates through an expression. Try
changing the
If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) +
Nz(Me.[Text126], 0))
<
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've
grayed
out
the
boxes, they will stay that way until you close and open the
form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000 Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter what
total
I
have
in the box.
Do you see anything wrong with the statement? I quite
puzzled
here!

:

You would need to run this in the form's Current event and
also in
the
AfterUpdate event of any control that, if changed, would
affect
the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work, you
may
need to
put
the equation from the textbox into the If statement instead.
It
takes
a
few
seconds for the calculated controls to show a value and it
may be
that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2, then
change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its result
to a
variable
then use the variable in the If statement to make things
easier to
read.

--
Wayne Morgan
MS Access MVP


I am having trouble with the code to gray out fields if my
total
is
less
than
$5000. The graying out part works, but for some reason
the
number
doesn't.
I've tried it a dozen different ways. Also, would I place
this
in
On
current, On Open or what? The field is a calculated field
and
my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Wayne Morgan]

As Marshall pointed out, if you can do the calculations in the queries
feeding the forms, at least the one feeding the subform, then the controls
on the form would be bound to these new calculated fields in the query and
the timing issue will go away. Otherwise, you're going to have to continue
to recalculate everything in the code, one step at a time.

I don't know if adding a Me.Recalculate before trying the calculation in the
code would force the code to wait on the form to do the calculations, but it
may be worth trying. As Marshall pointed out, DoEvents may also release
control long enough for the calculations to occur. Another option would be a
Loop with a DoEvents in the loop. Keep looping until there are values in all
of the calculated controls.

--
Wayne Morgan
MS Access MVP

Thanks Wayne. I redid my module and now the first and the third numbers
show
up, (though rounded,) but not the 2nd one which is based on totals from 2
subforms. I works great if I step through it, but not in realtime. Any
suggestions as to what I can do to rectify the timing.

Here's what I have:

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.[Manuf cost non recurring]
int5 = Me.[Parts to be added].[Form].[SumofNRE]
int4 = int1 + int2 + int3 + int5
If int4 < 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
Debug.Print int1
Debug.Print int2
Debug.Print int3
Debug.Print int5
Debug.Print int4
End Sub

If you can't get them into one calculated statement, then you can assign
each individual calculation to a variable then combine those variables
into
a calculation; essentially, just as you are doing on the form.

Example:
Dim int1 As Integer, int2 As Integer, dbl3 As Double
int1 = Me!Field1 + Me!Field2
int2 = Me!Field3 * Me!Field4
dbl3 = int1 / int2

--
Wayne Morgan
MS Access MVP

I see your point and yes, I'm sorry I didn't clarify that, but 2 of the
fields are calculated. What can I do, then, to accomplish my goal of
graying
out those fields if my total is less than $5000? I'm enclosing the
controls
of the the three fields in case I can somehow write them into one
calculated
statement. I've tried, but I can't get the sytax right.

=Sum([engineering time]*68.52+[engineering cost])

=NZ([Parts to be modified].[Form]![SumofNRE])+NZ([Parts to be
Added].[Form]![SumofNRE])

Manuf cost non recurring

I really appreciate your patience and help!



:

Let me jump in here to say I think Wayne has put his finger
on the problem that this is typical of trying to use
caclulated text box values in VBA. The code runs at a
higher priority than control updating so the code might(?)
get the value before it's updated.

Are you sure that text123, text125 and text126 are bound to
record source fields? It sure looks like they are also
calculated controls.

Sometimes(?) you can give the control calculations enough
time by using DoEvents before retrieving their values in
code, but it's best to avoid trying to coordinate code with
control calculations by using the original field values.
--
Marsh
MVP [MS Access]



Bryan wrote:
Rob, thanks for the input. By looking back at the watch window
without
the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

:

Try going for Wayne's suggestion of dropping some debug.print
commands
in
there, and run it without breakpoints. That should at least show
whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If





Wayne, i'm sorry to be such a bother on this one, but I'm
becoming
more
puzzled by the moment. I ran the debug and watched my numbers,
(individual
and total,) in my watch window. They were perfect and while I
was
using
the
breakpoint and stepping through it, it worked exactly like it
should. I
did
it several times back and forth, above and below 5000, through
two
different
records and could not get it to fail. (I would break the running
code at
the
end sub and switch to my form.) However, as soon as I cleared my
breakpoint
and worked directly from the form it no longer worked. Help!

:

You can put a break point in at the start of the If statement
and
hold
the
mouse cursor over each item and the value of the item will
popup.
You
can
also put in some Debug.Print statements to send them to the
immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they
are
always
enabled. That tells me that it is seeing only a 0. Is there
any way
I
can
see this in debug to know what access is seeing? I've tried
5,000,000
and
I've tried 5 with the same results. At least I may be able
to
see
what's
happening!

:

There is a syntax error in the If statement at the Else
part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to,
so
an
Else is
all you need. I noticed also that for [Dir Current Date] you
have
used
'!'
instead of '.'. This may cause you a problem in some cases
if
the
field
and
control have the same name.

Are any of the values Null? If so, then adding them together
will
give
Null
as a result. Null propagates through an expression. Try
changing the
If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) +
Nz(Me.[Text126], 0))
<
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've
grayed
out
the
boxes, they will stay that way until you close and open the
form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000
Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000
Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter
what
total
I
have
in the box.
Do you see anything wrong with the statement? I quite
puzzled
here!

:

You would need to run this in the form's Current event
and
also in
the
AfterUpdate event of any control that, if changed, would
affect
the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work,
you
may
need to
put
the equation from the textbox into the If statement
instead.
It
takes
a
few
seconds for the calculated controls to show a value and
it
may be
that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2,
then
change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its
result
to a
variable
then use the variable in the If statement to make things
easier to
read.

--
Wayne Morgan
MS Access MVP


message
I am having trouble with the code to gray out fields if
my
total
is
less
than
$5000. The graying out part works, but for some reason
the
number
doesn't.
I've tried it a dozen different ways. Also, would I
place
this
in
On
current, On Open or what? The field is a calculated
field
and
my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Wayne Morgan]

I believe I overstated an item in the previous post. I think that you should
only have to wait for a value in the last calculated control in the chain,
but I'm not sure

 

[Guest]

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.

As Marshall pointed out, if you can do the calculations in the queries
feeding the forms, at least the one feeding the subform, then the controls
on the form would be bound to these new calculated fields in the query and
the timing issue will go away. Otherwise, you're going to have to continue
to recalculate everything in the code, one step at a time.

I don't know if adding a Me.Recalculate before trying the calculation in the
code would force the code to wait on the form to do the calculations, but it
may be worth trying. As Marshall pointed out, DoEvents may also release
control long enough for the calculations to occur. Another option would be a
Loop with a DoEvents in the loop. Keep looping until there are values in all
of the calculated controls.

--
Wayne Morgan
MS Access MVP

Thanks Wayne. I redid my module and now the first and the third numbers
show
up, (though rounded,) but not the 2nd one which is based on totals from 2
subforms. I works great if I step through it, but not in realtime. Any
suggestions as to what I can do to rectify the timing.

Here's what I have:

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.[Manuf cost non recurring]
int5 = Me.[Parts to be added].[Form].[SumofNRE]
int4 = int1 + int2 + int3 + int5
If int4 < 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
Debug.Print int1
Debug.Print int2
Debug.Print int3
Debug.Print int5
Debug.Print int4
End Sub

If you can't get them into one calculated statement, then you can assign
each individual calculation to a variable then combine those variables
into
a calculation; essentially, just as you are doing on the form.

Example:
Dim int1 As Integer, int2 As Integer, dbl3 As Double
int1 = Me!Field1 + Me!Field2
int2 = Me!Field3 * Me!Field4
dbl3 = int1 / int2

--
Wayne Morgan
MS Access MVP


I see your point and yes, I'm sorry I didn't clarify that, but 2 of the
fields are calculated. What can I do, then, to accomplish my goal of
graying
out those fields if my total is less than $5000? I'm enclosing the
controls
of the the three fields in case I can somehow write them into one
calculated
statement. I've tried, but I can't get the sytax right.

=Sum([engineering time]*68.52+[engineering cost])

=NZ([Parts to be modified].[Form]![SumofNRE])+NZ([Parts to be
Added].[Form]![SumofNRE])

Manuf cost non recurring

I really appreciate your patience and help!



:

Let me jump in here to say I think Wayne has put his finger
on the problem that this is typical of trying to use
caclulated text box values in VBA. The code runs at a
higher priority than control updating so the code might(?)
get the value before it's updated.

Are you sure that text123, text125 and text126 are bound to
record source fields? It sure looks like they are also
calculated controls.

Sometimes(?) you can give the control calculations enough
time by using DoEvents before retrieving their values in
code, but it's best to avoid trying to coordinate code with
control calculations by using the original field values.
--
Marsh
MVP [MS Access]



Bryan wrote:
Rob, thanks for the input. By looking back at the watch window
without
the
breakpoint my readings are indeed different than with.
123=
125=0
126=525.25
total=525.25

whereas my form and the watch window with breakpoint shows

123=320.77
125=5200
126=525.25
total=6046.02

Any ideas on how to correct the timing issue?

:

Try going for Wayne's suggestion of dropping some debug.print
commands
in
there, and run it without breakpoints. That should at least show
whether
your problems are down to some kind of timing issue. e.g.

debug.print "123="&me.text123
debug.print "125="&me.text125
debug.print "126="&me.text126

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
debug.print "Less"
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
debug.print "More"
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If





Wayne, i'm sorry to be such a bother on this one, but I'm
becoming
more
puzzled by the moment. I ran the debug and watched my numbers,
(individual
and total,) in my watch window. They were perfect and while I
was
using
the
breakpoint and stepping through it, it worked exactly like it
should. I
did
it several times back and forth, above and below 5000, through
two
different
records and could not get it to fail. (I would break the running
code at
the
end sub and switch to my form.) However, as soon as I cleared my
breakpoint
and worked directly from the form it no longer worked. Help!

:

You can put a break point in at the start of the If statement
and
hold
the
mouse cursor over each item and the value of the item will
popup.
You
can
also put in some Debug.Print statements to send them to the
immediate
window.

Example:
Debug.Print Me.Text123
or
Debug.Print "Text123 = " & Me.Text123

--
Wayne Morgan
MS Access MVP


They continue to remain gray. However, if I switch to < they
are
always
enabled. That tells me that it is seeing only a 0. Is there
any way
I
can
see this in debug to know what access is seeing? I've tried
5,000,000
and
I've tried 5 with the same results. At least I may be able
to
see
what's
happening!

:

There is a syntax error in the If statement at the Else
part.

If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If

If it's not less than, then it is greater than or equal to,
so
an
Else is
all you need. I noticed also that for [Dir Current Date] you
have
used
'!'
instead of '.'. This may cause you a problem in some cases
if
the
field
and
control have the same name.

Are any of the values Null? If so, then adding them together
will
give
Null
as a result. Null propagates through an expression. Try
changing the
If
line
to:

If (Nz(Me.[Text123], 0) + Nz(Me.[Text125], 0) +
Nz(Me.[Text126], 0))
<
5000
Then

This will add 0 if the value is Null.

You are correct, you need the Else, without it, once you've
grayed
out
the
boxes, they will stay that way until you close and open the
form.

--
Wayne Morgan
MS Access MVP


Still does not work, Wayne. This is what I have now:

Private Sub Form_Current()
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) < 5000
Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
Else
If (Me.[Text123] + Me.[Text125] + Me.[Text126]) >= 5000
Then
Me.[Director Approval].Enabled = True
Me.[Dir Signature].Enabled = True
Me![Dir Current Date].Enabled = True
End If
End If
End Sub

I tried an else since it just stays grayed out no matter
what
total
I
have
in the box.
Do you see anything wrong with the statement? I quite
puzzled
here!

:

You would need to run this in the form's Current event
and
also in
the
AfterUpdate event of any control that, if changed, would
affect
the
value
of
SumOfNRE.

If referring to the value of the textbox doesn't work,
you
may
need to
put
the equation from the textbox into the If statement
instead.
It
takes
a
few
seconds for the calculated controls to show a value and
it
may be
that
the
code has passed that point already.

For example, if the equation in SumOfNRE is =txt1+txt2,
then
change
the
If
statement to:

If (Me.txt1 + Me.txt2) < 5000 Then

If the equation is long, you may want to assign its
result
to a
variable
then use the variable in the If statement to make things
easier to
read.

--
Wayne Morgan
MS Access MVP


message
I am having trouble with the code to gray out fields if
my
total
is
less
than
$5000. The graying out part works, but for some reason
the
number
doesn't.
I've tried it a dozen different ways. Also, would I
place
this
in
On
current, On Open or what? The field is a calculated
field
and
my
present
code is below. Thanks in advance!

If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Dir Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If

 

[Wayne Morgan]

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

 

[Guest]

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