Can I control the auto Number Field?

  • Thread starter Malik via AccessMonster.com
  • Start date
M

Malik via AccessMonster.com

Hi,
I want to know that can i control the autoNumber Field , If Yes, How to do
that..........
 
T

tina

well, probably the answer is No, but we can't say for sure unless you
explain just what it is about an Autonumber that you want to control. and
tell us *why* you want to control an Autonumber, too, because i can't think
of any reason to do it, or try to.

hth
 
J

John W. Vinson

Hi,
I want to know that can i control the autoNumber Field , If Yes, How to do
that..........
Click to expand...

As Tina says... probably No, and probably You Really Don't Want To.

An autonumber has one purpose, and one purpose ONLY: to provide a meaningless
unique key for a record.

It's not guaranteed to be sequential. It's not guaranteed to be free of gaps.
It's not editable.

If you want any of these features... don't use an Autonumber; use something
else, such as a Long Integer number that you maintain and assign manually or
with VBA code.
 
M

malik via AccessMonster.com

Well,

I want That If I have a Sale Invoice Form which has a field SaleID
(AutoNumber), Now If I Delete some invoice, for example 115,
Now I want to save another invoice on the saleId 115,

So, This is my need to control AutoNumber.

As John W. Vinson said, VBA code can be used, But Problem is that I dont
know much about VBA coding.

Thanks for ur response Buddies.
 
B

BruceM

If you are concerned about gaps in the numbering, don't use autonumber.
Code can be used to generate your own number, but it will be more difficult
to reuse a number. That is, you can easily add a number that is higher by
one (or ten, or whatever) than the previous high number, but I'm not sure
how to go about locating the "missing" number in a sequence. I have seen
code somewhere for doing that, but in most cases an invoice created today,
sandwiched between invoices from two weeks ago, is to be avoided anyhow.

A Google groups search on the following search string should produce some
results if you need to reuse a number:
"microsoft.public.access" missing number sequence

You can leave out "microsoft.public.access" from the search.
 
J

John W. Vinson

Well,

I want That If I have a Sale Invoice Form which has a field SaleID
(AutoNumber), Now If I Delete some invoice, for example 115,
Now I want to save another invoice on the saleId 115,

So, This is my need to control AutoNumber.

As John W. Vinson said, VBA code can be used, But Problem is that I dont
know much about VBA coding.

Thanks for ur response Buddies.
Click to expand...

See Bruce's reply. You should NEVER use an autonumber for an invoice ID, for
the reasons posted before. Auditors get really annoyed (and annoying) when
they see invoices 113, 114 and 116 and no trace of 115 - and that's exactly
what you will get using autonumbers!

Instead you can use a Long Integer SaleID field; you can automatically
populate it by using the Form's BeforeInsert event:

Me!SaleID = NZ(DMax("[SaleID]", "[tablename]")) + 1

This needs to be a bit more complicated if you have a multiuser system where
two users might simultaneously be creating new invoices.
 
Top