Expression not working on tab form (1 Viewer)

Tupacmoche

Registered User.
Local time
Yesterday, 20:15
Joined
Apr 28, 2008
Messages
291
This problem is from the post 'Write Conflict' which needs its own post and attention. The original issue was using subforms on the same data source which caused a 'Write Conflict' This was resolved by using a tabbed control on the main form to logically segregate data.

But this has created a new issue. The following expression worked perfectly before the tabs and now is not:

=Sum(Nz([PP1_Amt])+Nz([PP2_Amt])+Nz([PP3_Amt])+Nz([PP4_Amt])+Nz([PP5_Amt]))

See attached file.

It simply adds up the payment above using the 'On Got Focus' event. But now it has a value in it even before anything is entered into the payment amounts. How can this be fixed? How can I restrict it to the current record?
 

Attachments

  • Tabbed Form - Pledge Payment.JPG
    Tabbed Form - Pledge Payment.JPG
    80.9 KB · Views: 44

Ranman256

Well-known member
Local time
Yesterday, 20:15
Joined
Apr 9, 2015
Messages
4,339
this should work, providing your text box names are correct.
(the text box name may not be the same as the field name, but they can)

by the look, these are multiple records? If so, why are there only 5?
if not, why not? Payments should be infinite in quantity, in their own table.
 

MarkK

bit cruncher
Local time
Yesterday, 17:15
Joined
Mar 17, 2004
Messages
8,178
Those five separate amounts should be in five separate rows in a related table. This is a table design problem, a normalization problem.
hrh.
Mark
 

Tupacmoche

Registered User.
Local time
Yesterday, 20:15
Joined
Apr 28, 2008
Messages
291
Absolutely true must be normalized but I inherited it this way and just need to fix the sum issue for now. Ranman256 said that the text boxes should have a different name than the field name. The names were the same so, I changed them from PP1_Amt to txtPP1_Amt ect. Now, I get #error. Any ideas?:banghead:
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:15
Joined
May 21, 2018
Messages
8,463
There will likely be other places in your application you want to get the sum. So I would recommend normalizing to give more flexibility.

qryUnionPmt

Code:
SELECT TableName.ID, 
 TableName.PP1_Amt As Payment_Amount
FROM TableName
UNION
SELECT TableName.ID, 
 TableName.PP2_Amt
FROM TableName
UNION
SELECT TableName.ID, 
TableName.PP3_Amt
FROM TableName
UNION
SELECT TableName.ID, 
TableName.PP4_Amt
FROM TableName
UNION SELECT TableName.ID, 
TableName.PP5_Amt
FROM TableName;

You can then get the sums using another query

Code:
SELECT 
 ID, 
 Sum(qryUnionPmt.Payment_Amount) AS SumOfPayment_Amount
FROM 
 qryUnionPmt
GROUP BY
 ID;

Now you can always do a dlookup to get the sum from the above query for any ID
 

Users who are viewing this thread

Top Bottom