Update Query

R

ruben

Hi!!, I need to update a field with a value correlative for example
1,2,3,4,etc... for all the records in my access 2002 table, how can I
make a query that can do this???


This query doesn't work.

UPDATE Tbl_OrderColumns
SET Tbl_OrderColumns.id_order = Tbl_OrderColumns.id_order + 1

Thanks.
 
O

Ofer Cohen

Open a RecordSet and look through the records

Function UpdateNumber()
Dim MyDB as Dao.DataBase, MyRec as Dao,Recordset, I as Long
I = 1
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select * From TableName Order By
FieldToBeOrderBy")
While Not MyRec.Eof
MyRec.Edit
MyRec!FieldName = I
MyRec.Update
I = I + 1
MyRec.MoveNext
Wend
End Function

Note: I didn't try the code, but I hope it will provide you with the right
direction
Back up your data before you try anything
 
O

OfficeDev18 via AccessMonster.com

Ruben,

I understand from your question that the id_order field is an AutoNumber
field. You can't change AutoNumber information at all. It's cast in concrete!

Interestingly, if you delete a record and re-append the record to the same
table, the AutoNumber field will now be 1 more than the previous highest
number. E.g., if there are 200 records in the table and you delete any record
and re-append it, the AutoNumber field will have the value 201 (assuming the
first record started with 1). By the way, if you delete ALL the records and
append new ones, the lowest value of the AutoNumber field in the new
recordset will also be 201.

The only way I've succeeded in starting with 1 while numbering items while
doing a sort-on-the-fly was by using VBA to dictate the value of an unbound
text box on a report. In truth, that way has some quirks too, but that's
another post.

Hope this helps,

Sam
 
Top