Customizing Autonumber

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

Guest

Hi All,

I am given a task to create a permit application form for the City I work
for. The Building Department use to carry on their work on a paper form.
Now they wanted to do it electronically. They want the permit number to
continue from the last number they have given out. And then increment by
one. However, when I choose AutoNumber for the Permit field it always start
with 1. Is there a way where I could customize to a specific number of my
choice? like for instance start with 2556635 and then increment by 1?

Your help is appreciated
 
Unfortunately, Microsoft Access doesn't seem to allow very much flexibility
when it comes to autonumbering. Fortunately, you can manually get around
this. If you're careful.

If you want to start with permit number 2556635, you're going to need to
make 2556634 other records first. Which of course sounds like a pain in the
ass, and don't fool yourself - that's exactly what it is. But if you're
diligant, you might be able to manually type out that many empty records in
oh, say half a month. You could, however, do a bit of copying and pasting to
get up to that number.

Create a new table, and create a column of type Autonumber. You'll need a
second column just to type in a random bit of data to get this started (since
you can't create an entry if the only field is an autonumber). Save the
design and in the datasheet view create a few nonsensicle records. You can
delete the second column at this point if you'd like. Probably a good idea
since we don't want 2556634 extra entries.

Then comes the busy work. Select all of the records by pressing CTRL-A,
copy them with CTRL-C, click on the Last Record button in the record
navigation on the ottom, right-click on the asterix for the bottom column
(the one used for adding new entries), and then paste. Click yes, and you
will have double the number of entries you started with. In a matter of a
few minutes, with such wild exponential growth, you can reach whatever number
you want rather quickly.

Of course, the problem with this is that you might.... overshoot. Do the
math and figure out what half of your target number is, and stop pasting when
you reach that number. You'll have to be careful with your final pastes in
order to get the number you want, but a little bit of thinking will put you
on the dime. Once you reach that target (or at least, one below the target),
you can delete all of the records that you used as placeholders. Autonumber
does not re-use it's numbers, so it will not revert back to 1.

Keep in mind that since autonumber does not re-use any numbers, deleting a
permit will cause that number to be lost forever. Instead of deleting
records, you should consider flagging them with a check box or something
similar, and them filtering them out of searches.

Hope that helps!

Nick
 
Dear Getch:

If the value of this number has meaning for your users, then you shouldn't
use an Autonumber field! Autonumbers can grow very large, and sometimes turn
negative in value. They are intended to provide unique numbers to use as
Primary Keys. You *can* manipulate the number, but it takes work, and you
would be much better off not to do so, since if you begin to edit a record
and then undo the edit, that number is no longer available as an Autonumber,
and your sequence will be ruined yet again.

Better to use a custom Autonumber. There are examples of how to do this
here:

http://rogersaccesslibrary.com/TableOfContents3.asp

Look for the "Autonumber problem" samples...

HTH
Fred Boer
 
Alternatively, you could code your entry form to create the number for you.
Instead of using an autonumber as a primary key, create a normal number field
(or double, if it actually gets as high as 2556635) and use that instead.
Then, slap in some code for the Form_Current() procedure, which is called
every time a new record is selected in the form. IE, if you click on the
record navigation buttons on the bottom of the form, Form_Current is run each
time. You can have form_current check to see if the Permit Number text box
is null, or equals "", or whatever criteria you'd like to add. Then, find
the maximum permit number in the table via SQL (SELECT MAX(permit_num) FROM
permits) and then set the form's permit field equal to that number plus one.

Nick
 
You don't need to insert 2556634 records first. You simply need to insert 1
record into the table with a value of 2556634 for the Autonumber field.
Access will continue numbering from that value for the next record.
 
Back
Top