Totaling subform records

  • Thread starter Thread starter szag via AccessMonster.com
  • Start date Start date
S

szag via AccessMonster.com

I have a subform that is an allocation (by cost center) of costs by percent.
The user is suppose to put in the percents of different cost centers so they
add up to 100% - I want to be able to send the user a message (and not allow
the record to be saved) unless the percent of all the records add up to 100%.
So if he/she puts in two entries and it adds up to 80% and they try to go to
another record in the main form it won't allow it. Any ideas?

Zag
 
Acces saves each record individually. If you block the saving of the record
until they all add up to 100%, the first record would have to *be* 100%
before the user could save the record. All remaining records would then have
to be 0%. So, although your strategy is understandable, it is logically
flawed.

An alternative might be to use the BeforeInsert event procedure to assign
the remaining percent to this field. For the first record, it would then
assign 100%, but you can change it to (say) 40%. Then as soon as you start
entering the next row, it would assign 60%, which you again reduce if you
need a 3rd row. This approach a) helps the user understand how much is left,
and b) helps reduce the chance that that the user gets the last entry wrong.

Additionally, you could refuse to print the results if the records don't add
up yet, indicating that the records are "incomplete" or "overblown."
 
By the time you are in the subform then the record in the main form has
already been saved, and attempting to control when a user may or may not
leave an already-saved record for another record is unlikely to be
successful. You need to think not only about what is happening within
Access while the user is working on the allocations, but also what happens
if, say, the power fails part way through. The allocations will then be
left incomplete, even though the user was not necessarily finished and did
not move to a new record.

Rather than trying to force the user to enter complete allocations, you
would do better to rethink your design so that warnings are generated at
appropriate times/places if there are incomplete allocations. Furthermore,
you could prevent the allocations being output if incomplete (for example,
if you have some kind of allocations report, then at the time the report is
requested you could validate that they are complete and refuse to run the
report - with an appropriate explanatory message - if there are incomplete
allocations).
 
Thanks - what your saying make perfect sense. Do you have any advice on how
to write the BeforeInsert code?

Allen said:
Acces saves each record individually. If you block the saving of the record
until they all add up to 100%, the first record would have to *be* 100%
before the user could save the record. All remaining records would then have
to be 0%. So, although your strategy is understandable, it is logically
flawed.

An alternative might be to use the BeforeInsert event procedure to assign
the remaining percent to this field. For the first record, it would then
assign 100%, but you can change it to (say) 40%. Then as soon as you start
entering the next row, it would assign 60%, which you again reduce if you
need a 3rd row. This approach a) helps the user understand how much is left,
and b) helps reduce the chance that that the user gets the last entry wrong.

Additionally, you could refuse to print the results if the records don't add
up yet, indicating that the records are "incomplete" or "overblown."
I have a subform that is an allocation (by cost center) of costs by
percent.
[quoted text clipped - 9 lines]
 
This example assumes a main form bound to tblGrant (with GrantID primary
key), and a subform bound to tblGrantDetail (with GrantID foreign key, and
MyPercent as a Number field of size Double formatted as Percent):

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the Grant in the main form first"
ElseIf Me.NewRecord Then
strWhere = "GrantID = " & Nz(!GrantID,0)
Me.MyPercent = 1 - Nz(DSum("MyPercent", "tblGrantDetail",
strWhere),0)
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

szag via AccessMonster.com said:
Thanks - what your saying make perfect sense. Do you have any advice on
how
to write the BeforeInsert code?

Allen said:
Acces saves each record individually. If you block the saving of the
record
until they all add up to 100%, the first record would have to *be* 100%
before the user could save the record. All remaining records would then
have
to be 0%. So, although your strategy is understandable, it is logically
flawed.

An alternative might be to use the BeforeInsert event procedure to assign
the remaining percent to this field. For the first record, it would then
assign 100%, but you can change it to (say) 40%. Then as soon as you start
entering the next row, it would assign 60%, which you again reduce if you
need a 3rd row. This approach a) helps the user understand how much is
left,
and b) helps reduce the chance that that the user gets the last entry
wrong.

Additionally, you could refuse to print the results if the records don't
add
up yet, indicating that the records are "incomplete" or "overblown."
I have a subform that is an allocation (by cost center) of costs by
percent.
[quoted text clipped - 9 lines]
 
Ok I am getting close but for some reason it error out with my code of:

Me.Percent = 1 - Nz(DSum("Percent", "T_Trans_Dept%", [forUnits]), 0).

I can't figure out why. When I just assign a "1" it works fine so I know the
rest of the code must be working. somehow I am doing something wrong in my
DSum.

Thanks!

Allen said:
This example assumes a main form bound to tblGrant (with GrantID primary
key), and a subform bound to tblGrantDetail (with GrantID foreign key, and
MyPercent as a Number field of size Double formatted as Percent):

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the Grant in the main form first"
ElseIf Me.NewRecord Then
strWhere = "GrantID = " & Nz(!GrantID,0)
Me.MyPercent = 1 - Nz(DSum("MyPercent", "tblGrantDetail",
strWhere),0)
End If
End With
End Sub
Thanks - what your saying make perfect sense. Do you have any advice on
how
[quoted text clipped - 26 lines]
 
Enclose the table name in square brackets. It contains a non-text character
(%.)

Also, the [forUnits] does not loook like a valid criterion. It needs to be a
string of the form:
[fieldname] = value

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

szag via AccessMonster.com said:
Ok I am getting close but for some reason it error out with my code of:

Me.Percent = 1 - Nz(DSum("Percent", "T_Trans_Dept%", [forUnits]), 0).

I can't figure out why. When I just assign a "1" it works fine so I know
the
rest of the code must be working. somehow I am doing something wrong in my
DSum.

Thanks!

Allen said:
This example assumes a main form bound to tblGrant (with GrantID primary
key), and a subform bound to tblGrantDetail (with GrantID foreign key, and
MyPercent as a Number field of size Double formatted as Percent):

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the Grant in the main form first"
ElseIf Me.NewRecord Then
strWhere = "GrantID = " & Nz(!GrantID,0)
Me.MyPercent = 1 - Nz(DSum("MyPercent", "tblGrantDetail",
strWhere),0)
End If
End With
End Sub
Thanks - what your saying make perfect sense. Do you have any advice on
how
[quoted text clipped - 26 lines]
 
Back
Top