Value is Negative Code Problem!

D

Dave Elliott

Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If
 
D

Dirk Goldgar

Dave Elliott said:
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With
 
D

Dave Elliott

OK, here is my dilemna, I have a generic form I am using to test this with
no record source.
The TextBoxes have no control source other than the one I use to subtract
one amount from another to.
Everything works fine via this form, but when I try to use it on my main
form in th real db, it does not work. All the fields are the same as the
mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is named
TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data from the
sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the main
form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after update
event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
 
D

Dirk Goldgar

Dave Elliott said:
OK, here is my dilemna, I have a generic form I am using to test this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Dirk Goldgar said:
Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may have
missed something, since I don't have your form in front of me. So you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.
 
D

Dave Elliott

Funny, If I use >0 for criteria, the Label30 shows up even though it is a
negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

Dirk Goldgar said:
Dave Elliott said:
OK, here is my dilemna, I have a generic form I am using to test this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Dirk Goldgar said:
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may have
missed something, since I don't have your form in front of me. So you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
B

Brendan Reynolds

Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the value to
make sure it is treated as a number? Something like YourControl.Visible =
(Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Funny, If I use >0 for criteria, the Label30 shows up even though it is a
negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

Dirk Goldgar said:
Dave Elliott said:
OK, here is my dilemna, I have a generic form I am using to test this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may have
missed something, since I don't have your form in front of me. So you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dave Elliott

Tried that, it did not work. If I use > rather than < it works, else it does
not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

Brendan Reynolds said:
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the value
to make sure it is treated as a number? Something like YourControl.Visible
= (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Funny, If I use >0 for criteria, the Label30 shows up even though it is a
negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

Dirk Goldgar said:
OK, here is my dilemna, I have a generic form I am using to test this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may have
missed something, since I don't have your form in front of me. So you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dirk Goldgar

Dave Elliott said:
Tried that, it did not work. If I use > rather than < it works, else
it does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

Dave, you keep posting code that is not the code that I suggested, so I
don't know whether my suggestion didn't work or not.
 
B

Brendan Reynolds

I can't reproduce this. I created a table with two fields, one long integer,
one text. I created a form with a text box and a label, and added the
following code to the AfterUpdate event procedure of the text box ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) > 0)

End Sub

I tested first with the text box bound to the long integer field, and then
with the text box bound to the text field. If I enter -1 or 0, the label is
hidden, if I enter 1, the label is shown, as expected. Next, I changed the
operator from the greater than to the less than operator ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) < 0)

End Sub

Now if I enter -1 the label is shown, if I enter 0 or 1, the label is
hidden, as expected. Again, tested with the control bound first to the long
integer field, then the text field, with the same result.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Tried that, it did not work. If I use > rather than < it works, else it
does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

Brendan Reynolds said:
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the value
to make sure it is treated as a number? Something like
YourControl.Visible = (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Funny, If I use >0 for criteria, the Label30 shows up even though it is
a negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

OK, here is my dilemna, I have a generic form I am using to test this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main
form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may have
missed something, since I don't have your form in front of me. So you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dave Elliott

My problem is that the Text Box (Text377) is not bound, (is is a computed
field)
it simply equals the balance from the sub-form. Text377 is on the main form
and Balance from where it gets it's computed
total is located in the form footer of the sub-form.
Balance on sub-form is in format currency and it's control sorce is
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])

Text377 control source is
=[FPaymentSub].[Form]![Balance]



Brendan Reynolds said:
I can't reproduce this. I created a table with two fields, one long
integer, one text. I created a form with a text box and a label, and added
the following code to the AfterUpdate event procedure of the text box ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) > 0)

End Sub

I tested first with the text box bound to the long integer field, and then
with the text box bound to the text field. If I enter -1 or 0, the label
is hidden, if I enter 1, the label is shown, as expected. Next, I changed
the operator from the greater than to the less than operator ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) < 0)

End Sub

Now if I enter -1 the label is shown, if I enter 0 or 1, the label is
hidden, as expected. Again, tested with the control bound first to the
long integer field, then the text field, with the same result.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Tried that, it did not work. If I use > rather than < it works, else it
does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

Brendan Reynolds said:
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the
value to make sure it is treated as a number? Something like
YourControl.Visible = (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Funny, If I use >0 for criteria, the Label30 shows up even though it is
a negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

OK, here is my dilemna, I have a generic form I am using to test this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main
form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may have
missed something, since I don't have your form in front of me. So you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
B

Brendan Reynolds

I tried changing the control source of the text box to an expression, and
moving the code to the current event of the form, with the same result - the
code works as expected. I'm afraid I don't have time to reproduce your
form/subform setup right now, if you're still stuck I'll try to get to that
tomorrow if I can.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
My problem is that the Text Box (Text377) is not bound, (is is a computed
field)
it simply equals the balance from the sub-form. Text377 is on the main
form and Balance from where it gets it's computed
total is located in the form footer of the sub-form.
Balance on sub-form is in format currency and it's control sorce is
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])

