It seems like this ought to be easy, and I'm sure it is, I just
haven't figure out how to make it easy.

I have data in a secondary table that I have been cleaning up so I can
insert into my primary table. The last step is to update a converted
index field (added by access when the data was imported from excel,
and irrelevant to the primary table) in each record with an
incremental value. So, the first record gets "7000", the second gets
"7001", and so forth. (This will be part of the key in the primary
table)

To make this more fun, there are groups of records that I want to get
numbered together, so I made a query that can pull each group and sort
it appropriately.

The table name is tblDrCbList. The query is qrySortDrCbs. The field I
am over-writing is ID.

Any help in pointing out simple solution I'm missing is greatly
appreciated.

Thanks.

Tim

Tim Olson wrote:
first append all the records and assign Null to the counter field.
After the append, select all records from primary where autonum field is
greater than when you appended sorted by the autonum. THen loop thru
that recordset, adding 1 the current starting number. The initial val
of the starting num would be 6999.

Thanks Salad. I figured something like that might be the easiest way,
and while I know how to write an update query, and how to write a
loop, I don't know how to make the two of them interact with each
other and recordset commands (methods? I think I've seen folks in
CDMA call them methods rather than commands). The database I'm
working with has not been split, and has a fairly low level
functionality, so there aren't any recordest operations in it on which
I can build this update-loop query. In general, what would the code
look like?

Thanks for your help.

Tim

On Nov 23, 3:48*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

Not too sure what you mean by 'numbered together', if you just mean that you
want the numbers for these records to be in a contiguous sequence then this
can be done but is a bad design.

If the above is the intention then you are better off adding a 'grouping'
field so that you can easily add items to the group with out having to worry
about re-sequencing the ID field.

Anyway, bit of code to get you started, make sure you test this on a backup
of the data not on your working copy.

dim lodb as dao.database
dim rs as dao.recordset
dim lngCount as long

set lodb = currentdb
set rs = lodb.openrecordset("SELECT [ID} FROM qrySortDrCbs")

lngCount = 6999
with rs
do until .eof
lngCount = lngCount + 1
.Edit
.Fields("ID") = lngCount
.Update
.movenext
loop
.Close
end with

set rs = nothing
set loDb = Nothing


Note this will only work if your query is an updatable query.



--
Terry Kreft


"Tim Olson" <bassmantpo (AT) gmail (DOT) com> wrote

Excellent. Thank you Terry. This is exactly the kind of start that I
needed. I appreciate your help.

Tim

On Dec 6, 6:04*am, "M L Kreft" <kre03038... (AT) kre03038298 (DOT) karoo.co.uk>
wrote: