If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. "Dave Elliott" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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" wrote in message ... "Dave Elliott" wrote in message om 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" wrote in message ... "Dave Elliott" wrote in message . com 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) |
#12
|
|||
|
|||
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" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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" wrote in message ... "Dave Elliott" wrote in message om 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" wrote in message ... "Dave Elliott" wrote in message . com 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) |
#13
|
|||
|
|||
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" brenreyn at indigo dot ie wrote in message ... 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" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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" wrote in message ... "Dave Elliott" wrote in message om 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" wrote in message ... "Dave Elliott" wrote in message . com 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) |
#14
|
|||
|
|||
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" wrote in message om... 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" brenreyn at indigo dot ie wrote in message ... 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" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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" wrote in message ... "Dave Elliott" wrote in message om 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" wrote in message ... "Dave Elliott" wrote in message . com 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) |
#15
|
|||
|
|||
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" wrote in message om... 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" brenreyn at indigo dot ie wrote in message ... 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" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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" wrote in message ... "Dave Elliott" wrote in message om 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" wrote in message ... "Dave Elliott" wrote in message . com 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) |
#16
|
|||
|
|||
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" brenreyn at indigo dot ie wrote in message ... 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" wrote in message om... 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" brenreyn at indigo dot ie wrote in message ... 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" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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. "Dave Elliott" wrote in message . com... 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" wrote in message ... "Dave Elliott" wrote in message om 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" wrote in message ... "Dave Elliott" wrote in message . com 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) |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Export to RTF very slow when code is present in Access report. | [email protected] | Setting Up & Running Reports | 11 | September 14th, 2004 08:17 PM |
Conversion of excel vba code to access vba | filnigeria | General Discussion | 5 | July 15th, 2004 02:23 AM |
Chip code problem - conditional formatting colors GetStrippedValue | Barney Fife | Worksheet Functions | 0 | June 16th, 2004 10:23 PM |