VBA Open Record Set w/ Select Statement

[cleteh]

I have a table called TotalInterest with a field call TotInt. In my code below I am trying to take the value currently in TotInt and add to it the value in YESTERDAYS_INT_Calc from the table Daily Interest. Also, Im not sure I somehow have to loop this as there are many files I am tracking interest for.

Everything seems to work except for the lines in red below.

Thanks for any help

Private Sub Form_Open(Cancel As Integer)
Dim maxDate As Date
Dim x As Currency
Dim y As Currency
Dim z As Currency
maxDate = DMax("SystemDate", "SystemDate")
If maxDate = Date Then
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO SystemDate (SystemDate) VALUES (Date());"
DoCmd.SetWarnings True
DoCmd.OpenQuery ("Daily Interest")
x = CurrentDb.OpenRecordset("select YESTERDAYS_INT_Calc from [Daily Interest]")(0)
y = CurrentDb.OpenRecordset("select TotInt from TotalInterest")(0)
z = x + y
CurrentDb.Execute ("insert into TotalInterest (TotInt) values (" & z & ")")
End If
End Sub

[cleteh]

The lines are indeed working... but now I need to set this up on some kind of loop that only adds the value z to the TotInt field of the correct FileNo. FileNo is a unique number in both tables.

[cleteh]

I'm also trying another way to update the TotalInterest table by running a series of queries in a row... The last Update statement below is producing an error saying "Operation must be an updateable query"

UPDATE TOTALINTEREST INNER JOIN TotalInterestQuery ON [TotalInterestQuery].[FileNo] = TotalInterest.FileNo SET TotInt = TotalInterestQuery.TotInterest
WHERE TOTALINTEREST.FileNo = TotalInterestQuery.FileNo;

[cleteh]

Ive also written another query I could call at anytime into my code that does work it's called TotalInterestQuery.

It gives me the field totalInterest that I want to move into TotInt in the TotalInterest Table