Public Sub UpdateTable()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * from auxBancoScheda order by [SK Nº] ASC")
Set rst = CurrentDb.OpenRecordset("Select * from BancoScheda order by [SK Nº] ASC")
For nIndex = 0 To 95
rst.Edit
If rst.Fields(nIndex).Value <> rs.Fields(nIndex).Value Then
rst.Update
Else
Exit Sub
End If
Next
rst.MoveNext
rs.MoveNext
Set rst = Nothing
Set rs = Nothing
End Sub
Well you need to move the values from one recordset to the recordset you are going to update.
Not having done this, but don't see why it should not work, try:
rst.Fields(nIndex).Value = rs.Fields(nIndex).Value
Public Sub UpdateTable()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * from auxBancoScheda order by [SK Nº] ASC")
Set rst = CurrentDb.OpenRecordset("Select * from BancoScheda order by [SK Nº] ASC")
For nIndex = 0 To 95
rst.Edit
If rst.Fields(nIndex).Value <> rs.Fields(nIndex).Value Then
rst.Update
Else
Exit Sub
End If
Next
rst.MoveNext
rs.MoveNext
Set rst = Nothing
Set rs = Nothing
End Sub
For what its worth, here are a few thoughts.
I would set it up in 2 stages and you may want to backup your data, and add some Onerror code.
1) Create a test to make sure your procedure would change the values as it should(as you have programmed). Just debug.print a few fields in a couple of records before going to a real Update.
2) Do the actual update once you have confirmed the process is doing what you intend. And adjust the routine as FoFa said (see the red lines)
As I see the code,
-there is nothing setting the value in the rst field
-there is no code to return to the For Loop to process the next records
- do the Update at the record level (after the For Loop)
- do the rst.edit at the record level (outside the For Loop)
Code:
Public Sub UpdateTable()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * from auxBancoScheda order by [SK Nº] ASC")
Set rst = CurrentDb.OpenRecordset("Select * from BancoScheda order by [SK Nº] ASC")
[COLOR="Red"]
Do while not rst.eof
rst.Edit[/COLOR]
For nIndex = 0 To 95
If rst.Fields(nIndex).Value <> rs.Fields(nIndex).Value Then
[COLOR="Red"]'
'fields aren't equal so, replace the field value in rst
'with the field value in rs
rst.Fields(nIndex).Value = rs.Fields(nIndex).Value
[/COLOR]
Else
End If
Next
[COLOR="Red"]rst.Update[/COLOR]
rst.MoveNext
rs.MoveNext
[COLOR="Red"]Loop
rs.close
rst.close[/COLOR]
Set rst = Nothing
Set rs = Nothing
End Sub