Update ID numbers that are not autonumber

[CH]

Hi, I'm trying to update an ID field in a table. The new record starts with
an ID of 121888 and I'd like to update 12,010 records sequentially from this
number. Does anyone know how to do this?
Thanks in advance.

 

[John W. Vinson]

Hi, I'm trying to update an ID field in a table. The new record starts with
an ID of 121888 and I'd like to update 12,010 records sequentially from this
number. Does anyone know how to do this?
Thanks in advance.

What's in the field NOW? How would you determine which existing record should
e 121889?

 

[CH]

Hi John,
There are numbers in the field at the moment. I appended a new table with
12,010 records and would like to carry on the ID numbering. It doesn't really
matter what new record is 121889, or their order, just as long as all the new
records get a unique ID number.
Thanks,
CH

 

[Larry Linson]

Are the numbers sequential, from which you wish to "carry on the ID
numbering"? What number format are they? Or, indeed, is it a text field
with numeric characters? Do you have a batch of new, unnumbered records, or
are you looking for an approach for manual entry of each record.

Some common suggestions for which you can check help:

DMAX is a domain aggregate function that returns the maximum value in a
field in a table. Retrieve that, add 1 to it, and place that in the ID
field. When that update is complet, the next DMAX will return the number you
just inserted. You can also use DMAX in a loop in DAO or ADO VBA code, if
you have a "batch" of existing records to number.

Larry Linson
Microsoft Office Access MVP

 

[CH]

Hi Larry,
Thanks for your suggestions.
To answer your questions: Yes the numbers are sequential that I'd like to
carry on the ID numbering with. The field is a 'number' field. Yes I have a
batch of new unnumbered records. I'm not looking to do it manually as there
are over 12,000 new records.
Would I use DMAX in a query?
Thanks, CH