Create my own Unique ID number to track records and link records in different tables instead of using AutoNumber

Posted on 2007-10-04
Medium Priority
Last Modified: 2013-11-29

I have a two Excel Spreadsheets,rds and the second contains 4000 records.  I have converted the files into Access called tblPartInventory (6000 Records) and tblPartTransfer (4000 records).  

When I receive a new part, I enter the PartName, PartNumber, and VendorIDe information into a table called tblPart.  When I have to catalog the part and place it on the shelf, I add a Barcode number, part serial number, and additioal part information into to a recvord in tblPartInventory.  When I have to transfer the part, the part information is deleted from tblPartInventory and moved to tblPartTransfer.

Both excel files that I received do not have a unique product ID, that can be associated with a part's PartNumber or BarcodeNumber, therfore I canot create a one-to-on or one-to-many relationship.  The data was just entered into colums on the spreadsheet and as a result; I cannot use the Parts Barcode Number, Part Number or Part Serial Number because 50% of all the fields are blank.

Please help me come up with a way to assign a unique ID number to all related records in both tables that can be added to an partID field the same way Access AutoNumbe works each time I create a new Inventory record.  I would perfer not to have to enter the unique on each line manually.
Question by:cesemj
  • 2

Assisted Solution

elec1cele earned 240 total points
ID: 20016345
The Easiest way I can think of to relate them would be to add an auto number to both tables and then create a new join table with both numbers in it to relate the records.  It depends on how you need them to be related as to whether or not this solution will work for you

Author Comment

ID: 20021351
Is there a way to automatically create a random alapha numeric value ike, AUTO1234567, that is automatically entered into the ID field when I enter a new record from a form.
LVL 19

Accepted Solution

frankytee earned 1760 total points
ID: 20027807
not directly but there are several workarounds that can be done.
one is to write some fancy code to do this, or the simplest way is use a composite key (ie two fields) as your primary key, one alpha the other an autonumber field.
the alpha field doesnt have to be unique here due to the uniqueness of the numeric field so you can use a default value for the alpha field or use code to generate a random value, something like:
chr((round(rnd * 25,0) + 1)+ 64)
for each character

or still use 2 fields (first autonumber, 2nd alphanumericID) where just before the user "submits" via a command button, the button will populate the alphanumericID field with concatenation of the alpha part (using the code above) + the autonumber value from that field. the difference here is that your alphanumericID  will contain the AUTO1234567 so it will be unique.


Author Comment

ID: 20040746
IThank you, I am going to work on this now and let you know what happens.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question