update table variable

  • Thread starter Thread starter Chris L.
  • Start date Start date
C

Chris L.

HI,
I have two tables inner joined and would like to input
(update) a variable in the second table using a field
from the first table. (In the primary table multiple
records are being related to a single record in the
second table) Having problems with the update. What am I
missing? THANKS.
CL

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim tx1 As Double
Set db = CurrentDb()

strSQL = "select * from comb03p inner join cito03p on
comb03p.city_num=cito03p.city_num"

Set rs = db.OpenRecordset(strSQL)

While Not rs.EOF

With cito03p
tx1 = cito03p!x1
cito03p![x1] = tx1 + rs![ttc]
cito03p.Update
End With
rs.MoveNext
Wend
 
Sorry,
My earlier reply wont work

if you are trying to update the sum of the values then use this:

HS
----------------------------------------


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim tx1 As Double
Set db = CurrentDb()

strSQL = "select CityNum, Sum(ttc) as SumTTC
comb03p Group by city_num"

Set rs = db.OpenRecordset(strSQL)

if not (rs.bof and rs.eof)
rs.movefirst
While Not rs.EOF
strSQL = "update Cit03p set x1 = " & rs("sumTTC") &
strSQL= strSQL & " where city_num = " & rs("City_num")
db.execute (strSQL)

rs.MoveNext
Wend
 
Back
Top