subform question

[ryan.fitzpatrick3]

This is a great learning experience for me, on being specific as
possible, for that I appoligize. I'll rephrase my earlier comment.

The code you gave me

Dim TotalPercent As Long
TotalPercent = 0
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0)
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent

Then the Form's Before Update event would be:

If Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
Cancel = True
me.undo
End If

I have the code above in the subform before update event and the top
code in the tboxpercofcomp textbox, this is the textbox for the manual
entry of the percentage. So I have a lets say an item apples and next
to it the tboxpercofcomp textbox that has the 7% and the subform
creates as many new entries depending on how many components there
are, so there could be 10 items listed in the subform with different
percentages that should equal to 100% since it makes up the entirety
of the item. At the bottom of the subform is a textbox that should sum
of the total percentages to equal 100, so item 1 is 20%, item 2 is
50%, and item 3 is 30% the textbox tboxpercofcomptotal would have 100
since it adds the percentages up.

The code listed above works great when I'm in the subform and when I
input a percentage that is higher than 1.0 (100%) the messagebox comes
up and therefore I would have to go back and change the input so it's
equal to or under 1.0 (100%) Now this works in the tboxpercofcomp
textbox perfectly, but the textbox that totals the sum of the
percentages up at the bottom i got rid of =sum, because you stated not
to use that. The only thing is how can I add the total up where it
doesn't interfere with the before update event on the subform? The
only code I have is the one you gave me above and I added me.undo as
you can see.

ryan

You are using "it" in your explanation too many times. I
think the "it" you are refering to is the percent text box,
because you later ask how to get the total to display.
However I have no idea what code you used to get "it" to
work.

I posted the alternate way of calculating the total before
(see below), but I still don't know if you are using that
code, using just part of it, or ignored all of it.

If you have not tried the code I posted, please try it. If
you did try it, what about it did did not meet your needs?
If you have a further question, please post a Copy/Paste of
the code that you actually you used.
--
Marsh
MVP [MS Access]

I got it to work, i took out the =sum out of the percofcomptotal
textbox where it sums up all the percentages. When I type in a number
under 100% it works and when I type in a number over 100% the message
box comes up so I have to correct it, and when I correct it, it works
great. Now that this works how do I sum up the percofcomptotal textbox
to reflect grand total percentage?

On Mar 6, 12:07 pm, Marshall Barton wrote:
Here is a completely different way to calculate the sum that
allows the code to check the total. Put this code in the
percent text box's AfterUpdate event:
Dim TotalPercent As Long
TotalPercent = 0
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0)
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent

 

[Marshall Barton]

That's much better, but ...

When you post code, never retype it (it looks like you at
least retyped the me.undo line and maybe more). Retyping
can easily introduce typos that would be a waste of time
discussing. Also, use Copy/Paste on all the relavent code,
not just pieces of procedures. The Sub lines tells the
reader what event the procedure is attached to so you don't
need to use words to describe that.

You also need to explain in detail what is wrong in the
result.
"The only thing is how can I add the total up
where it doesn't interfere with the before
update event on the subform?"
only tells me that you don't like the result. I need to
know exactly what the values are in both text boxes (this is
why I wanted the debugging msgbox:
MsgBox "Percent=" & [Percent of Composition] _
& " Total=" & tboxpercofcomptotal
in the form's BeforeUpdate event). In this case, it would
also help if you could provide the percent values on the
other records so I can double check the total. For example.
if you enter 50 on the first record and 60 in the second, I
would hope to see the msgbox display:
Percent=60 Total=110
If it displays something else, then maybe I could deduce
what's going on and come up with a way to correct the code.

After thinking about how the result could be wrong, I
realized that the AfterUpdate code is using only saved
values when the new percent text box value has not been
saved yet. I still don't know what you did about the 100 vs
1.0 issue so I think there may still be a discrepency there
too. Let's try replacing the code with:

Sub tboxpercofcomp_AfterUpdate()
Dim TotalPercent As Double
TotalPercent = 0.0
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If .Boomark = Me.Bookmark
TotalPercent = TotalPercent + Nz(tboxpercofcomp, 0.0)
Else
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0.0)
End If
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent
End Sub

Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Percent=" & tboxpercofcomp _
& " Total=" & tboxpercofcomptotalIf
Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
Cancel = True
Me.Undo
End If
End Sub
--
Marsh
MVP [MS Access]

