 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / June 2007GETTING ERROR# AS DEFAULT VALUE IN CALCULATED TEXT FEILD
Hello All, I have a subform were the user inputs Qty received . The form has the total qty received were it sums the qty recieved and displays. The problem I am facing is, when there are no items received yet the Qty received displays as 0 and the total is blank which is correct. Everything was working fine till i gave selective access to the users to use the form. Those who have full data user access have no issues. The users who have read only access get an error# in the total qty received calculated text box. When they click on the item that has not been received yet there is no row displayed on the subform and so the value in the text field is error# . Is there anyway i can get it to display 0. I tried default value options, valid text option!! any help would be appreciated. thanks >I have a subform were the user inputs Qty received . The form has the total >qty received were it sums the qty recieved and displays. The problem I am [quoted text clipped - 6 lines] >so the value in the text field is error# . Is there anyway i can get it to >display 0. I tried default value options, valid text option!! The error is, as you said, because there is nothing to sum. I guess there is a question why different users should see a different total. Isn't the quantity received the same regardless of who looks at it, even if some of them can not see all of the records? If so, then you might want to use DSum to calculate the total in the table instead of just the total of what is displayed on the form. If you really do want to display the total of just the data displayed in the form, you can get a zero by checking for the situation where there is no information: =IIf(Form.Recordset.RecordCount > 0, Sum(Qty), 0)  SignatureMarsh MVP [MS Access] Thanks Marshall, Were does one use this formula. I tried it in the validation rule section of the text field holding the value. The sum is getting calculated in form which is called up as the subform. So i am confused as to were to apply this formula. > >I have a subform were the user inputs Qty received . The form has the total > >qty received were it sums the qty recieved and displays. The problem I am [quoted text clipped - 20 lines] > the situation where there is no information: > =IIf(Form.Recordset.RecordCount > 0, Sum(Qty), 0) You were supposed to use that in the subform total text box's Control Source instead of just using =Sum(Amt) If that's not what you are doing, then maybe you are calculating the total in the subform but displaying it in a mainform text box. with this arrangement, the subform header/footer text box uses the control source expression: =Sum(Amt) The main form text box that is used to display the total calculated in the subform would use a control source expression like: =IIf(subformcontrol.Form.Recordset.RecordCount > 0, subformcontrol.Form.subformtextbox, 0) If I am still missing the point, please explain where and how the total is calculated and where it displayed SignatureMarsh MVP [MS Access] >Were does one use this formula. I tried it in the validation rule section of >the text field holding the value. The sum is getting calculated in form which [quoted text clipped - 16 lines] >> the situation where there is no information: >> =IIf(Form.Recordset.RecordCount > 0, Sum(Qty), 0) Hi Marshall, I have a situation were i calculate a subform total. sum([stock_in_qty]) name this text box final_sum_tot_in In the main form call the subform in a datasheet view and call the text box the formula is automatically selected as =child12.form!final_sum_tot_in when i apply your formula, i dont think i am applyling it in the right order. =IIf(Form.Recordset.RecordCount > 0, Sum(stock_in_Qty), 0) in the subform. In the main form i use IIf(child12.form!final_sum_tot_in > 0, child12.form!final_sum_tot_in, 0) is this correct. I am quite new to form coding , any help at your end would be appreciated. > You were supposed to use that in the subform total text > box's Control Source instead of just using =Sum(Amt) [quoted text clipped - 33 lines] > >> the situation where there is no information: > >> =IIf(Form.Recordset.RecordCount > 0, Sum(Qty), 0) >I have a situation were i calculate a subform total. > [quoted text clipped - 9 lines] >IIf(child12.form!final_sum_tot_in > 0, >child12.form!final_sum_tot_in, 0) Try this in the main form text box: =IIf(child12.Form.Recordset.RecordCount > 0, child12.form!final_sum_tot_in, 0) SignatureMarsh MVP [MS Access] Since you're talking about a calculated value, is it possible that Access doesn't allow the calculations to be run since the form is Read Only? SignatureThere's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000 I tried this formula in the main form =IIf([Child12].[Form].[Recordset].[RecordCount]>0,[Child12].[Form]![final_sum_tot_in],0) I am getting a #name? error what does this mean > >I have a situation were i calculate a subform total. > > [quoted text clipped - 14 lines] > =IIf(child12.Form.Recordset.RecordCount > 0, > child12.form!final_sum_tot_in, 0) >I tried this formula in the main form > >=IIf([Child12].[Form].[Recordset].[RecordCount]>0,[Child12].[Form]![final_sum_tot_in],0) > >I am getting a #name? error what does this mean #Name means that one of the names in the expression does not exist. You only have two names to double check: final_sum_tot_in is the name of the =Sum(...) text box in the subform Child12 is the name of the subform ***CONTROL*** on the main form. Don't confuse the name of the control that displays the form being used as a subform with the name of form being displayed (they may or may not have the same name). SignatureMarsh MVP [MS Access] Hi Marshall, I am back. I tried this =IIf(Child24.Form!tot_received>0,Child24.Form!tot_received,0) formala it worked for full data users the total dispalyes 0 for items not yet received. When read only users open the form the same error# is listed on the text. Is there any way i can show them the first record for read only users. Is there any way to work around this!! Thanks for your help. > >I tried this formula in the main form > > [quoted text clipped - 13 lines] > form being displayed (they may or may not have the same > name). Is this a different problem? What happened to the #Name problem? Why are you using Child24? What happened to Child12? What situation are you trying to deal with now? What does the form do differently for "read only" users? What error number are you getting now? Or is the text box displaying #Error? If the text box is displaying #Error when the subform does not have any records to display, then the problem is the same as before. Checking for Child24.Form!tot_received>0 is INVALID when there are no records to sum up, which is why I keep telling you to check the subform's recordset's record count. SignatureMarsh MVP [MS Access] >I am back. I tried this > [quoted text clipped - 22 lines] >> form being displayed (they may or may not have the same >> name). Hi Marshall, I did not mean to mislead you!! I have changed my subform and hence child24. I am using this formula on the main form text feild. =IIf(Child24.Form.Recordset.RecordCount>0,Child24.Form!tot_received,0) If I understand it , child24.form (points to subform) it checks for records and if it is not null it would give the total and if false it should display 0 . I have the database preloaded with product and item nos. When a person receives an item qty the total should be displayed , till then the qty should be 0. The message which is displayed using the above formula is #Name? and the values were items have been received does not total off. I have a subform (child24) were i total the qty using sum(qty) call it tot_received. In my main form i open my main form and then the subform and select tot_received it automatically points to child24.Form!tot_received. so i am positive that child24 is my childform. were can i see the name of my main form value. what am i doing wrong. Thanks for your patience but perhaps i am missing something which is very simple. > Is this a different problem? What happened to the #Name > problem? Why are you using Child24? What happened to [quoted text clipped - 38 lines] > >> form being displayed (they may or may not have the same > >> name). For an explanation of #Name, review my previous reply. Let's back up a little and try to find out what's going on in the subform. Is it displayed in single, continuous or datasheet view? If it is in datasheet, then change it to single or continuous and make sure the form footer section is visible. When you switch the main form from design view to form view, what is displayed in the subform total text box? SignatureMarsh MVP [MS Access] >I have changed my subform and hence child24. > [quoted text clipped - 63 lines] >> >> form being displayed (they may or may not have the same >> >> name). I have created a subform in tabular view. Ensured the footer section is visible. I have included the subform in the main form. when i open the main form in the design view and go to the subform footer secion this is the formula which sum contains. =Sum([UnitsReceived]) I have named the text field -tot_received Am i correct till this step! > For an explanation of #Name, review my previous reply. > [quoted text clipped - 72 lines] > >> >> form being displayed (they may or may not have the same > >> >> name). That looks correct, what does the subform total text box display? Is it different from what the main form text box displays. SignatureMarsh MVP [MS Access] >I have created a subform in tabular view. Ensured the footer section is >visible. [quoted text clipped - 85 lines] >> >> >> form being displayed (they may or may not have the same >> >> >> name). Hi Marshall, I am back!! The subform text feild contains: =Sum([UnitsReceived]) The mainform text feild contains: =[Child24].[Form]![tot_received] were tot_received = sum([UnitsReceived]) How to proceed from here!! Thanks once again!! > That looks correct, what does the subform total text box > display? Is it different from what the main form text box [quoted text clipped - 88 lines] > >> >> >> form being displayed (they may or may not have the same > >> >> >> name). Hi Marshall I found the solution to my problem in the same discussion group with the subject #error solution given by ofer. I used this formula and it worked. Now for all read only users the #error is replaced by 0 and all calculated values are being displayed correctly. It checks for iserror. =IIf(IsError(Child24.Form!tot_received),0,Child24.Form!tot_received) but using your formula it should have worked too. what was i doing wrong!! Do let me know if you find out. Thanks for all your help and patience. > That looks correct, what does the subform total text box > display? Is it different from what the main form text box [quoted text clipped - 88 lines] > >> >> >> form being displayed (they may or may not have the same > >> >> >> name). Using IsError can mask other problems as well as the specific case you are trying to deal with so it's painting with a broader brush than you should need. You might a result of 0 when something else goes wrong, but you will probably notice whatever else is not working so it's no great worry. OTOH, we have not been able to figure out why using the Recordset.RecordCount generates a #Name problem even though both ideas use the same names??? SignatureMarsh MVP [MS Access] >I found the solution to my problem in the same discussion group with the >subject #error solution given by ofer. [quoted text clipped - 101 lines] >> >> >> >> form being displayed (they may or may not have the same >> >> >> >> name). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.