Assigning a Specified Unique ID

M

M Johnson

I have a table already populated with distinct records
and I need to add a field where I can assign a unique
ID. I want the unique ID for the first record to read
F007708604 and then go from there (F007708605, 606, etc.)

How do I go about getting these ID's into my table?

Any ideas/suggestions would be greatly appreciated.

Thanks so much in advance :)
 
R

Rose

Here's the Help topic on how to do this.
Yes, believe it or not, it was actually in Help, :)

Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change
the starting value of an AutoNumber field that has its
NewValues property set to Increment to a number other than
1. For a table that contains records, you can also use this
procedure to change the next value assigned in an
AutoNumber field to a new number.

Create a temporary table with just one field, a Number
field; set its FieldSize property to Long Integer and give
it the same name as the AutoNumber field in the table whose
value you want to change.
How?

In Datasheet view, enter a value in the Number field of the
temporary table that is 1 less than the starting value you
want for the AutoNumber field. For example, if you want the
AutoNumber field to start at 100, enter 99 in the Number field.


Create and run an append query to append the temporary
table to the table whose AutoNumber value you want to change.
How?

Note If your original table has a primary key, you must
temporarily remove the primary key before running the
append query. Also, if your original table contains fields
that have the Required property set to Yes, the Indexed
property set to Yes (No Duplicates), or field and/or record
ValidationRule property settings that prevent Null entries
in fields, you must temporarily disable these settings.

Delete the temporary table.


Delete the record added by the append query.


If you had to disable property settings in step 3, return
them to their original settings.
When you enter a record in the remaining table, Microsoft
Access uses an AutoNumber field value 1 greater than the
value you entered in the temporary table.

Note If you want to compact the database after changing
the starting AutoNumber value, make sure to add at least
one record to the table first. If you don't, when you
compact the database, the AutoNumber value for the next
record added will be reset to 1 more than the highest
previous value. For example, if there were no records in
the table when you reset the starting value, compacting
would set the AutoNumber value for the next record added to
1; if there were records in the table when you reset the
starting value and the highest previous value was 50,
compacting would set the AutoNumber value for the next
record added to 51.
 
G

Guest

Hi Rose,

I had found the topic in help, but I couldn't get it to
run right. I already have records in 1 table. When I
append the new id field, it only auto numbers correctly
for any new record that I add in- I need to add a unique
field to the records already in the table- Any other
advice?

Thanks!
 
R

Rose

I'm sure the more brilliant of the Access help guys could
come up with a more elegant solution. But since this is a
one-shot, a possibility that springs to mind is to

a) 'port the table to Excel
b) autofill the field ID column (by typing in the first two
values, highlighting them both, and dragging down the
handle in the bottom right of the cells)
c) export the sheet back to Access

Not pretty, but it would work.
 
P

Pavel Romashkin

I would strongly discourage using an AN field to get a meaningful IDs
like this. Even though it may seem to work, AN is not editable and is
not guaranteed to be in any order or continuous. It is only "guaranteed"
to be unique (although if you read the NG, you will see posts that claim
duplicate AN in corrupted databases).
You should write a little piece of code that will generate your own
automatic, incrementing Ids. You can get a sample little db that will
show how to do this from here
http://www.ainaco.com/access/db_samples/anum_cboxes.zip
and more help from others:
http://groups.google.com/groups?hl=...nG=Search&meta=group=comp.databases.ms-access
Good luck,
Pavel
 
T

Tim Ferguson

I want the unique ID for the first record to read
F007708604 and then go from there (F007708605, 606, etc.)

How do I go about getting these ID's into my table?
Click to expand...

I think this is one of those rare occasions where iterating a recordset
might be the best way to do things. There is an SQL solution, but it's not
trivial and debugging and testing would probably take longer than a quick-
and-dirty VBA sub.

First of all, I am not clear about your data needs. What about this "F" on
the front: does it have any meaning? will it ever change? And the starting
number is both very precise and very large -- is there some special
significance to the 007708 part, or is this really going to count up eight
million records? I have a strong suspicion that your business needs can be
easier met by using a the .Format property of the textboxes on your forms
and reports.

Some random assumptions: the "F" is significant and needs to be in a
separate field, called "SerialCode"; the first four digits "0077" are fixed
and can be inserted at display time, leaving a number "SerialNumber" that
increments from 8605. If your situation is different, then you can make
changes as needed.

First, use the UI to insert a Text(1) field for the SerialCode and the
SerialNumber as a Long Integer. Leave both as NOT REQUIRED, and default
values of NULL for now. You can't set them as PK yet, because they don't
have unique values.

Next run a bit of VBA to fill them like this:

strSQL = "SELECT SerialCode, SerialNumber " & vbNewLine & _
"FROM MyTable" & vbNewLine & _
"ORDER BY SomeSortingCriterion;"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

dwNumber = c_dwStartingNumber ' = 8605 but use a const
Do While Net rs.EOF
rs.Edit
rs!SerialCode = "F"
rs!SerialNumber = dwNumber
rs.Update

dwNumber = dwNumber + 1
rs.MoveNext

Loop

rs.Close

Now go back to the table design, set the two new fields to Required TRUE,
and combine them into the PK.

Finally, you'll need some VBA on your data entry or record creation forms
to allocate a new SerialNumber. Check Google for "Access Custom Autonumber"
for a large number of solutions that may fit your need.

When you need to display the number, you should use a query that includes
soemthing like the following:

SELECT SerialCode & "0077" & FORMAT(SerialNumber, "00000")
AS UniqueID,
etc, etc...


Hope that helps


Tim F
 
Top