This is a great learning experience for me, on being specific as
possible, for that I appoligize. I'll rephrase my earlier comment.

The code you gave me

Dim TotalPercent As Long
TotalPercent = 0
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0)
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent

Then the Form's Before Update event would be:

If Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
Cancel = True
me.undo
End If

I have the code above in the subform before update event and the top
code in the tboxpercofcomp textbox, this is the textbox for the manual
entry of the percentage. So I have a lets say an item apples and next
to it the tboxpercofcomp textbox that has the 7% and the subform
creates as many new entries depending on how many components there
are, so there could be 10 items listed in the subform with different
percentages that should equal to 100% since it makes up the entirety
of the item. At the bottom of the subform is a textbox that should sum
of the total percentages to equal 100, so item 1 is 20%, item 2 is
50%, and item 3 is 30% the textbox tboxpercofcomptotal would have 100
since it adds the percentages up.

The code listed above works great when I'm in the subform and when I
input a percentage that is higher than 1.0 (100%) the messagebox comes
up and therefore I would have to go back and change the input so it's
equal to or under 1.0 (100%) Now this works in the tboxpercofcomp
textbox perfectly, but the textbox that totals the sum of the
percentages up at the bottom i got rid of =sum, because you stated not
to use that. The only thing is how can I add the total up where it
doesn't interfere with the before update event on the subform? The
only code I have is the one you gave me above and I added me.undo as
you can see.

 

[ryan.fitzpatrick3]

Thanks Marsh, quick question, I got an error on your code on

If .Boomark = Me.Bookmark

I'd like to assume it's if. bookmark = me.bookmark ?

That's much better, but ...

When you post code, never retype it (it looks like you at
least retyped the me.undo line and maybe more). Retyping
can easily introduce typos that would be a waste of time
discussing. Also, use Copy/Paste on all the relavent code,
not just pieces of procedures. The Sub lines tells the
reader what event the procedure is attached to so you don't
need to use words to describe that.

You also need to explain in detail what is wrong in the
result.
"The only thing is how can I add the total up
where it doesn't interfere with the before
update event on the subform?"
only tells me that you don't like the result. I need to
know exactly what the values are in both text boxes (this is
why I wanted the debugging msgbox:
MsgBox "Percent=" & [Percent of Composition] _
& " Total=" & tboxpercofcomptotal
in the form's BeforeUpdate event). In this case, it would
also help if you could provide the percent values on the
other records so I can double check the total. For example.
if you enter 50 on the first record and 60 in the second, I
would hope to see the msgbox display:
Percent=60 Total=110
If it displays something else, then maybe I could deduce
what's going on and come up with a way to correct the code.

After thinking about how the result could be wrong, I
realized that the AfterUpdate code is using only saved
values when the new percent text box value has not been
saved yet. I still don't know what you did about the 100 vs
1.0 issue so I think there may still be a discrepency there
too. Let's try replacing the code with:

Sub tboxpercofcomp_AfterUpdate()
Dim TotalPercent As Double
TotalPercent = 0.0
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If .Boomark = Me.Bookmark
TotalPercent = TotalPercent + Nz(tboxpercofcomp, 0.0)
Else
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0.0)
End If
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent
End Sub

Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Percent=" & tboxpercofcomp _
& " Total=" & tboxpercofcomptotalIf
Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
Cancel = True
Me.Undo
End If
End Sub
--
Marsh
MVP [MS Access]

This is a great learning experience for me, on being specific as
possible, for that I appoligize. I'll rephrase my earlier comment.

The code you gave me

Dim TotalPercent As Long
TotalPercent = 0
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0)
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent

Then the Form's Before Update event would be:

If Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
Cancel = True
me.undo
End If

I have the code above in the subform before update event and the top
code in the tboxpercofcomp textbox, this is the textbox for the manual
entry of the percentage. So I have a lets say an item apples and next
to it the tboxpercofcomp textbox that has the 7% and the subform
creates as many new entries depending on how many components there
are, so there could be 10 items listed in the subform with different
percentages that should equal to 100% since it makes up the entirety
of the item. At the bottom of the subform is a textbox that should sum
of the total percentages to equal 100, so item 1 is 20%, item 2 is
50%, and item 3 is 30% the textbox tboxpercofcomptotal would have 100
since it adds the percentages up.

The code listed above works great when I'm in the subform and when I
input a percentage that is higher than 1.0 (100%) the messagebox comes
up and therefore I would have to go back and change the input so it's
equal to or under 1.0 (100%) Now this works in the tboxpercofcomp
textbox perfectly, but the textbox that totals the sum of the
percentages up at the bottom i got rid of =sum, because you stated not
to use that. The only thing is how can I add the total up where it
doesn't interfere with the before update event on the subform? The
only code I have is the one you gave me above and I added me.undo as
you can see.

 

[ryan.fitzpatrick3]

Sorry, it was your code. Here is complete code.

Option Compare Database


Private Sub Form_beforeUpdate(cancel As Integer)

MsgBox "Percent=" & tboxPercofComp _
& " Total=" & tboxpercofcomptotal
If Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
cancel = True
Me.Undo
End If
End If
End Sub


Private Sub tboxpercofcomp_AfterUpdate()
Dim TotalPercent As Double
TotalPercent = 0#
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If .Boomark = Me.Bookmark
TotalPercent = TotalPercent +
Nz(tboxPercofComp, 0#)
Else
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0#)
End If
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent
End Sub

 

[Marshall Barton]

Thanks Marsh, quick question, I got an error on your code on

If .Boomark = Me.Bookmark

I'd like to assume it's if. bookmark = me.bookmark ?

There may be something wrong in my code, but it is not a
simple syntax error on that line. That should be as I wrote
it, assuming the With statement is also as I wrote it.

Again, you need to post the code and the error message so I
can check the surrounding code with the error as an
important clue.

 

[Marshall Barton]

Sorry, it was your code. Here is complete code.

Private Sub tboxpercofcomp_AfterUpdate()
Dim TotalPercent As Double
TotalPercent = 0#
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If .Boomark = Me.Bookmark
TotalPercent = TotalPercent +
Nz(tboxPercofComp, 0#)

Sheesh, it's starting to get to me. I really mangled that
line, just not in the way you thought. It should have been:

If .Bookmark = Me.Bookmark Then

 

[ryan.fitzpatrick3]

do I need a then after thi

If .Boomark = Me.Bookmark "THEN"

Just wondering.

 

[Marshall Barton]

do I need a then after thi

If .Boomark = Me.Bookmark "THEN"

Just wondering.

If .Bookmark = Me.Bookmark Then

 

[ryan.fitzpatrick3]

It now says object doesn't support this property or method.

Private Sub Form_beforeUpdate(Cancel As Integer)

MsgBox "Percent=" & tboxPercofComp _
& " Total=" & tboxpercofcomptotal
If Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
Cancel = True
Me.Undo

End If
End Sub


Private Sub tboxpercofcomp_AfterUpdate()
Dim TotalPercent As Double
TotalPercent = 0#
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If .Boomark = Me.Bookmark Then
TotalPercent = TotalPercent +
Nz(tboxPercofComp, 0#)
Else
TotalPercent = TotalPercent + Nz(![Percent of
Composition], 0#)
End If
.MoveNext
Loop
End With
Me.tboxpercofcomptotal = TotalPercent
End Sub

 

[ryan.fitzpatrick3]

Thanks Marsh for all your help, i figured out what I wanted thank you.
I'm done with this issue.