How to create a unique auto-number?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
can anyone tell me how i can create an auto number that
is a primary key, without to enter the values?
for eg, the following auto numbers (also is my primary
key) will be generated everytime a record is entered.
ABC-XYZ-001
ABC-XYZ-002
ABC-XYZ-003, etc
 
Hi,
can anyone tell me how i can create an auto number that
is a primary key, without to enter the values?
for eg, the following auto numbers (also is my primary
key) will be generated everytime a record is entered.
ABC-XYZ-001
ABC-XYZ-002
ABC-XYZ-003, etc
Click to expand...

Well, the Access Autonumber facility will NOT work for this.

What are ABC and XYZ? Other fields? Constant, or variable? Do you want
the numbering to start over if either changes, or should the numbers
just count up indefinately? What will you do when you get the 1000th
record?

This can be done - but just how depends on your answers to these
questions!
 
what i mean is that the key has prefix.but the numerals
are actually running order numbers.
For eg, my company is Apple and the dept name is HR
So, my auto-pri-key would be called Apple-HR-001
and 001 would be the 1st record.
subsequent record will be automatically generated by
automatically as
Apple-HR-001
Apple-HR-002
Apple-HR-003 etc
 
what i mean is that the key has prefix.but the numerals
are actually running order numbers.
For eg, my company is Apple and the dept name is HR
So, my auto-pri-key would be called Apple-HR-001
and 001 would be the 1st record.
subsequent record will be automatically generated by
automatically as
Apple-HR-001
Apple-HR-002
Apple-HR-003 etc
Click to expand...

This is called an "Intelligent Key". That's not a compliment.

Storing data redundantly in two fields (your Company field and your ID
field, your Department field and your ID field) is a bad idea; storing
multiple different kinds of data in a single field is even worse!

I'd suggest that - if you need this number for compatibility with an
existing paper system - that you do NOT construct this composite field
at all, in your table. Instead you can use the *three* fields
(Company, Dept, and an Integer field SeqNo) as a three-field composite
key, and concatenate them in a Query for display purposes:

IDNum: [Company] & "-" & [Dept] & "-" & [SeqNo]

To increment SeqNo, you'll need to do all your data entry using a
Form; table datasheets don't have any usable events. In the Form's
BeforeUpdate event put code like

Me!txtSeqNo = NZ(DMax("[SeqNo]", "[your-table]", "[Company] = """ &
Me!txtCompany & """ AND Dept = """ & Me!txtDept & """")) + 1
 
thanks for the advice.
sorry...can i bother u again?
i m really new to access. can u help to show me the steps
(step-by-step) approach to do that?
as i tried ur method but dont seem to get anywhere.
really appreciate ur help.
thank u.

-----Original Message-----
what i mean is that the key has prefix.but the numerals
are actually running order numbers.
For eg, my company is Apple and the dept name is HR
So, my auto-pri-key would be called Apple-HR-001
and 001 would be the 1st record.
subsequent record will be automatically generated by
automatically as
Apple-HR-001
Apple-HR-002
Apple-HR-003 etc
Click to expand...

This is called an "Intelligent Key". That's not a compliment.

Storing data redundantly in two fields (your Company field and your ID
field, your Department field and your ID field) is a bad idea; storing
multiple different kinds of data in a single field is even worse!

I'd suggest that - if you need this number for compatibility with an
existing paper system - that you do NOT construct this composite field
at all, in your table. Instead you can use the *three* fields
(Company, Dept, and an Integer field SeqNo) as a three- field composite
key, and concatenate them in a Query for display purposes:

IDNum: [Company] & "-" & [Dept] & "-" & [SeqNo]

To increment SeqNo, you'll need to do all your data entry using a
Form; table datasheets don't have any usable events. In the Form's
BeforeUpdate event put code like

Me!txtSeqNo = NZ(DMax("[SeqNo]", "[your- table]", "[Company] = """ &
Me!txtCompany & """ AND Dept = """ & Me!txtDept & """")) + 1



.
Click to expand...
 
thanks for the advice.
sorry...can i bother u again?
i m really new to access. can u help to show me the steps
(step-by-step) approach to do that?
as i tried ur method but dont seem to get anywhere.
really appreciate ur help.
thank u.
Click to expand...

Well... I know NOTHING about your table structure, nor about how
you're adding data (table datasheet? Sorry, no way; Form, possible).

Care to give me some background about the table structure, and the
form that you're using?
 
ok.
i m creating a "library" for recording documents and these
documents r reference as follows :
Apple-HR-001/04 where Apple=company name and HR=dept name.
001 is the first document and 04 is the year of the
tender. We can omit the year.
We also have 2 tables, I think. One for storing the tender
ref num, title, and details (eg.name of people who can
access the record).
Another table for the name of people who can access the
document.
I just need a simple form to fill with the relevant fields
so that I can add records via the form instead of the
tables. And when I click the OK button on the form, it
will accept all the inputs.

Can you help to guide me? I really dont know how to get
started. Thank u. If u can,can u email me some sample with
steps how to do it? Thank u again.
 
Back
Top