update a table based on another table from a form

R

rerunbh

Hello, I have 2 tables. One is site with cpid, paytype, payamt. I have
another table that is a history table with the cpid, newpaytype and
newpayamt. The paytype and payamt on the site table can be changed numerous
times and we need to keep track of when/who, etc. I have a Change form
created. The main form is based off the current data in the site table. The
Change form has a subform which consists of continuous forms for the history
table. So the main form will show 1 cpid but the subform will show all of the
changes for that cpid. I created and update query for the afterevent
procedure for both the newpaytype and newpayamt fields. The code for the
update query is: UPDATE SiteAutoID INNER JOIN FinancialChanges ON
SiteAutoID.CPID=FinancialChanges.CPID SET SiteAutoID.PayAmt =
FinancialChanges.NewPayAmt
WHERE SiteAutoID.CPID=[Please enter the CPID for the financial change(s)];
It seems to work but I noticed that if I enter a newpayamt on the first
continuous form for that cpid, it doesn't update. When I enter the second
one, which is the most current newpayamt in the next continuous form, it
updates the site table but with the amount from the first continuous form not
the most recent amount.


For example for cpid 1 the first change I make is old payamt = 15, newpayamt
= 17. then after this I add another change old payamt = 17, newpayamt = 20.
The site table updates to 17 and not 20. How can I fix this please? Also my
afterupdate procedure is the following:
Private Sub NewPayAmt_AfterUpdate()
On Error GoTo Err_NewPayAmt_AfterUpdate

Dim stDocName As String

stDocName = "UPDATE_FinChangePayAmt"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_NewPayAmt_AfterUpdate:
Exit Sub

Err_NewPayAmt_AfterUpdate:
MsgBox Err.Description
Resume Exit_NewPayAmt_AfterUpdate

End Sub

Thanks so much for any help anyone can give me.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top