VBA to Insert payments from a DailyQuery into a Total Payments table (tricky)

[cleteh]

Trying to finish the last step in what for me has been a very complicated procedure involving VBA and SQL. I have no idea how do go about starting the last part. I put in a comment in red into the procedure that will need to be replaced by code.

The procedure below ends with running a Query I wrote to produce interest calculations. 2 of the fields the query returns are FileNo and YESTERDAYS_INT_CALC. I want to take the amount in YESTERSAYS_INT_CALC and add it to the amount in a table called TotalInterest that has 2 fields. FileNo and totInt

Private Sub Form_Open(Cancel As Integer)
Dim maxDate As Date
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")

'Insert into TotalInterest the amount in the field YESTERDAYS_INTEREST_CALC from the Query called DailyInterest by adding it to the field TotInt

End If
End Sub

[jonh]

x = currentdb.openrecordset("select YESTERDAYS_INTEREST_CALC from DailyInterest")(0) + totint
currentdb.execute("insert into TotalInterest fieldname values (" & x & ")")

[cleteh]

FINAL CODE

John I couldn't thank you enough below is the final code it all seems to be working. Can I ask what the (0) is doing? Thats the one part of your code I wasn't sure about.

x = CurrentDb.OpenRecordset("select YESTERDAYS_INTEREST_CALC from DailyInterest")(0) + CurrentDb.OpenRecordset("select totint from TotalInterst")(0)
CurrentDb.Execute ("insert into TotalInterest (totInt) values (" & x & ")")