Total problem in subform

**Linda Burnside** · Feb 2 '06, 02:45 AM

Re: Total problem in subform

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" <ken_d128@yahoo .com> wrote in message
news:1138841914 .780284.78070@g 44g2000cwa.goog legroups.com...[color=blue]
>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
>[/color]

**ken** · Feb 3 '06, 01:15 AM

Re: Total problem in subform

tried it and still not working

**steve.minnaar** · Feb 3 '06, 06:15 AM

Re: Total problem in subform

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** · Feb 3 '06, 08:35 AM

Re: Total problem in subform

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** · Feb 3 '06, 10:35 PM

Re: Total problem in subform

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** · Feb 5 '06, 04:35 PM

Re: Total problem in subform

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:[color=blue]
> 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 ***[/color]

**steve.minnaar** · Feb 5 '06, 06:45 PM

Re: Total problem in subform

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:[color=blue]
> 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[/color]

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

**ken** · Feb 10 '06, 12:45 AM

Re: Total problem in subform

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

**Wayne Gillespie** · Feb 10 '06, 01:57 AM

Re: Total problem in subform

On 1 Feb 2006 16:58:34 -0800, "ken" <ken_d128@yahoo .com> wrote:
[color=blue]
>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[/color]

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

**steve.minnaar** · Feb 10 '06, 05:55 PM

Re: Total problem in subform

I mailed a working example to <ken_d128@yahoo .com>

Please have a look at it.

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

**Wayne Gillespie** · Feb 10 '06, 10:55 PM

Re: Total problem in subform

On Fri, 10 Feb 2006 17:46:18 GMT, steve.minnaar <steve.m@concis e.com> wrote:
[color=blue]
>I mailed a working example to <ken_d128@yahoo .com>
>
>Please have a look at it.
>[/color]

How?

Wayne Gillespie
Gosford NSW Australia

**steve.minnaar** · Feb 11 '06, 12:55 AM

Re: Total problem in subform

I saw:

From: Wayne Gillespie
Date Posted: 2/9/2006 6:27:00 PM

On 1 Feb 2006 16:58:34 -0800, "ken" <ken_d128@yahoo .com> wrote:
[color=blue]
>I have a formA and subformB
>subformB is a continous form
>with a txtTotal in form footer
>=Sum([Total])[/color]
..
..
..
So I mailed the solution to the address above.

If that will not work then give me an address like:
"ken at mydomain dot extension" and I'll mail it.

My email address is "steve at concisedata dot co dot za"

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

**Wayne Gillespie** · Feb 11 '06, 01:15 AM

Re: Total problem in subform

On Sat, 11 Feb 2006 00:42:15 GMT, steve.minnaar <steve.m@concis e.com> wrote:
[color=blue]
>I saw:
>
>From: Wayne Gillespie
>Date Posted: 2/9/2006 6:27:00 PM
>
>On 1 Feb 2006 16:58:34 -0800, "ken" <ken_d128@yahoo .com> wrote:
>[color=green]
>>I have a formA and subformB
>>subformB is a continous form
>>with a txtTotal in form footer
>>=Sum([Total])[/color]
>.
>.
>.
>So I mailed the solution to the address above.
>
>If that will not work then give me an address like:
>"ken at mydomain dot extension" and I'll mail it.
>
>My email address is "steve at concisedata dot co dot za"
>[/color]

You have emailed the db to the OP. (which is where it should go)

Wayne Gillespie
Gosford NSW Australia

**steve.minnaar** · Feb 11 '06, 02:15 AM

Re: Total problem in subform

Plea in General: I am new to news groups and am not familiar with the
techniques and terms used here. I've looked at various guidelines but am
still not much wiser.

This I do know:
There is a user out there called Ken with a problem.
I have solved the problem but need to send a complete mdb to demonstrate
this.
I may not post attachments to the group (even though I don't know how
to)
I do want him to get the solution.

This need to know about groups:
What OP stands for.
Who can see the mail sent to OP.
How I can ensure that Ken received the solution.
How I can make the solution available to any other interested user?
If an email client is used to communicate with the group how do I do it?

Is there a plain document out there which explains all of this?

From: Wayne Gillespie

On Sat, 11 Feb 2006 00:42:15 GMT, steve.minnaar <steve.m@concis e.com>
wrote:
[color=blue]
>I saw:
>
>From: Wayne Gillespie
>Date Posted: 2/9/2006 6:27:00 PM
>
>On 1 Feb 2006 16:58:34 -0800, "ken" <ken_d128@yahoo .com> wrote:
>[color=green]
>>I have a formA and subformB
>>subformB is a continous form
>>with a txtTotal in form footer
>>=Sum([Total])[/color]
>.
>.
>.
>So I mailed the solution to the address above.
>
>If that will not work then give me an address like:
>"ken at mydomain dot extension" and I'll mail it.
>
>My email address is "steve at concisedata dot co dot za"
>[/color]

You have emailed the db to the OP. (which is where it should go)

Wayne Gillespie
Gosford NSW Australia

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

Total problem in subform

Total problem in subform

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment