fields from other records

  • Thread starter Thread starter Petrus Van den Cruyce
  • Start date Start date
P

Petrus Van den Cruyce

i have a database in with i 'd like to do the following: from record 1
the value of field 3 should be copied into record 2 field 3, from record
3 the value of field 3 to record 4 feild 3, etc..
How do i manage this in access or excel?
The records already exits, it's only a matter of copying the value but
because it is a database with more than 2000 records i prefer to do it with
a query or a macro or a program.

Can anyone give me a good suggestion?

Thanks in advance
 
I'd do it with the following (untested) code:

Sub basUpdateRecords
On Error Goto ProcedureError
Dim rs as DAO.Recordset
Dim x As Variant 'should change this to proper datatype
Set rs = CurrentDb.OpenRecordset("SELECT [Field3] FROM

ORDER BY [some field to put them into the correct order]
Do Until rs.EOF = True
x = rs("Field3")
rs.MoveNext
If rs.EOF = False Then
rs.Edit
rs("Field3") = x
rs.Update
rs.MoveNext
End IF
Loop
ProcedureExit:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub
ProcedureError:
Msgbox "Darn, this sucks"
Resume ProcedureExit
End Sub

Create a new module, paste the above code into it, go to Tools.Reference
and ensure you have a reference to the DAO library (I think it is
Microsoft Data Access Objects - either 3.5 or 3.6 depending on what you
have got installed), place your cursor into the code and press the
function Key "F5".

Remember to make a copy of the database first :-)

Regards,
Andreas
 
Back
Top