I know its been asked b4, but help!
John Hickey
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.
Can anyone help?
Mike Reid
Can you post a extract/snippet of the code/function your using so
people can see and advise.
Don Leverton
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]![Total
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
news:389337dd...@NEWS.EROLS.COM...
pop3.btclick.com
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
Jan van Balkom
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.
Kind regards,
Jan.van...@BRIGHT-Software.nl
Don Leverton <nospam_...@telusplanet.net> schreef in berichtnieuws
5kOk4.96293$n3.19...@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]![Total
> 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
>
Don Leverton
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.
============================================================================
====
Jan van Balkom <Jan.van...@BRIGHT-Software.nl> wrote in message
news:8725dh$p78$1...@porthos.nl.uu.net...
> 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.
>
> Kind regards,
>
> Jan.van...@BRIGHT-Software.nl
<Previous threads snipped> DL