Difficulty in updating a table from Form (1 Viewer)

Tanvir

New member
Local time
Today, 10:18
Joined
Feb 22, 2022
Messages
3
Hi everyone, I am new to this forum and this is my first post. I am trying to update a table from a form but despite trying various options and codes I am unable to do so. My problem goes like this: It's a sort of Loan System. I have a transaction table and a master table. Master table has loanees information as well as a field called Balance. Both are joined (relationship) with RegNo. Now what I want to do is I want the Balance field to be updated (reduced) when I press OK button, with the amount I enter in transaction table. I have put following code in btnOK_click.

Code:
Private Sub btnOK_Click()

Dim strSQL  As String
       
    strSQL = "UPDATE MasterFile INNER JOIN TransFile On (MasterFile.RegNo = TransFile.RegNo)" & _
             "SET MasterFile.LastTransDate = Me!TransDate AND" & _
             "MasterFile.LastBalance = MasterFile.LastBalance - Me!TransAmt" & _
             "WHERE MasterFile.RegNo = Me!RegNo"
   
    DoCmd.RunSQL strSQL
      
   
End Sub
But somehow it's not working. Can somebody guide me where I am going wrong
 
Last edited by a moderator:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:18
Joined
May 7, 2009
Messages
19,169
it's not advisable to have a Balance field in your table when
you can Calculate the balance from your Transaction table using a Query.

query to total all payments to a particular Regno (qryPayments):
select Regno, Sum(TransAmt) As TotalPayment, Max(TransDate) As LastPayment from transfile Group by Regno


query to get balance:
select A.Regno, A.LoanAmt, B.TotalPayment, B.LastPayment, (A.LoanAmt - B.TotalPayment) As Balance From Masterfile As A Left Join qryPayments As B On A.Regno = B.Regno;
 

Tanvir

New member
Local time
Today, 10:18
Joined
Feb 22, 2022
Messages
3
it's not advisable to have a Balance field in your table when
you can Calculate the balance from your Transaction table using a Query.

query to total all payments to a particular Regno (qryPayments):
select Regno, Sum(TransAmt) As TotalPayment, Max(TransDate) As LastPayment from transfile Group by Regno


query to get balance:
select A.Regno, A.LoanAmt, B.TotalPayment, B.LastPayment, (A.LoanAmt - B.TotalPayment) As Balance From Masterfile As A Left Join qryPayments As B On A.Regno = B.Regno;
Thanx Arnel, I know it's not advisable to have a calculated field in file, but then I was asked to report all those who have not paid back anything, or people who are defaulting on their payments for the last six months. These kind of queries prompted my to include LastDate and Balance fields in master file. I want to put all these info in one report. (RegNo, Name, Last PaymentDate, Balance). Can you run two queries in one report?
I'll try your queries.
Thanx again.
 
Last edited:

GPGeorge

Grover Park George
Local time
Yesterday, 22:18
Joined
Nov 25, 2004
Messages
1,776
Thanx Arnel, I know it's not advisable to have a calculated field in file, but then I was asked to report all those who have not paid back anything, or people who are defaulting on their payments for the last six months. These kind of queries prompted my to include LastDate and Balance fields in master file.
But I'll try your query. Thanx again.
Sometimes, for a pure reporting situation, where we know the data will never, ever again be updated (think "Data Warehouse" type applications), we do resort to storing calculated values for efficiency. We almost never consider that a good idea, though, in a Transactional Relational Database Application.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:18
Joined
Feb 19, 2002
Messages
42,981
I was asked to report all those who have not paid back anything, or people who are defaulting on their payments for the last six months
You would use a query to find the balance as suggested by @arnelgp.

"Not Advisable" is pretty wishy-washy advice. Do not store this value. How about that. No equivocation. It is hard enough for experts to make this work. It is almost impossible for novices. This is not a data warehouse application where the data is static. This is a live, transaction database that is constantly in flux. If you can calculate the outstanding balance in a query, you do NOT need to store that value.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:18
Joined
May 7, 2009
Messages
19,169
see this demo.
see the LoanMaster table has no Balance field.
balance of loan is calculated on query qryLoanBalance.
totalPayment is calculated in query qryPayments.

see report rptLoanStatus for the balance of the loan/s.
 

Attachments

  • LoanDB.accdb
    1.1 MB · Views: 205

Tanvir

New member
Local time
Today, 10:18
Joined
Feb 22, 2022
Messages
3
Thanx Arnel @arnelgp. Your queries really helped. I achieved exactly what I wanted. Thank you again, and thank you all for your valuable advices.
 

Users who are viewing this thread

Top Bottom