Update Query or Loop?

[Kevin D.]

I have a table "tblAttendees" with a field "BidNumber". The table is
populated with 300 records but the "BidNumber" Field is Null. I want to
assign a number to the "BidNumber" field in the first record and then have
the "BidNumber" field incremented by 1 for each successive record until all
records are updated. I don't want to use a autonumber field. I tried an
update query using a DMAX but it only updated the "BidNumber" field in the
next null record and not all the records.

What's the best way to do this? A query or in VBA using a Loop until EOF
method? And how would the code look?

Thanks for your help.

Kevin D.

 

[Ofer Cohen]

Use VBA, something like

Dim DB as Dao.DataBase, MyRec As Dao.RecordSet, I as Integer
I = 1
Set DB=CurrentDb
Set Myrec = Db.openRecordSet("Select BidNumber From tblAttendees")
While Not MyRec.Eof
MyRec.Edit
MyRec!BidNumber = I
MyRec.Update
I = I + 1
MyRec.MoveNext
Wend
=====================
You might want to order the records in a cretain order so the BidNumber will
be in the right order, if so, set the order by in the record set

Set Myrec = Db.openRecordSet("Select BidNumber From tblAttendees Order By
FieldName")

 

[Kevin D.]

Thanks so much. It worked perfectly.

Kevin D.

Use VBA, something like

Dim DB as Dao.DataBase, MyRec As Dao.RecordSet, I as Integer
I = 1
Set DB=CurrentDb
Set Myrec = Db.openRecordSet("Select BidNumber From tblAttendees")
While Not MyRec.Eof
MyRec.Edit
MyRec!BidNumber = I
MyRec.Update
I = I + 1
MyRec.MoveNext
Wend
=====================
You might want to order the records in a cretain order so the BidNumber will
be in the right order, if so, set the order by in the record set

Set Myrec = Db.openRecordSet("Select BidNumber From tblAttendees Order By
FieldName")