Create an Unique AutoNumber

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

Guest

I am creating a database with several audit sections; Cash, Investments,
Loans, etc. There will be various audit steps entered into the table for
each section. I want to create a unique identifier for each step. for
example the audit steps in cash would be labelled C.1, C.2 etc. and
Investments would be I.1, I.2 etc. Currently, I have a separate table for
each section and a separate form for data entry for each table but I would
like to be able to combine the data into one table. The problem that I have
is that the autonumber function will assign the numbers sequentially
regardless of the order the audit steps are entered into the table. For
example, I might end up with some cash steps that skip a few numbers because
those got assigned to an investment step. How can I set it up so that it
assigns sequential numbers to each section? Thanks.
 
Basically, you cannot use autonumber for that. It's simply going to
increment sequentially as each record is added, regardless of the section.

To do what you want to do, you're going to have to write some code. Instead
of an autonumber field, use a long integer field. Then do a DMax on that
field with a criteria specifying the section you want. That will give you
the max # assigned to that section. Increment that number by 1 and that's
what you store in the long integer field for that section.
 
Thanks JP. I gave that a try and it worked, but I have some more questions.
I want this calculation to automatically assign the value to that record in
the table, but when I add new records, all of the previous records change
their value to the new count. Is there a way to correct this. Thanks again
for your help.
 
jdmcleod said:
Thanks JP. I gave that a try and it worked, but I have some more
questions. I want this calculation to automatically assign the value
to that record in the table, but when I add new records, all of the
previous records change their value to the new count. Is there a way
to correct this. Thanks again for your help.
Click to expand...

How exactly did you implement JP's suggestion? Normally one would assign
the value to a new record when it is created in a form. There should be no
way for this to affect other records.
 
Being new to Access, I guess I did it incorrectly. I simply added a text box
to my form and listed the formula as the source. Each time a new record is
added, that text box automatically gives the next available number, the
problem is when you go into a previously added record on the form, it
chagnes. Any help on where I went wrong would be greatly appreciated.
Thanks.
 
jdmcleod said:
Being new to Access, I guess I did it incorrectly. I simply added a
text box to my form and listed the formula as the source. Each time
a new record is added, that text box automatically gives the next
available number, the problem is when you go into a previously added
record on the form, it chagnes. Any help on where I went wrong would
be greatly appreciated. Thanks.
Click to expand...

No, that is incorrect. The number has to be added to a field in your table.
Assuming you had a long integer field in your table named ID you would put
the following in your form's BeforeUpdate event

If Nz(Me.ID, 0) = 0 Then
Me.ID = Nz(DMax("ID","TableName","Your Criteria"), 0) + 1
End If
 
Back
Top