Autonumber Sequence

[Rebecca Dell]

Hi everyone

I have a spreadsheet I want to import to access. The records are already
numbered sequentionally after being sorted in alphabetical order. I want to
use this field as the primary key and have it automatically assigned once the
DB is up and running. I know I can't turn this into an autonumber field but
I thought if I added an extra autonumber field with the records sorted, the
autonumber would be assigned to match the existing number but it isn't - in
fact it's all over the place and I can't figure out where the sequence is
coming from.. I've tried adding the order to the table properties but
nothing works.

I need to keep the existing numbers because they are tied to other programs.

Rebecca

 

[Larry Daugherty]

You need to keep thinking through what you're trying to accomplish (in
real-world terms) and then explain it in your next post.

Data in tables is never guaranteed to be in any predictable order.
Tables are "buckets" in which records are held. Access can change the
order of data in tables to suit its own devious purposes. Only by
using Queries can you control the sort order of the data presented.

Since you have already "koochied" your data before assigning the
numbers, why not continue to just use the numbers you already have as
the primary key?

Do you intend to replace the Excel functionality with Access?

How are these numbers used in other programs?

HTH

 

[BruceM]

Maybe you used random autonumbers. If you were to copy the table, paste it
as structure only, open it in design view and add an autonumber field, then
use an append query to add data from the original table, the new table would
have the autonumbers in order, as long as you selected sequential rather
than random autonumbering in table design view when you created the new
table.
This can be a handy technique for creating sequential numbering where it
didn't exist before, but you would need to change the autonumber field to
Number (Long Integer, for instance) and come up with a way of adding
sequential numbers without gaps, for which purpose you cannot rely on
autonumber. The good news is that sequential numbering is really quite
simple. One way of doing so is described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
In general, if you are using this system to add, say, an invoice number, you
could have something like this as the default value of a text box bound to
the InvoiceNum field:
=DMax("InvoiceNum","TableName") + 1
The default value only comes into play for new records. Exisiting records
will keep their numbers. If you set the format of that text box to 00000
you will have 00001, 00002, etc. as your numbers.
The link explains one way to avoid problems in a multi-user environment.
You can also use VBA code. One way that helps guard against duplication
errors in a multi-user environment is to use the form's Before Update event:

If Me.NewRecord Then
Me.InvoiceNum = DMax("InvoiceNum","TableName") + 1
End If

In a single-user environment you could do the same thing in the form's
Current event.

 

[Ken Sheridan]

Rebecca:

You might care to take a look at a demo I've posted at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


This generates sequential numbers in table's primary key column when a row
is entered via a form. To prevent conflicts in a multi-user environment the
last number is stored in an external database in a shared folder. This is
opened exclusively in code so only one user can get a new number at any one
time. In the rare event of the external database being open by another user
it makes up to 10 randomly timed attempts to get the number. As with a true
autonumber, if a user begins to insert a row and then abandons it the number
will not be re-used. This can be got round by getting the number only when
the user attempts to save the row, however, rather than when they begin to
insert the row..

It also allows for the next number to be used when a row is inserted to be
reset, provided the new number is higher than the last used, so in your case
you could reset it at your highest number plus one.

Note that it only works for individual rows being inserted via the form, not
for bulk inserts with an 'append' query for instance. For bulk inserts you'd
need to use code which loops through the source recordset and appends one row
at a time, calling the function to insert a new number each time.

Ken Sheridan
Stafford, England

 

[Rebecca Dell]

Hi to everyone who posted back to me on this and apologies for the delay in
reply.

I think I had bitten off a little more than I could chew when I started on
transferring my project from Excel to Access and abandoned it for a while.
I'm back at it now and just tonight solved my problemw with a combination of
a couple of ideas here.

So thank you very much!
Rebecca