Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Line Item Total (A2K SR1)

    Forgive the inane question, but I'm having a brain drain. I have a hidden text box on a subform that totals the value of all of the line items in the subform. I 'copy' the value of the hidden subform text box to a visible text box on the main form, giving the user a real-time running total of the value of the purchase order. When there are no line items to total, I want the main form text box to remain blank. However, the main form text box is displaying #Error when there are no line items.

    This works as desired on an existing form. However, I can't get it to work on a form I'm redesigning. I've searched everywhere and I can't find any property differences between the two forms.

    Any help would be appreciated. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Line Item Total (A2K SR1)

    I assume the control on the parent form has a formula something like "=[subformcontrol]![textbox]". If there are no records in the subform, then the problem is probably coming from the textbox on the subform. When I'm troubleshooting stuff like this, I find it easiest to turn on the visibility of the control being referenced until I can figure out what's going on. Do you have an Nz() function in the subform textbox formula? If not, try adding one and see if that helps solve the parent form problem.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Good morning, Charlotte.

    The parent form control has this formula: =[sbfLINEITEMS].Form!AgreeTotal. I don't have an Nz() function in the subform text box formula. The subform control has this formula: =Sum([TotalCost]). How would I use the Nz() function?

    This is frustrating because I'm using the same technique on another form and it works. Stuff like this makes my hair fall out at an even faster rate!

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    I have an application where I did the following:
    the totaling field in the subform is in the footer of the subform and contains as control source : = Sum([FieldToSum].
    In the mainform I have a control that repeat the summed textbox and contain as control source = [SubformControlName].[Form]![TotalTextbox]
    I think if your Total texbox is on the detail of the form and there is no record, there is also no Total textbox and this his what give the #Error.
    Try to put it in the footer of the subform
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    The totaling field in the subform is already in the footer of the subform. On the main form, I have a control that repeats the summed text box from the subform. It sounds like I'm already doing as Francois suggested and I'm still getting the error.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Is totalcost a calculated field ?
    When you open the subform directly, in form view or continuous view, did the total field already show the error ?
    Francois

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Francois,

    totalcost is a hidden text box that is populated after the unitcost field on the subform is updated. The values of all of the totalcost fields (1 for each line item) are then summed in the hidden AgreeTotal text box on the subform. The value of AgreeTotal is then 'copied' to the summing text box on the main form.

    Each record in the subform is associated with a particular record in the main form, so I am unable to show anything except a grand total of all of the line items, instead of a total for just a particular main form record.

    If you still have a copy of my DB, open frmLINEITEMS and scroll through the records to see how the technique is supposed to work. I'm trying to use that technique on a new form I've designed and I'm getting the #Error.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Sorry for the delay, but I have a little crash and had to reinstall Office and Norton Systemworks.
    I see nothing wrong in the form LINEITEMS.
    Can you send me the database with the form you have problems with?
    Francois

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Roy, I have received your database and have found the following.
    The difference between frmLINEITEMS and frmSUBINFO6, is that for the subform lineitems the AllowAdditions property is set to no in frmSUBINFO6, so that if it has no records, totalcost dont exist. In frmLINEITEMS, the AllowAddition is set to Yes, so you have always a record (the new record, if there is no other).
    To avoid the #error enter the following in the control source of LineItemTotal of frmSUBINFO6:
    =IIf([Forms].[frmsubinfo6].[sbflineitems].Form.RecordSet.RecordCount=0;"";[sbfLINEITEMS].Form!AgreeTotal)
    Francois

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Line Item Total (A2K SR1)

    Try =Nz([sbfLineItems].Form!AgreeTotal,0). In the subform, you would use it like this: =Sum(Nz([TotalCost],0))
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Francois' solution worked best. The only change: instead of semi-colons on each side of the "", you should use commas.

    As always, many thanks to everyone for their advice and guidance. Thank you.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Sorry for the semi-colons, I just copy the line from Access to the Lounge and forget to change them to commas. Here in Belgium with international version of Office, we use the comma for decimal separator and have to use semi-colons between the argument of a function.
    I'll try to not forget to change them the next time.
    Francois

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Item Total (A2K SR1)

    Francois, it was not a problem. Thanks to you, and everyone, for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •