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 5 1787
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
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********@nospam.com> wrote in message
news:H3******************@newsread2.news.atl.earth link.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
BTW they numbers do not have to be sequential - we don't view card number
666660 as newer than 666659...
"MacDermott" <ma********@nospam.com> wrote in message
news:H3******************@newsread2.news.atl.earth link.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
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.openrecordset("MyTable", dbOpenSnapshot)
lngCardPrev=rs!Card
lngCardNew=0
rs.movenext
do while not rs.eof
if rs!Card=lngCardPrev+1 then
'not a gap
rs.movenext
else
'gap found
lngCardNew=rs!Card
exit do
end if
loop
if lngCardNew=0 then
'found EOF before we found a gap. Use next no.
lngCardNew=lngCardPrev+1
end if
rs.close
set rs=nothing
set db=nothing
GetNextCard=lngCardNew
-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..?
On Thu, 11 Dec 2003 07:27:43 -0700, Tom van Stiphout
<to*****@no.spam.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.openrecordset("MyTable", dbOpenSnapshot) lngCardPrev=rs!Card lngCardNew=0 rs.movenext do while not rs.eof if rs!Card=lngCardPrev+1 then 'not a gap rs.movenext else 'gap found lngCardNew=rs!Card exit do end if loop if lngCardNew=0 then 'found EOF before we found a gap. Use next no. lngCardNew=lngCardPrev+1 end if rs.close set rs=nothing set db=nothing GetNextCard=lngCardNew
-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..? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ilan Sebba |
last post by:
I have a 'supertype' table with only one field: autonumber. Call this table
the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Martian have only one thing in common: they give...
|
by: Lee C. |
last post by:
I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and...
|
by: Traci |
last post by:
If I have a table with an autonumber primary key and 100 records and I delete
the last 50 records, the next record added would have a primary key of 101. Is
there any way to have the primary key...
|
by: yf |
last post by:
A KB article
"http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells
that the maximum number of records that a table may hold if the PRIMARY
key data type is set to AUTONUMBER is...
|
by: jimfortune |
last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore,
sometimes I use those same fields in orderdetail type tables. So it's
important in that case that once an autonumber key value is...
|
by: petebeatty |
last post by:
I have created a SQL string the properly inserts a record in the table.
However, the insert does not occur at the end of the table. Instead
it inserts a record after the last record that I viewed....
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: gtwannabe |
last post by:
I'm having a problem with a form that uses AutoNumber as the primary
key. I have an Abort button to delete the current record and close the
form. If AutoNumber is assigned, the code executes a...
|
by: Tom_F |
last post by:
To comp.databases.ms-access --
I just discovered, to my more than mild dismay, that some tables in my
Microsoft Access 2003 database have duplicate numbers in the
"AutoNumber" field. (Field...
|
by: ashes |
last post by:
Hi,
I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net
When someone wants to register on the website, they fill out a form and the...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |