Total problem in subform

[ken]

I have a formA and subformB
subformB is a continous form
with a txtTotal in form footer
=Sum([Total])
This works fine as long as there are
records in form
but if form is null I get Error
I would like to get arround this as I
Have a label to be Visible only
if txtTotal>= 45000
The way it is now label shows up visible
on form when it has no records
thank you for any suggestions

[Linda Burnside]

Ken,

Try making the box visible based on the following

If Not IsNull(Me.txtTo tal) and Me.txtTotal>=45 000 Then

Me.txtTotal.Vis ible = True

End If

Linda

"ken" <ke******@yahoo .com> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.com...

I have a formA and subformB
subformB is a continous form
with a txtTotal in form footer
=Sum([Total])
This works fine as long as there are
records in form
but if form is null I get Error
I would like to get arround this as I
Have a label to be Visible only
if txtTotal>= 45000
The way it is now label shows up visible
on form when it has no records
thank you for any suggestions

[ken]

tried it and still not working

[steve.minnaar]

Private Sub Form_Current()
Me!txtTotal.vis ible = Nz(Me!txtTotal, 0) >=45000
End Sub
Thick Quinker - I just never learn.

*** Sent via Developersdex http://www.developersdex.com ***

[steve.minnaar]

Please ignore my response.

Private Sub Form_Current()
Me!txtTotal.vis ible = Nz(Me!txtTotal, 0) >=45000
End Sub

It was intended for a different problem altogether. I don't know how it
ended up here.

Thick Quinker - I just never learn.

*** Sent via Developersdex http://www.developersdex.com ***

[steve.minnaar]

I have a formA and subformB
subformB is a continous form with a txtTotal in form footer
=Sum([Total])
This works fine as long as there are records in form
but if form is null I get Error I would like to get around this as I
have a label to be Visible only if txtTotal>= 45000
The way it is now label shows up visible on form when it has no records
thank you for any suggestions

I know of no event which occurs when subform totals have been
calculated. There is invariably an indeterminate delay before one can
use a subform total in further expressions which leads to unpredictable
results. One can use the main form's timer event to do such work but
I've found a better approach is to compute the total in the main form's
current event for this purpose.

Assumptions:

1) The column being summed is named Item
2) The label you want to show or hide is on the subform
3) The linking index in both forms is named IndexID
4) The RecordSource of the subform is named SubTable

Use the following in your main form, formA:

Private Sub Form_Current()
Me!subformB!lbl Label.Visible = Nz(DSum("Nz(Sub Table!Item, 0)",
"SubTable", "IndexID=" & Me![IndexID])) >= 45000
End Sub

Sorry about the earlier confusion. I've really got to take more time to
ponder the question before offering a solution.

Thick Quinker - I just never learn.

*** Sent via Developersdex http://www.developersdex.com ***

[ken]

Thank you for the response and advice
The only question i have is the formA and suformB are linked
with DateId as this is a schedule form
and formA is just dates and subformB is details for deliverys on
particular day
Thanks
Ken
steve.minnaar wrote:

I have a formA and subformB
subformB is a continous form with a txtTotal in form footer
=Sum([Total])
This works fine as long as there are records in form
but if form is null I get Error I would like to get around this as I
have a label to be Visible only if txtTotal>= 45000
The way it is now label shows up visible on form when it has no records
thank you for any suggestions

I know of no event which occurs when subform totals have been
calculated. There is invariably an indeterminate delay before one can
use a subform total in further expressions which leads to unpredictable
results. One can use the main form's timer event to do such work but
I've found a better approach is to compute the total in the main form's
current event for this purpose.

Assumptions:

1) The column being summed is named Item
2) The label you want to show or hide is on the subform
3) The linking index in both forms is named IndexID
4) The RecordSource of the subform is named SubTable

Use the following in your main form, formA:

Private Sub Form_Current()
Me!subformB!lbl Label.Visible = Nz(DSum("Nz(Sub Table!Item, 0)",
"SubTable", "IndexID=" & Me![IndexID])) >= 45000
End Sub

Sorry about the earlier confusion. I've really got to take more time to
ponder the question before offering a solution.

Thick Quinker - I just never learn.

*** Sent via Developersdex http://www.developersdex.com ***

[steve.minnaar]

Ken,

In the example (note that I'm now using DateID):

Private Sub Form_Current()
Me!subformB!lbl Label.Visible = Nz(DSum("Nz(Sub Table!Item, 0)",
"SubTable", "DateId=" & Me![DateId])) >= 45000
End Sub

"DateId=" & Me![DateId] is the criterion to identify the records which
are displayed in the subform.

Just as Access would use the "Link Child Fields" and "Link Master
Fields" properties to display a subset of records in the subform, you
need to mimic this in the DSum expression used in your main form.

By the way, I'm new to groups and might be unwittingly breaking a few
cardinal rules here. If I do please let me know.
In the extraction from your previous post I manually added the "> "
before each line. Is there an automatic way to do this?

Ken wrote:

Thank you for the response and advice
The only question i have is the formA and suformB are
linked with DateId as this is a schedule form
and formA is just dates and subformB is details for
deliverys on particular day
Thanks
Ken

*** Sent via Developersdex http://www.developersdex.com ***

[ken]

I ran the code just like stated above but
label is still visible even of total over 45000
any suggestions
Ken

[Wayne Gillespie]

On 1 Feb 2006 16:58:34 -0800, "ken" <ke******@yahoo .com> wrote:

I have a formA and subformB
subformB is a continous form
with a txtTotal in form footer
=Sum([Total])
This works fine as long as there are
records in form
but if form is null I get Error
I would like to get arround this as I
Have a label to be Visible only
if txtTotal>= 45000
The way it is now label shows up visible
on form when it has no records
thank you for any suggestions

You do not needtxtTotal in the subform.
Put something like this in the current event of formA.

Sub Form_Current()

Me.MyLable.Visi ble=Me!SubformB !Form.Recordset Clone.RecordCou nt >0 AND _
DSum("[Total]", Me!SubformB!For m.RecordSource) >=45000

End Sub

Wayne Gillespie
Gosford NSW Australia