By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,589 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Total problem in subform

P: n/a
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

Feb 2 '06 #1
Share this Question
Share on Google+
16 Replies


P: n/a
Ken,

Try making the box visible based on the following

If Not IsNull(Me.txtTotal) and Me.txtTotal>=45000 Then

Me.txtTotal.Visible = True

End If

Linda

"ken" <ke******@yahoo.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.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

Feb 2 '06 #2

P: n/a
ken
tried it and still not working

Feb 3 '06 #3

P: n/a

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

*** Sent via Developersdex http://www.developersdex.com ***
Feb 3 '06 #4

P: n/a
Please ignore my response.

Private Sub Form_Current()
Me!txtTotal.visible = 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 ***
Feb 3 '06 #5

P: n/a
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!lblLabel.Visible = Nz(DSum("Nz(SubTable!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 ***
Feb 3 '06 #6

P: n/a
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!lblLabel.Visible = Nz(DSum("Nz(SubTable!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 ***


Feb 5 '06 #7

P: n/a
Ken,

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

Private Sub Form_Current()
Me!subformB!lblLabel.Visible = Nz(DSum("Nz(SubTable!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 ***
Feb 5 '06 #8

P: n/a
ken
I ran the code just like stated above but
label is still visible even of total over 45000
any suggestions
Ken

Feb 10 '06 #9

P: n/a
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.Visible=Me!SubformB!Form.RecordsetClone .RecordCount >0 AND _
DSum("[Total]", Me!SubformB!Form.RecordSource)>=45000

End Sub

Wayne Gillespie
Gosford NSW Australia
Feb 10 '06 #10

P: n/a
I mailed a working example to <ke******@yahoo.com>

Please have a look at it.
*** Sent via Developersdex http://www.developersdex.com ***
Feb 10 '06 #11

P: n/a
On Fri, 10 Feb 2006 17:46:18 GMT, steve.minnaar <st*****@concise.com> wrote:
I mailed a working example to <ke******@yahoo.com>

Please have a look at it.


How?

Wayne Gillespie
Gosford NSW Australia
Feb 10 '06 #12

P: n/a
I saw:

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

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])

..
..
..
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 ***
Feb 11 '06 #13

P: n/a
On Sat, 11 Feb 2006 00:42:15 GMT, steve.minnaar <st*****@concise.com> wrote:
I saw:

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

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])

.
.
.
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"


You have emailed the db to the OP. (which is where it should go)
Wayne Gillespie
Gosford NSW Australia
Feb 11 '06 #14

P: n/a
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 <st*****@concise.com>
wrote:
I saw:

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

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])

.
.
.
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"


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 ***
Feb 11 '06 #15

P: n/a
On Sat, 11 Feb 2006 02:08:12 GMT, steve.minnaar <st*****@concise.com> wrote:
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?


No problems.
You did send the db to Ken's email address, but you replied to my post asking me
to look at the database you emailed to Ken. Hence my "How?"

OP stands for Original Poster.
Wayne Gillespie
Gosford NSW Australia
Feb 11 '06 #16

P: n/a
ken
Thanks Steve and Wayne
Really appreciate your help
Ken
steve.minnaar wrote:
I saw:

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

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])

.
.
.
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 ***


Feb 11 '06 #17

This discussion thread is closed

Replies have been disabled for this discussion.