update tables by VBA

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hi,
I need update fields of a table(TABELLA1) from a query(CALCOLO) by a VBA
code; I already know I can do it by accoding query, but I need to do it by
VBA code.

at the moment, I've built this code, but I can't understand where i'm wrong:

Function Riepilogo() As String
Dim db As DAO.Database
Set db = Access.CurrentDb
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim i As Integer

' here I make the time band
db.Execute "delete from tabella1"
Set rs = db.OpenRecordset("select * from tabella1", dbOpenDynaset)
For i = 1 To n
rs.AddNew
rs.Fields!hlavorate = 0
rs.Fields!IN1 = 0
rs.Update
Next
rs.Close

' here I insert the query data
Set rs2 = db.OpenRecordset("select * from Calcolo", dbOpenDynaset)
While Not rs2.EOF
sRic = Format(rs2.Fields!out, "hhnn")
db.Execute "update Tabella1 set Hlavorate=Hlavorate+" &
rs2.Fields!passo1 & " where IN1=" & sRic & ""
rs2.MoveNext
Wend
rs2.Close

Set rs = Nothing
Set rs2 = Nothing

db.Close
Set db = Nothing

Riepilogo = ""
End Function
 
Hi Andrew

here you delete everything in tabella1
db.Execute "delete from tabella1"
and then you fill the table with zeros
For i = 1 To n
rs.AddNew
rs.Fields!hlavorate = 0
rs.Fields!IN1 = 0
rs.Update
Next

In your update query there's a where clause saying to update where IN1= sRic
but every IN1 = 0 'cause you zeroed it.
So that I think you have to revise the logic of what you're doing.

HTH Paolo
 
Back
Top