Access VBA Open Record Set w/ Select Statement

[Clete]

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

 

[Norie]

How are the highlighted lines not working?

 

[Clete]

After I run the code for the line X and Y are equal to 0

 

[Clete]

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.

 

[james_lankford]

go here
Microsoft Access Update Query Examples, SQL Syntax, and Errors
see the sections named
Update with Values from Fields in Another Table
and
Update Using its Own Value


it sounds like you want to take something like this

select 
   DI.YESTERDAYS_INT_Calc as x, 
   TI.TotInt as y,  
   DI.YESTERDAYS_INT_Calc + TI.TotInt as z
from 
  [Daily Interest] as DI
  inner join 
  TotalInterest as TI
  on 
  DI.FileNo  = TI.FileNo

and turn it into an update statement

 

[Clete]

Im trying this now with a series of queries.... this is the last one which will give me what I need. When I execute this code in access I get an error saying "Operation must use an updateable query"

UPDATE TOTALINTEREST INNER JOIN TotalInterestQuery ON [TotalInterestQuery].[v] = TotalInterest.FileNo SET TotInt = TotalInterestQuery.z
WHERE TOTALINTEREST.FileNo = TotalInterestQuery.v

;

 

[Clete]

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 total interest that I want to move into TotInt in the TotalInterest Table