R
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
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