subform question

[ryan.fitzpatrick3]

I have a subform where i have a list of components, the 2nd column
next it to it has percentage. I have a total box at the bottom where
I have total. when all the components are listed the total should be
100%, they have to manually enter this information in. This works
fine, but in the percentage textboxes, when I type 7 for 7% it comes
up as 700%, I would have to type in .07 to equal 7%. Is there away
where the number I type in could just be the %, instead of doing .07.
This would be easier for the people who are about to use the program.
thanks in advance.

Ryan

 

[Marshall Barton]

I have a subform where i have a list of components, the 2nd column
next it to it has percentage. I have a total box at the bottom where
I have total. when all the components are listed the total should be
100%, they have to manually enter this information in. This works
fine, but in the percentage textboxes, when I type 7 for 7% it comes
up as 700%, I would have to type in .07 to equal 7%. Is there away
where the number I type in could just be the %, instead of doing .07.
This would be easier for the people who are about to use the program.
thanks in advance.

You can use code in the percent text box's AfterUpdate event
procedure:

If Me.Text2 > 1 Then
Me.Text2 = Me.Text2 / 100
End If

 

[ryan.fitzpatrick3]

I put this in

Private Sub Percent_of_Composition_AfterUpdate()
If Me.PercofComp > 1 Then
Me.PercofComp = Me.PercofComp / 100
End If
End Sub

didn't work, still pops up as 700% when I type in 7

 

[ryan.fitzpatrick3]

I got it to work. thanks Mike.

I put this in

Private Sub Percent_of_Composition_AfterUpdate()
If Me.PercofComp > 1 Then
Me.PercofComp = Me.PercofComp / 100
End If
End Sub

didn't work, still pops up as 700% when I type in 7

You can use code in the percent text box's AfterUpdate event
procedure:

If Me.Text2 > 1 Then
Me.Text2 = Me.Text2 / 100
End If

 

[ryan.fitzpatrick3]

Mike,

I have a follow up question. The total textbox which adds up all the
percentages, I want it to give a msgbox stating it's too high of a
percentage if the total is greater than 100, is there away to have it
not save to the table unless it's equal to or less than 100. this is
what I was thinking on afterupdate

Private Sub tboxpercofcomptotal_AfterUpdate()
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage please correct percentages" &
Err.Number & " : " & Err.Description, vbCritical
End Sub

 

[Marshall Barton]

I have a follow up question. The total textbox which adds up all the
percentages, I want it to give a msgbox stating it's too high of a
percentage if the total is greater than 100, is there away to have it
not save to the table unless it's equal to or less than 100. this is
what I was thinking on afterupdate

Private Sub tboxpercofcomptotal_AfterUpdate()
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage please correct percentages" &
Err.Number & " : " & Err.Description, vbCritical
End Sub

I presume that the total is calculated using an expression
or ?? in which case the text box's update events do not
fire.

Depending on how you are doing the calculation, you can use
the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 1 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

 

[ryan.fitzpatrick3]

I have this in build expression

=Sum([Percent of Composition])

 

[Marshall Barton]

Does the BeforeUpdate event work in that situation?

I suspect that the expression may not complete its
calculation by the time the record save starts or the
unsaved record's percentage is not being included in the
calculation.

If that's what you are seeing, then the calculation will
have to be done another way.

How about explaining what problem you are having and I'll
see what I can come up with to deal with it.
--
Marsh
MVP [MS Access]

I have this in build expression

=Sum([Percent of Composition])

I presume that the total is calculated using an expression
or ?? in which case the text box's update events do not
fire.

Depending on how you are doing the calculation, you can use
the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 1 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

 

[ryan.fitzpatrick3]

Mike,

The beforeupdate doesn't work, I do not get a msgbox when the total
goes above 100. I have the vba code for the textbox that has the total
correct? The event doesn't even fire that would be, I guess, the
problem. To explain what i have, i have a subform within my form. It
has a list of items

Flour 60% <--------this textbox is tboxpercofcomp
eggs 25%
sugar 7%
vanilla 5%
salt 3%

total 100%

textbox that has total of 100% is called tboxpercofcomptotal, I have
this vba code for this textbox

Private Sub tboxpercofcomptotal_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

This is in the information that I know. Does this help?

Does the BeforeUpdate event work in that situation?

I suspect that the expression may not complete its
calculation by the time the record save starts or the
unsaved record's percentage is not being included in the
calculation.

If that's what you are seeing, then the calculation will
have to be done another way.

How about explaining what problem you are having and I'll
see what I can come up with to deal with it.
--
Marsh
MVP [MS Access]

I have this in build expression

=Sum([Percent of Composition])

(e-mail address removed) wrote:
I have a follow up question. The total textbox which adds up all the
percentages, I want it to give a msgbox stating it's too high of a
percentage if the total is greater than 100, is there away to have it
not save to the table unless it's equal to or less than 100. this is
what I was thinking on afterupdate
Private Sub tboxpercofcomptotal_AfterUpdate()
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage please correct percentages" &
Err.Number & " : " & Err.Description, vbCritical
End Sub
I presume that the total is calculated using an expression
or ?? in which case the text box's update events do not
fire.
Depending on how you are doing the calculation, you can use
the form's BeforeUpdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 1 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

 

[Marshall Barton]

That is the wrong BeforeUpdate event because. as I said
before, the text box events will not be used when its value
is set in VBA code or calculated in an expression.

Please try my suggestion and use the FORM's BeforeUpdate
event instead and report back with the results. It will
probably help us understand what's happening if you add a
message box to display the relevant values.

And, you need to think about the actual value in the text
box, not what it's formatting displays (100% is the value
1.0).

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Percent=" & [Percent of Composition] _
& " Total=" & tboxpercofcomptotal
If Me.tboxpercofcomptotal > 1.0 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub
--
Marsh
MVP [MS Access]

The beforeupdate doesn't work, I do not get a msgbox when the total
goes above 100. I have the vba code for the textbox that has the total
correct? The event doesn't even fire that would be, I guess, the
problem. To explain what i have, i have a subform within my form. It
has a list of items

Flour 60% <--------this textbox is tboxpercofcomp
eggs 25%
sugar 7%
vanilla 5%
salt 3%

total 100%

textbox that has total of 100% is called tboxpercofcomptotal, I have
this vba code for this textbox

Private Sub tboxpercofcomptotal_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

This is in the information that I know. Does this help?

Does the BeforeUpdate event work in that situation?

I suspect that the expression may not complete its
calculation by the time the record save starts or the
unsaved record's percentage is not being included in the
calculation.

If that's what you are seeing, then the calculation will
have to be done another way.

How about explaining what problem you are having and I'll
see what I can come up with to deal with it.

I have this in build expression

=Sum([Percent of Composition])

 

[ryan.fitzpatrick3]

I got an error of "method or data member not found"

I put code on form properties as instructed. I noticed when I added a
new item and when I went to type in data into the subform this
messagebox popped up, but when I go to an old record and make the
percentage over 100 nothing happened, only on the new items i put in.

That is the wrong BeforeUpdate event because. as I said
before, the text box events will not be used when its value
is set in VBA code or calculated in an expression.

Please try my suggestion and use the FORM's BeforeUpdate
event instead and report back with the results. It will
probably help us understand what's happening if you add a
message box to display the relevant values.

And, you need to think about the actual value in the text
box, not what it's formatting displays (100% is the value
1.0).

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Percent=" & [Percent of Composition] _
& " Total=" & tboxpercofcomptotal
If Me.tboxpercofcomptotal > 1.0 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub
--
Marsh
MVP [MS Access]

The beforeupdate doesn't work, I do not get a msgbox when the total
goes above 100. I have the vba code for the textbox that has the total
correct? The event doesn't even fire that would be, I guess, the
problem. To explain what i have, i have a subform within my form. It
has a list of items

Flour 60% <--------this textbox is tboxpercofcomp
eggs 25%
sugar 7%
vanilla 5%
salt 3%

total 100%

textbox that has total of 100% is called tboxpercofcomptotal, I have
this vba code for this textbox

Private Sub tboxpercofcomptotal_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

This is in the information that I know. Does this help?

Does the BeforeUpdate event work in that situation?
I suspect that the expression may not complete its
calculation by the time the record save starts or the
unsaved record's percentage is not being included in the
calculation.
If that's what you are seeing, then the calculation will
have to be done another way.
How about explaining what problem you are having and I'll
see what I can come up with to deal with it.
(e-mail address removed) wrote:
I have this in build expression
=Sum([Percent of Composition])

 

[Marshall Barton]

Which form did you do that in? It sounds like you did it in
the main form when the percentages and calculation are in
the subform.

It may just be that you are using the wrong words, but the
line of code must be in the event **procedure**. The event
**property** must contain [Event Procedure]
Note that properties can not contain code, VBA code can only
be used in a module.

In order to avoid at least some of these misunderstandings,
you should probably take a little time to learn a few of the
basics of using events to control form behavior.
--
Marsh
MVP [MS Access]

I got an error of "method or data member not found"

I put code on form properties as instructed. I noticed when I added a
new item and when I went to type in data into the subform this
messagebox popped up, but when I go to an old record and make the
percentage over 100 nothing happened, only on the new items i put in.

That is the wrong BeforeUpdate event because. as I said
before, the text box events will not be used when its value
is set in VBA code or calculated in an expression.

Please try my suggestion and use the FORM's BeforeUpdate
event instead and report back with the results. It will
probably help us understand what's happening if you add a
message box to display the relevant values.

And, you need to think about the actual value in the text
box, not what it's formatting displays (100% is the value
1.0).

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Percent=" & [Percent of Composition] _
& " Total=" & tboxpercofcomptotal
If Me.tboxpercofcomptotal > 1.0 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

The beforeupdate doesn't work, I do not get a msgbox when the total
goes above 100. I have the vba code for the textbox that has the total
correct? The event doesn't even fire that would be, I guess, the
problem. To explain what i have, i have a subform within my form. It
has a list of items

Flour 60% <--------this textbox is tboxpercofcomp
eggs 25%
sugar 7%
vanilla 5%
salt 3%

total 100%

textbox that has total of 100% is called tboxpercofcomptotal, I have
this vba code for this textbox

Private Sub tboxpercofcomptotal_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub

This is in the information that I know. Does this help?

On Mar 5, 7:44 am, Marshall Barton wrote:
Does the BeforeUpdate event work in that situation?

I suspect that the expression may not complete its
calculation by the time the record save starts or the
unsaved record's percentage is not being included in the
calculation.

If that's what you are seeing, then the calculation will
have to be done another way.

How about explaining what problem you are having and I'll
see what I can come up with to deal with it.

(e-mail address removed) wrote:
I have this in build expression

=Sum([Percent of Composition])

 

[ryan.fitzpatrick3]

I got it work, the msgbox comes up but after it states that the
percentage is to high, I can't change the percentage numbers back to
make it 100%. Instead of cancel is there away to do undo last action?
Thanks a million. I just started access 2 months ago. I'm learning by
reading these posts, access books and by asking questions to you the
experts. Thanks for you patience.

Ryan

Which form did you do that in? It sounds like you did it in
the main form when the percentages and calculation are in
the subform.

It may just be that you are using the wrong words, but the
line of code must be in the event **procedure**. The event
**property** must contain [Event Procedure]
Note that properties can not contain code, VBA code can only
be used in a module.

In order to avoid at least some of these misunderstandings,
you should probably take a little time to learn a few of the
basics of using events to control form behavior.
--
Marsh
MVP [MS Access]

I got an error of "method or data member not found"

I put code on form properties as instructed. I noticed when I added a
new item and when I went to type in data into the subform this
messagebox popped up, but when I go to an old record and make the
percentage over 100 nothing happened, only on the new items i put in.

That is the wrong BeforeUpdate event because. as I said
before, the text box events will not be used when its value
is set in VBA code or calculated in an expression.
Please try my suggestion and use the FORM's BeforeUpdate
event instead and report back with the results. It will
probably help us understand what's happening if you add a
message box to display the relevant values.
And, you need to think about the actual value in the text
box, not what it's formatting displays (100% is the value
1.0).
Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Percent=" & [Percent of Composition] _
& " Total=" & tboxpercofcomptotal
If Me.tboxpercofcomptotal > 1.0 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub
(e-mail address removed) wrote:
The beforeupdate doesn't work, I do not get a msgbox when the total
goes above 100. I have the vba code for the textbox that has the total
correct? The event doesn't even fire that would be, I guess, the
problem. To explain what i have, i have a subform within my form. It
has a list of items
Flour 60% <--------this textbox is tboxpercofcomp
eggs 25%
sugar 7%
vanilla 5%
salt 3%
total 100%
textbox that has total of 100% is called tboxpercofcomptotal, I have
this vba code for this textbox
Private Sub tboxpercofcomptotal_BeforeUpdate(Cancel As Integer)
If Me.tboxpercofcomptotal > 100 Then
MsgBox "To high of percentage. Please correct"
Cancel = True
End If
End Sub
This is in the information that I know. Does this help?
On Mar 5, 7:44 am, Marshall Barton wrote:
Does the BeforeUpdate event work in that situation?
I suspect that the expression may not complete its
calculation by the time the record save starts or the
unsaved record's percentage is not being included in the
calculation.
If that's what you are seeing, then the calculation will
have to be done another way.
How about explaining what problem you are having and I'll
see what I can come up with to deal with it.
(e-mail address removed) wrote:
I have this in build expression
=Sum([Percent of Composition])

 

[Marshall Barton]

You can try adding the line:
Me.percenttextbox.Undo
to undo the change in the percent text box,
or, the line:
Me.Undo
to undo all the edits to the current record.

However, I don't think it will help. The issue is that the
calculation is done asynchronously from the form updates and
the code we're using. That means that the total percent
**might** not be recalculated by the next time the code
checks it.

You've come a long way in just a couple of months, but this
is a very tricky issue that can only be resloved completely
by doing the sum in a VBA procedure.

If neither Undo does the job and if the values in the
debugging msgbox demonstrates the above issue, post back and
I'll put together an alternate approach.

 

[ryan.fitzpatrick3]

You're correct, the textbox that does the total doesn't add up before
the code gets executed. If I type in 100 for 100%
this works fine, but if I change that to 102 the error pops up and
when I retype in 100 to make it correct, it still recognizes that as
over 100% because the total textbox hasn't recalculated before the
msgbox stating that it's too high goes off. Make sense?

What if I put this before the code you gave me?

If Me.tboxpercofcomptotal >= 100 Then
exit sub

I guess the issue is that the total textbox needs to total up before
the code gets executed, right? if >= 100 then it exits sub as ok, but
if over then the msgbox will come up.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.tboxpercofcomptotal >= 100 Then
Exit Sub
End If

MsgBox "Percent=" & [Percent of Composition] _
& " Total=" &
tboxpercofcomptotal

If Me.tboxpercofcomptotal > 1# Then
MsgBox "To high of percentage. Please correct"
Cancel = True
Me.Undo
End If
End Sub



Thanks,

Ryan

You can try adding the line:
Me.percenttextbox.Undo
to undo the change in the percent text box,
or, the line:
Me.Undo
to undo all the edits to the current record.

However, I don't think it will help. The issue is that the
calculation is done asynchronously from the form updates and
the code we're using. That means that the total percent
**might** not be recalculated by the next time the code
checks it.

You've come a long way in just a couple of months, but this
is a very tricky issue that can only be resloved completely
by doing the sum in a VBA procedure.

If neither Undo does the job and if the values in the
debugging msgbox demonstrates the above issue, post back and
I'll put together an alternate approach.
--
Marsh
MVP [MS Access]

I got it work, the msgbox comes up but after it states that the
percentage is to high, I can't change the percentage numbers back to
make it 100%. Instead of cancel is there away to do undo last action?
Thanks a million. I just started access 2 months ago. I'm learning by
reading these posts, access books and by asking questions to you the
experts. Thanks for you patience.

 

[Marshall Barton]

No, that won't help.

What else did you change so that you can use 100 instead of
1.0? What is the data type and Size of the [Percent of
Composition] field in the table? This is important because
it will affect the code below.

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

Then the Form's Before Update event would be:

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

Once the total is calculated in the text box's AfterUpdate
event, the check for the total > 100 could also be done for
a more immediate check.
--
Marsh
MVP [MS Access]

You're correct, the textbox that does the total doesn't add up before
the code gets executed. If I type in 100 for 100%
this works fine, but if I change that to 102 the error pops up and
when I retype in 100 to make it correct, it still recognizes that as
over 100% because the total textbox hasn't recalculated before the
msgbox stating that it's too high goes off. Make sense?

What if I put this before the code you gave me?

If Me.tboxpercofcomptotal >= 100 Then
exit sub

I guess the issue is that the total textbox needs to total up before
the code gets executed, right? []

You can try adding the line:
Me.percenttextbox.Undo
to undo the change in the percent text box,
or, the line:
Me.Undo
to undo all the edits to the current record.

However, I don't think it will help. The issue is that the
calculation is done asynchronously from the form updates and
the code we're using. That means that the total percent
**might** not be recalculated by the next time the code
checks it.

You've come a long way in just a couple of months, but this
is a very tricky issue that can only be resloved completely
by doing the sum in a VBA procedure.

If neither Undo does the job and if the values in the
debugging msgbox demonstrates the above issue, post back and
I'll put together an alternate approach.

 

[ryan.fitzpatrick3]

I didn't change anything, I just added this

If Me.tboxpercofcomptotal >= 100 Then
Exit Sub

to see if it'll work.

In the table field for percent of composition I have double as field
type and percent, the data type is listed as a number.

It said that you can't assign a number to this:

Me.tboxpercofcomptotal = TotalPercent

Ryan

No, that won't help.

What else did you change so that you can use 100 instead of
1.0? What is the data type and Size of the [Percent of
Composition] field in the table? This is important because
it will affect the code below.

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

Then the Form's Before Update event would be:

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

Once the total is calculated in the text box's AfterUpdate
event, the check for the total > 100 could also be done for
a more immediate check.
--
Marsh
MVP [MS Access]

You're correct, the textbox that does the total doesn't add up before
the code gets executed. If I type in 100 for 100%
this works fine, but if I change that to 102 the error pops up and
when I retype in 100 to make it correct, it still recognizes that as
over 100% because the total textbox hasn't recalculated before the
msgbox stating that it's too high goes off. Make sense?

What if I put this before the code you gave me?

If Me.tboxpercofcomptotal >= 100 Then
exit sub

I guess the issue is that the total textbox needs to total up before
the code gets executed, right? []

You can try adding the line:
Me.percenttextbox.Undo
to undo the change in the percent text box,
or, the line:
Me.Undo
to undo all the edits to the current record.
However, I don't think it will help. The issue is that the
calculation is done asynchronously from the form updates and
the code we're using. That means that the total percent
**might** not be recalculated by the next time the code
checks it.
You've come a long way in just a couple of months, but this
is a very tricky issue that can only be resloved completely
by doing the sum in a VBA procedure.
If neither Undo does the job and if the values in the
debugging msgbox demonstrates the above issue, post back and
I'll put together an alternate approach.
(e-mail address removed) wrote:
I got it work, the msgbox comes up but after it states that the
percentage is to high, I can't change the percentage numbers back to
make it 100%. Instead of cancel is there away to do undo last action?

 

[Marshall Barton]

Since we are using code to calculate the total, you must
remove the =Sum expression in the text box.

You did not explain what you did to make the percents add up
to 100 instead of 1.0? I strongly suspect that you are
still making the mistake of thinking in terms of percents
instead of the numeric values. Please post a copy of the
numbers in the debugging msgbox to confirm or refute this.

When I suggested the alternative approach, you have to try
all of it. Nothing will make sense if you only choose to do
parts of it. Since I can not see what you actually used
when you run a test, you can help minimize the number of
guesses I have to make by posting a Copy/Paste of the code
at the time you ran the test.
--
Marsh
MVP [MS Access]

I didn't change anything, I just added this

If Me.tboxpercofcomptotal >= 100 Then
Exit Sub

to see if it'll work.

In the table field for percent of composition I have double as field
type and percent, the data type is listed as a number.

It said that you can't assign a number to this:

Me.tboxpercofcomptotal = TotalPercent

No, that won't help.

What else did you change so that you can use 100 instead of
1.0? What is the data type and Size of the [Percent of
Composition] field in the table? This is important because
it will affect the code below.

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

Then the Form's Before Update event would be:

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

Once the total is calculated in the text box's AfterUpdate
event, the check for the total > 100 could also be done for
a more immediate check.
--
Marsh
MVP [MS Access]

You're correct, the textbox that does the total doesn't add up before
the code gets executed. If I type in 100 for 100%
this works fine, but if I change that to 102 the error pops up and
when I retype in 100 to make it correct, it still recognizes that as
over 100% because the total textbox hasn't recalculated before the
msgbox stating that it's too high goes off. Make sense?

What if I put this before the code you gave me?

If Me.tboxpercofcomptotal >= 100 Then
exit sub

I guess the issue is that the total textbox needs to total up before
the code gets executed, right? []

You can try adding the line:
Me.percenttextbox.Undo
to undo the change in the percent text box,
or, the line:
Me.Undo
to undo all the edits to the current record.

However, I don't think it will help. The issue is that the
calculation is done asynchronously from the form updates and
the code we're using. That means that the total percent
**might** not be recalculated by the next time the code
checks it.

You've come a long way in just a couple of months, but this
is a very tricky issue that can only be resloved completely
by doing the sum in a VBA procedure.

If neither Undo does the job and if the values in the
debugging msgbox demonstrates the above issue, post back and
I'll put together an alternate approach.

(e-mail address removed) wrote:
I got it work, the msgbox comes up but after it states that the
percentage is to high, I can't change the percentage numbers back to
make it 100%. Instead of cancel is there away to do undo last action?

 

[ryan.fitzpatrick3]

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?

Sorry about not mentioning that I make the total equal 100% and not
1.0, earlier I mentioned how I wanted the user to input 7 for 7%
instead of .07 to equal 7%, this made it easier for the user to input
this value. I didn't know if complicated this that much.

I changed the format to numeric and not percentage as requested. If
you'd like I can copy the 2 tables and form onto a blank database and
email it to you, you then wouldn't have to deal with my lack of
experience.

ryan

Since we are using code to calculate the total, you must
remove the =Sum expression in the text box.

You did not explain what you did to make the percents add up
to 100 instead of 1.0? I strongly suspect that you are
still making the mistake of thinking in terms of percents
instead of the numeric values. Please post a copy of the
numbers in the debugging msgbox to confirm or refute this.

When I suggested the alternative approach, you have to try
all of it. Nothing will make sense if you only choose to do
parts of it. Since I can not see what you actually used
when you run a test, you can help minimize the number of
guesses I have to make by posting a Copy/Paste of the code
at the time you ran the test.
--
Marsh
MVP [MS Access]

I didn't change anything, I just added this

If Me.tboxpercofcomptotal >= 100 Then
Exit Sub

to see if it'll work.

In the table field for percent of composition I have double as field
type and percent, the data type is listed as a number.

It said that you can't assign a number to this:

Me.tboxpercofcomptotal = TotalPercent

No, that won't help.
What else did you change so that you can use 100 instead of
1.0? What is the data type and Size of the [Percent of
Composition] field in the table? This is important because
it will affect the code below.
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
Then the Form's Before Update event would be:
If Me.tboxpercofcomptotal > 100 Then
MsgBox "Too high of percentage. Please correct"
Cancel = True
End If
Once the total is calculated in the text box's AfterUpdate
event, the check for the total > 100 could also be done for
a more immediate check.
--
Marsh
MVP [MS Access]
(e-mail address removed) wrote:
You're correct, the textbox that does the total doesn't add up before
the code gets executed. If I type in 100 for 100%
this works fine, but if I change that to 102 the error pops up and
when I retype in 100 to make it correct, it still recognizes that as
over 100% because the total textbox hasn't recalculated before the
msgbox stating that it's too high goes off. Make sense?
What if I put this before the code you gave me?
If Me.tboxpercofcomptotal >= 100 Then
exit sub
I guess the issue is that the total textbox needs to total up before
the code gets executed, right?
[]
You can try adding the line:
Me.percenttextbox.Undo
to undo the change in the percent text box,
or, the line:
Me.Undo
to undo all the edits to the current record.
However, I don't think it will help. The issue is that the
calculation is done asynchronously from the form updates and
the code we're using. That means that the total percent
**might** not be recalculated by the next time the code
checks it.
You've come a long way in just a couple of months, but this
is a very tricky issue that can only be resloved completely
by doing the sum in a VBA procedure.
If neither Undo does the job and if the values in the
debugging msgbox demonstrates the above issue, post back and
I'll put together an alternate approach.
(e-mail address removed) wrote:
I got it work, the msgbox comes up but after it states that the
percentage is to high, I can't change the percentage numbers back to
make it 100%. Instead of cancel is there away to do undo last action?

 

[Marshall Barton]

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