I have a Main Form called "Item Number" and two sub forms, "Shipped jobs" and "back ordered jobs". Both subforms have a calculated control in their respective footers that sum the "quantity" of the item.
On the main form I want to add the quantities together in a control. Of course everything works great provided totals exist in both subforms.
If one of the subforms contains no data for the particular item, I get error# in the main form control. I have been playing with the NZ and IIFs and anything else I thought would work but nothing seems to make it work.
>I have a Main Form called "Item Number" and two sub forms, "Shipped >jobs" and "back ordered jobs". Both subforms have a calculated >control in their respective footers that sum the "quantity" of the >item.
>On the main form I want to add the quantities together in a control. >Of course everything works great provided totals exist in both >subforms.
>If one of the subforms contains no data for the particular item, I get >error# in the main form control. I have been playing with the NZ and >IIFs and anything else I thought would work but nothing seems to make >it work.
I renamed your main form and subforms in this example for clarity (using Hungarian Notation, a popular naming convention... i.e. "frm" prefix for main forms and "sbf" prefix for subforms...Also, no spaces in object or field names) ... I hope you don't mind..
Use an unbound textbox called "txtTotalItems" on "frmItemNumber" with something like this for its source code: =nz([sbfShippedJobs].[Form]![TotalShipped])+nz([sbfBackOrders].[Form]![Tota l BackOrdered])
You should also have something like this as a control source for the "totals" textboxes in each subform's footer: =Sum(Nz([QtyShipped],0)) =Sum(Nz([QtyBackOrdered],0))
That, I believe, should do it!
HTH, Don
John Hickey <jhickey...@revestne.com> wrote in message
> I have a Main Form called "Item Number" and two sub forms, "Shipped > jobs" and "back ordered jobs". Both subforms have a calculated > control in their respective footers that sum the "quantity" of the > item.
> On the main form I want to add the quantities together in a control. > Of course everything works great provided totals exist in both > subforms.
> If one of the subforms contains no data for the particular item, I get > error# in the main form control. I have been playing with the NZ and > IIFs and anything else I thought would work but nothing seems to make > it work.
Either try if Isnull(me!vblname) then docmd cancelevent endif (I suppose that you have) or safest method, default all numeric fields to 0 and make them requried so that the user cannot wipe out a value. Do this at table level) This method is very safe and you will not get nulls.
Tricia
-- CV and summary enclosed. Available 20/01/2000 . Rate £30 ph approx.
References from Alan Tilanus, Department Head Technical Support, Best International Group 0171 300 9000 Stuart Walker, Principal Engineer one2one 0181 214 2057 Ronnie Thompson, Design and Planning Compass Group UK PLC 0181 741 8900 Annette Cameron Project Manager, Computacenter 01908 206292 Pete Fox Manager Proposals Dept, ABB Industrial Systems, 01438 781934
Project managers can assess my technical competence directly as I always bring documentation(VBA code I have written and other documents) where the previous client has given permission to do so.
Regards
Tricia Belling
John Hickey <jhickey...@revestne.com> wrote in message
> I have a Main Form called "Item Number" and two sub forms, "Shipped > jobs" and "back ordered jobs". Both subforms have a calculated > control in their respective footers that sum the "quantity" of the > item.
> On the main form I want to add the quantities together in a control. > Of course everything works great provided totals exist in both > subforms.
> If one of the subforms contains no data for the particular item, I get > error# in the main form control. I have been playing with the NZ and > IIFs and anything else I thought would work but nothing seems to make > it work.
The code provided by Don Leverton will still give you the #error message in your field, when there is no data in the sub-form. You have to use SubForm.Nodata to check if the subform contains data. If not you can do whatever you like to do at that moment.
Don Leverton <nospam_lever...@telusplanet.net> schreef in berichtnieuws 5kOk4.96293$n3.1980...@news0.telusplanet.net...
> Hi John,
> I renamed your main form and subforms in this example for clarity (using > Hungarian Notation, a popular naming convention... i.e. "frm" prefix for > main forms and "sbf" prefix for subforms...Also, no spaces in object or > field names) ... > I hope you don't mind..
> Use an unbound textbox called "txtTotalItems" on "frmItemNumber" with > something like this for its source code:
=nz([sbfShippedJobs].[Form]![TotalShipped])+nz([sbfBackOrders].[Form]![Tota l
> You should also have something like this as a control source for the > "totals" textboxes in each subform's footer: > =Sum(Nz([QtyShipped],0)) > =Sum(Nz([QtyBackOrdered],0))
> > I have a Main Form called "Item Number" and two sub forms, "Shipped > > jobs" and "back ordered jobs". Both subforms have a calculated > > control in their respective footers that sum the "quantity" of the > > item.
> > On the main form I want to add the quantities together in a control. > > Of course everything works great provided totals exist in both > > subforms.
> > If one of the subforms contains no data for the particular item, I get > > error# in the main form control. I have been playing with the NZ and > > IIFs and anything else I thought would work but nothing seems to make > > it work.
What version of Access are you using? I can't seem to find a "NoData" for subform, and "NoData" doesn't fire in a sub-report in Access97. (Subreports require the use of ".HasData" instead.)
Incidentally, I *don't* get " #Error" 's on my main form / dual subform either....
This is copied and pasted from Help: =========================================================================== = ==== The NoData event occurs after Microsoft Access formats a report for printing that has no data (the report is bound to an empty recordset), but before the report is printed. You can use this event to cancel printing of a blank report.
Remarks
To run a macro or event procedure when this event occurs, set the OnNoData property to the name of the macro or to [Event Procedure]. If the report isn't bound to a table or query (by using the report's RecordSource property), the NoData event doesn't occur. This event occurs after the Format events for the report, but before the first Print event.
This event doesn't occur for subreports. If you want to hide controls on a subreport when the subreport has no data, so that the controls don't print in this case, you can use the HasData property in a macro or event procedure that runs when the Format or Print event occurs. The NoData event occurs before the first Page event for the report. =========================================================================== = ====
> The code provided by Don Leverton will still give you the #error message in > your field, when there is no data in the sub-form. You have to use > SubForm.Nodata to check if the subform contains data. If not you can do > whatever you like to do at that moment.