Text377 control source is
=[FPaymentSub].[Form]![Balance]



Brendan Reynolds said:
I can't reproduce this. I created a table with two fields, one long
integer, one text. I created a form with a text box and a label, and added
the following code to the AfterUpdate event procedure of the text box ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) > 0)

End Sub

I tested first with the text box bound to the long integer field, and
then with the text box bound to the text field. If I enter -1 or 0, the
label is hidden, if I enter 1, the label is shown, as expected. Next, I
changed the operator from the greater than to the less than operator ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) < 0)

End Sub

Now if I enter -1 the label is shown, if I enter 0 or 1, the label is
hidden, as expected. Again, tested with the control bound first to the
long integer field, then the text field, with the same result.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Tried that, it did not work. If I use > rather than < it works, else it
does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the
value to make sure it is treated as a number? Something like
YourControl.Visible = (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Funny, If I use >0 for criteria, the Label30 shows up even though it
is a negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

OK, here is my dilemna, I have a generic form I am using to test
this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main
form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may
have
missed something, since I don't have your form in front of me. So
you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dave Elliott

Alternatively, I could send a small demo db zipped to explain.Very hard to
do this via a newsgroup due to complexity.
Thanks for you help.

Brendan Reynolds said:
I tried changing the control source of the text box to an expression, and
moving the code to the current event of the form, with the same result -
the code works as expected. I'm afraid I don't have time to reproduce your
form/subform setup right now, if you're still stuck I'll try to get to that
tomorrow if I can.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
My problem is that the Text Box (Text377) is not bound, (is is a computed
field)
it simply equals the balance from the sub-form. Text377 is on the main
form and Balance from where it gets it's computed
total is located in the form footer of the sub-form.
Balance on sub-form is in format currency and it's control sorce is
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])

Text377 control source is
=[FPaymentSub].[Form]![Balance]



Brendan Reynolds said:
I can't reproduce this. I created a table with two fields, one long
integer, one text. I created a form with a text box and a label, and
added the following code to the AfterUpdate event procedure of the text
box ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) > 0)

End Sub

I tested first with the text box bound to the long integer field, and
then with the text box bound to the text field. If I enter -1 or 0, the
label is hidden, if I enter 1, the label is shown, as expected. Next, I
changed the operator from the greater than to the less than operator ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) < 0)

End Sub

Now if I enter -1 the label is shown, if I enter 0 or 1, the label is
hidden, as expected. Again, tested with the control bound first to the
long integer field, then the text field, with the same result.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Tried that, it did not work. If I use > rather than < it works, else it
does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the
value to make sure it is treated as a number? Something like
YourControl.Visible = (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


Funny, If I use >0 for criteria, the Label30 shows up even though it
is a negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

OK, here is my dilemna, I have a generic form I am using to test
this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main
form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause
the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may
have
missed something, since I don't have your form in front of me. So
you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
B

Brendan Reynolds

If the MDB will zip to less than 1MB, Dave, you can e-mail it to (change the
obvious) ...

brenreyn at brinkster dot net

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Alternatively, I could send a small demo db zipped to explain.Very hard to
do this via a newsgroup due to complexity.
Thanks for you help.

Brendan Reynolds said:
I tried changing the control source of the text box to an expression, and
moving the code to the current event of the form, with the same result -
the code works as expected. I'm afraid I don't have time to reproduce your
form/subform setup right now, if you're still stuck I'll try to get to
that tomorrow if I can.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
My problem is that the Text Box (Text377) is not bound, (is is a
computed field)
it simply equals the balance from the sub-form. Text377 is on the main
form and Balance from where it gets it's computed
total is located in the form footer of the sub-form.
Balance on sub-form is in format currency and it's control sorce is
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])

Text377 control source is
=[FPaymentSub].[Form]![Balance]



"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
I can't reproduce this. I created a table with two fields, one long
integer, one text. I created a form with a text box and a label, and
added the following code to the AfterUpdate event procedure of the text
box ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) > 0)

End Sub

I tested first with the text box bound to the long integer field, and
then with the text box bound to the text field. If I enter -1 or 0, the
label is hidden, if I enter 1, the label is shown, as expected. Next, I
changed the operator from the greater than to the less than operator
...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) < 0)

End Sub

Now if I enter -1 the label is shown, if I enter 0 or 1, the label is
hidden, as expected. Again, tested with the control bound first to the
long integer field, then the text field, with the same result.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Tried that, it did not work. If I use > rather than < it works, else
it does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the
value to make sure it is treated as a number? Something like
YourControl.Visible = (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it
impossible for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me
with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


Funny, If I use >0 for criteria, the Label30 shows up even though it
is a negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

OK, here is my dilemna, I have a generic form I am using to test
this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on
my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of
the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form
after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not
work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main
form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause
the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may
have
missed something, since I don't have your form in front of me. So
you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
B

Brendan Reynolds

It appears to be a timing issue. It seems that Access has not finished
calculating the balance when the Current event fires. I modified the code in
the form's Current event procedure as follows ...

Private Sub Form_Current()

Call EnableCtl

'Me!Label30.Visible = (Val(Nz(Me!Text377, 0)) < 0)
Me.TimerInterval = 1000

End Sub

.... and moved the line that sets the visible property of the control to the
Timer event procedure as follows ...

Private Sub Form_Timer()

Me.TimerInterval = 0
Me!Label30.Visible = (Val(Nz(Me!Text377, 0)) < 0)

End Sub

This code works as expected, the label is visible only when the balance is
negative.

I know the above is not an ideal solution, what I was primarily attempting
to do with the above code was to test my theory that the problem was a
timing issue. The result of the test does appear to support that theory. If
you could find a more efficient way of performing the calculation, I believe
that would solve the problem, but I'm afraid I don't have any specific
suggestions along those lines right now.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Alternatively, I could send a small demo db zipped to explain.Very hard to
do this via a newsgroup due to complexity.
Thanks for you help.

Brendan Reynolds said:
I tried changing the control source of the text box to an expression, and
moving the code to the current event of the form, with the same result -
the code works as expected. I'm afraid I don't have time to reproduce your
form/subform setup right now, if you're still stuck I'll try to get to
that tomorrow if I can.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
My problem is that the Text Box (Text377) is not bound, (is is a
computed field)
it simply equals the balance from the sub-form. Text377 is on the main
form and Balance from where it gets it's computed
total is located in the form footer of the sub-form.
Balance on sub-form is in format currency and it's control sorce is
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])

Text377 control source is
=[FPaymentSub].[Form]![Balance]



"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
I can't reproduce this. I created a table with two fields, one long
integer, one text. I created a form with a text box and a label, and
added the following code to the AfterUpdate event procedure of the text
box ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) > 0)

End Sub

I tested first with the text box bound to the long integer field, and
then with the text box bound to the text field. If I enter -1 or 0, the
label is hidden, if I enter 1, the label is shown, as expected. Next, I
changed the operator from the greater than to the less than operator
...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) < 0)

End Sub

Now if I enter -1 the label is shown, if I enter 0 or 1, the label is
hidden, as expected. Again, tested with the control bound first to the
long integer field, then the text field, with the same result.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Tried that, it did not work. If I use > rather than < it works, else
it does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the
value to make sure it is treated as a number? Something like
YourControl.Visible = (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it
impossible for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me
with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


Funny, If I use >0 for criteria, the Label30 shows up even though it
is a negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how to
resolve!

OK, here is my dilemna, I have a generic form I am using to test
this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on
my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of
the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form
after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not
work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main
form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause
the
value of Text377 to change. As far as I can see, these are (a) the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may
have
missed something, since I don't have your form in front of me. So
you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dave Elliott

Thanks, I believe you are right. I must concentrate as well on getting the
db to respond faster especially since it is on a network.
The main form has a lot of code to run. Thanks again for your help.

Brendan Reynolds said:
It appears to be a timing issue. It seems that Access has not finished
calculating the balance when the Current event fires. I modified the code
in the form's Current event procedure as follows ...

Private Sub Form_Current()

Call EnableCtl

'Me!Label30.Visible = (Val(Nz(Me!Text377, 0)) < 0)
Me.TimerInterval = 1000

End Sub

... and moved the line that sets the visible property of the control to
the Timer event procedure as follows ...

Private Sub Form_Timer()

Me.TimerInterval = 0
Me!Label30.Visible = (Val(Nz(Me!Text377, 0)) < 0)

End Sub

This code works as expected, the label is visible only when the balance is
negative.

I know the above is not an ideal solution, what I was primarily attempting
to do with the above code was to test my theory that the problem was a
timing issue. The result of the test does appear to support that theory.
If you could find a more efficient way of performing the calculation, I
believe that would solve the problem, but I'm afraid I don't have any
specific suggestions along those lines right now.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave Elliott said:
Alternatively, I could send a small demo db zipped to explain.Very hard
to do this via a newsgroup due to complexity.
Thanks for you help.

Brendan Reynolds said:
I tried changing the control source of the text box to an expression, and
moving the code to the current event of the form, with the same result -
the code works as expected. I'm afraid I don't have time to reproduce
your form/subform setup right now, if you're still stuck I'll try to get
to that tomorrow if I can.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


My problem is that the Text Box (Text377) is not bound, (is is a
computed field)
it simply equals the balance from the sub-form. Text377 is on the main
form and Balance from where it gets it's computed
total is located in the form footer of the sub-form.
Balance on sub-form is in format currency and it's control sorce is
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])

Text377 control source is
=[FPaymentSub].[Form]![Balance]



"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
I can't reproduce this. I created a table with two fields, one long
integer, one text. I created a form with a text box and a label, and
added the following code to the AfterUpdate event procedure of the text
box ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) > 0)

End Sub

I tested first with the text box bound to the long integer field, and
then with the text box bound to the text field. If I enter -1 or 0,
the label is hidden, if I enter 1, the label is shown, as expected.
Next, I changed the operator from the greater than to the less than
operator ...

Private Sub txtTest_AfterUpdate()

Me!lblTest.Visible = (Val(Nz(Me!txtTest, 0)) < 0)

End Sub

Now if I enter -1 the label is shown, if I enter 0 or 1, the label is
hidden, as expected. Again, tested with the control bound first to the
long integer field, then the text field, with the same result.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


Tried that, it did not work. If I use > rather than < it works, else
it does not work.
Thanks,
If (Val(Nz(Text377, 0)) > 0) Then
Label30.Visible = True
Else
Label30.Visible = False
End If

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Is it possible that the value is being treated as text? In a textual
comparison, "-1" > "0" = True. What if you wrapped Val() around the
value to make sure it is treated as a number? Something like
YourControl.Visible = (Val(NZ(YourField, 0)) > 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it
impossible for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me
with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


Funny, If I use >0 for criteria, the Label30 shows up even though
it is a negative number, but if I use
<0 it does not show up???
This has something with it being a negative number, dont know how
to resolve!

OK, here is my dilemna, I have a generic form I am using to test
this
with no record source.
The TextBoxes have no control source other than the one I use to
subtract one amount from another to.
Everything works fine via this form, but when I try to use it on
my
main form in th real db, it does not work. All the fields are the
same as the mock db except they do have a record source.
Here is the code I am using for the mock DB
More Details. The main form is named Timecards and the sub-form
is
named TimeCardPaymentSub
The main form has the Text377 on it which gets it's computed data
from the sub-form Form Footer Text Box named Balance.
The main form also has the Label30 on it.
The Text Box Balance has it's control source set to the sub-form
TimeCardPaymentSub with this calculation.
=IIf(IsNull([SumPayment]),[OrderAmtA],[OrderAmtA]-[SumPayment])
Hope this explains better, I am lost...

If IsNull([Text377]) Then ' This is on the current event of
the
main form.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If


If IsNull([Text377]) Then , This is on the sub-form
after
update event.
Label30.Visible = False
Else
Label30.Visible = ([Text377] < 0)
End If
Text377 is a computed value from another sub-form textbox.
If Text377 is a negative, i.e. Less Than Zero
Then Label30 should be visible
This code is on the current event of main form, it does not
work.

If (Text377) >= 0 Then
Label30.Visible = False
Else
Label30.Visible = (Text377) < 0
End If

Please try this and tell me if it works:

With Me!Text377
.Requery
Me!Label30.Visible = (.Value < 0)
End With

Okay, let's see what we can do with this information.

Create the following procedure in the General section of the main
form's
module. Note that the subroutine is declared as Public:

'---- start of function code ----
Public Sub SetLabelVisibility()

With Me!Text377

' The following statement may not be necessary.
' After everything is working, take it out to see.
.Requery

If IsError(.Value) Then
Me!Label30.Visible = False
Else
Me!Label30.Visible = (Nz(.Value, 0) < 0)
End If

End With

End Sub
'---- start of function code ----

Now we need to call this function in every event that might cause
the
value of Text377 to change. As far as I can see, these are (a)
the
Current event of the main form, (b) the AfterUpdate event of the
subform, and (c) the AfterDelConfirm event of the subform. I may
have
missed something, since I don't have your form in front of me. So
you
need the following event procedures.

'---- event procedure for main form's Current event ----
Private Sub Form_Current()

SetLabelVisibility

End Sub
'---- end event procedure for main form' Current event ----


'---- event procedure for subform's AfterUpdate event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterUpdate event ----


'---- event procedure for subform's AfterDelConfirm event ----
Private Sub Form_AfterUpdate()

Me.Parent.SetLabelVisibility

End Sub
'---- end event procedure for subform's AfterDelConfirm event ----

Now, I haven't tested this but something along these lines ought
to
work -- at least, I think it should if the Balance text box on the
subform doesn't contain #ERROR.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top