use of autonumber

[Lapchien]

Our database has a field called 'Card' which records the transaction
reference number. This is generated as an autonumber. Not the best way at
all - I know, having picked this up in the last few months.

We do not want to use 7 numbers for this, and would like to know if we can
generate the Card number by 'picking up' any of the 'missing' Card
autonumbers in the Card field? (autonumber having missed an awful lot of
insertions)...

I guess a bit of code that looks at the Card column, and picks up one of the
non-sequential (missing!) numbers, and inserts that into the new record Card
field..?

--
Thanks,
Lap

[MacDermott]

If you want your numbers to be meaningful (e.g. sequential), don't use
autonumber.
Change to a Long Integer, and calculate the value you want explicitly, e.g.
by using DLookup().

HTH
- Turtle

"Lapchien" <bl**@blar.tv > wrote in message
news:10******** *******@ananke. eclipse.net.uk. ..

Our database has a field called 'Card' which records the transaction
reference number. This is generated as an autonumber. Not the best way at all - I know, having picked this up in the last few months.

We do not want to use 7 numbers for this, and would like to know if we can
generate the Card number by 'picking up' any of the 'missing' Card
autonumbers in the Card field? (autonumber having missed an awful lot of
insertions)...

I guess a bit of code that looks at the Card column, and picks up one of the non-sequential (missing!) numbers, and inserts that into the new record Card field..?

--
Thanks,
Lap

[Lapchien]

Yes, no question that is what we require. Can you give us some of the
mechanics needed (code?) to lookup and return the 'missing' sequential
numbers in the table and return those as the value we require for 'Card'?
"MacDermott " <ma********@nos pam.com> wrote in message
news:H3******** **********@news read2.news.atl. earthlink.net.. .

If you want your numbers to be meaningful (e.g. sequential), don't use
autonumber.
Change to a Long Integer, and calculate the value you want explicitly, e.g. by using DLookup().

HTH
- Turtle

"Lapchien" <bl**@blar.tv > wrote in message
news:10******** *******@ananke. eclipse.net.uk. ..

Our database has a field called 'Card' which records the transaction
reference number. This is generated as an autonumber. Not the best way

at

all - I know, having picked this up in the last few months.

We do not want to use 7 numbers for this, and would like to know if we can generate the Card number by 'picking up' any of the 'missing' Card
autonumbers in the Card field? (autonumber having missed an awful lot of
insertions)...

I guess a bit of code that looks at the Card column, and picks up one of

the

non-sequential (missing!) numbers, and inserts that into the new record

Card

field..?

--
Thanks,
Lap

[Lapchien]

BTW they numbers do not have to be sequential - we don't view card number
666660 as newer than 666659...

"MacDermott " <ma********@nos pam.com> wrote in message
news:H3******** **********@news read2.news.atl. earthlink.net.. .

If you want your numbers to be meaningful (e.g. sequential), don't use
autonumber.
Change to a Long Integer, and calculate the value you want explicitly, e.g. by using DLookup().

HTH
- Turtle

"Lapchien" <bl**@blar.tv > wrote in message
news:10******** *******@ananke. eclipse.net.uk. ..

Our database has a field called 'Card' which records the transaction
reference number. This is generated as an autonumber. Not the best way

at

all - I know, having picked this up in the last few months.

We do not want to use 7 numbers for this, and would like to know if we can generate the Card number by 'picking up' any of the 'missing' Card
autonumbers in the Card field? (autonumber having missed an awful lot of
insertions)...

I guess a bit of code that looks at the Card column, and picks up one of

the

non-sequential (missing!) numbers, and inserts that into the new record

Card

field..?

--
Thanks,
Lap

[Tom van Stiphout]

On Thu, 11 Dec 2003 07:26:45 -0000, "Lapchien" <bl**@blar.tv > wrote:

If you want to set a value, change your autonumber to longint.

I don't think there is a SQL Statement that would find missing
numbers, unless you have another table with all 6-digit numbers. So
you write some DAO code like this (off the top of my head).
Essentially you loop over the card numbers checking for a gap in the
numbers.

public function GetNextCard() as long
dim lngCardPrev as long
dim lngCardNew as long
dim db as dao.database
dim rs as dao.recordset
set db=currentdb
'NOTE: Assuming Card is the Primary Key, so rs will be ordered by
Card. If not, use a query with an OrderBy clause.
'NOTE2: This needs work if table can ever have 0 or 1 row.
set rs=db.openrecor dset("MyTable", dbOpenSnapshot)
lngCardPrev=rs! Card
lngCardNew=0
rs.movenext
do while not rs.eof
if rs!Card=lngCard Prev+1 then
'not a gap
rs.movenext
else
'gap found
lngCardNew=rs!C ard
exit do
end if
loop
if lngCardNew=0 then
'found EOF before we found a gap. Use next no.
lngCardNew=lngC ardPrev+1
end if
rs.close
set rs=nothing
set db=nothing
GetNextCard=lng CardNew

-Tom.

Our database has a field called 'Card' which records the transaction
reference number. This is generated as an autonumber. Not the best way at
all - I know, having picked this up in the last few months.

We do not want to use 7 numbers for this, and would like to know if we can
generate the Card number by 'picking up' any of the 'missing' Card
autonumbers in the Card field? (autonumber having missed an awful lot of
insertions). ..

I guess a bit of code that looks at the Card column, and picks up one of the
non-sequential (missing!) numbers, and inserts that into the new record Card
field..?

[Tom van Stiphout]

On Thu, 11 Dec 2003 07:27:43 -0700, Tom van Stiphout
<to*****@no.spa m.cox.net> wrote:

Oops, after rs.movenext, add this line:
lngCardPrev = rs!Card

-Tom.

On Thu, 11 Dec 2003 07:26:45 -0000, "Lapchien" <bl**@blar.tv > wrote:

If you want to set a value, change your autonumber to longint.

I don't think there is a SQL Statement that would find missing
numbers, unless you have another table with all 6-digit numbers. So
you write some DAO code like this (off the top of my head).
Essentially you loop over the card numbers checking for a gap in the
numbers.

public function GetNextCard() as long
dim lngCardPrev as long
dim lngCardNew as long
dim db as dao.database
dim rs as dao.recordset
set db=currentdb
'NOTE: Assuming Card is the Primary Key, so rs will be ordered by
Card. If not, use a query with an OrderBy clause.
'NOTE2: This needs work if table can ever have 0 or 1 row.
set rs=db.openrecor dset("MyTable", dbOpenSnapshot)
lngCardPrev=rs !Card
lngCardNew=0
rs.movenext
do while not rs.eof
if rs!Card=lngCard Prev+1 then
'not a gap
rs.movenext
else
'gap found
lngCardNew=rs!C ard
exit do
end if
loop
if lngCardNew=0 then
'found EOF before we found a gap. Use next no.
lngCardNew=lngC ardPrev+1
end if
rs.close
set rs=nothing
set db=nothing
GetNextCard=ln gCardNew

-Tom.

Our database has a field called 'Card' which records the transaction
reference number. This is generated as an autonumber. Not the best way at
all - I know, having picked this up in the last few months.

We do not want to use 7 numbers for this, and would like to know if we can
generate the Card number by 'picking up' any of the 'missing' Card
autonumbers in the Card field? (autonumber having missed an awful lot of
insertions).. .

I guess a bit of code that looks at the Card column, and picks up one of the
non-sequential (missing!) numbers, and inserts that into the new record Card
field..?