Adding an incremental number to each record in a table

  • Thread starter Thread starter Andreww
  • Start date Start date
A

Andreww

High - I have a small table of 500 recs and I want to be able to add a URN
(Unique Ref Number). I am not a great Access expert and am thus somewhat
flummoxed on how to do this. The point is that record 1 gets a 0001, 2
0002, n 000n etc.

I imagine it has to be done with VBA. I bought "Beginning Access VBA 2000"
a while ago with suggest a possible route using DAO which I thought was
defunct in favour of ADO/something else.

I would think the flow goes something like this:

proc inc_urn()

connect to database
use tblStudent
dim kk as int

kk=1

go top

for kk =1 to 500

replace urn with kk

next

close tblStudent
disconnect database

end proc

I come from the humble FoxPro background so all this is somewhat new!

Any help appreciated.

Regards

Andrew

www.jmdata.co.uk
 
If you haven't referenced the records in this table anywhere else in your
database, do 1 else do 2:
1A If your primary key is autonumber, go to design view of the table. Delete
the PK. Recreate the PK. The PK will be 1-500 after you leave design view
1B If PK is not autonumber, create new field named URN and make it autonumber.
URN will now be a-500 after you leave design view.
2. Go to design view and create new field named URN and make it autonumber. URN
will now be a-500 after you leave design view.
 
I already have data in the table.

If you don't already have a numerical "URN", adding an autonumber field will
work fine even with data already in the table - just, as always, back up your
database before making the changes. If you don't want your "URN" based on an
autonumber for "new" records (that is, if you want to generate your own
sequential numbers to reduce the likelihood of *gaps* in the generated
sequence), after creating your autonumber field, and saving the changes (it will
now have record numbers generated), simply convert the autonumber field to a
"Number" data type of size "Long Integer". The numbers will still be there and
you can number new records with your own generated numbers. If this is your
intention, and you need help with implementing that approach, just holler and
I'm sure you will receive ideas on numerous ways to generate record numbers. To
get you started, here is a link to a post of mine on this subject:

http://groups.google.com/groups?hl=...ff&[email protected]

If that link is broken, try this one: http://tinyurl.com/2qr3k

:-)
 
Many thanks folks... sorted as what most of you suggested.

Would still love to know how this sort of thing can be done using VBA if
anyone has any ideas???

Cheers

Andrew
 
Go to
http://www.rogersaccesslibrary.com/TableOfContents3.asp
and check a sample called AutonumberProblem.mdb. This
approach uses code in the Before Insert event of the form
to increment the number by 1. In general, it works
something like this: me!IDnumber =nz(DMax
("IDnumber","name of query or table"))+1. nz converts
null values to zero; DMax identifies the largest number in
that field; +1 increments the number. Search Google
groups for Access autonmumber emulate or something like
that. There has been quite a bit of discussion on the
topic.
 
Back
Top