How to correct this access 2003 database behaviour?

Hi Folks, I attached an MS ACCESS 2003 database which I use in my application. I also attached several procedures I use in conjunction with the ADO table. Now I have this field, Number which should only reflect the number of customers. This is where something goes wrong.

Let's say I have 10 customers, then it nicely displays number 1 to 10. But when I jump from the first customer to the last, it changes the number 10 into number 1. So then it reads 1-2-3....8-9-1. When I jump back to the first customer it changes it back to 10 again. Now at first I tried to solve this by disallowing duplicates, thus indexed yes( no duplicates).

But this caused some problems since I already had an index yes no duplicates field I believe. Can someone solve this problem?

Regards,

Peter

// this is the procedure which provides the numbering
 
procedure TForm1.tblKlantenCalcFields(DataSet: TDataSet);
begin
  Dataset.fieldbyname('Nummer').value := max(dataset.RecNo, 1);
end;

Open in new window

database.mdb
PeterdeBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
It looks like your statement
Dataset.fieldbyname('Nummer').value := max(dataset.RecNo, 1);

updates the dataset as you move from row to row.  The max() function isn't the same as a 'Select Max()...' query
0
PeterdeBAuthor Commented:
Hi Aikimark,

And now what?

I have no idea how to handle this :)
0
aikimarkCommented:
Peter

I don't know anything about your application or what you're trying to do with the statement you posted.  A bit more explanation might be helpful.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

PeterdeBAuthor Commented:
Ah okay aikimark, I only want to edit add and delete customers to it nothig more actually. Say I have 300 customers or records I simply want an index from 1 to 300 so I can immediately see how many customers I have.

Like so:

1. Jenna Jameson
2. Paris Hilton
3. My Grandma
4. and so on

And I dont know how to achieve that

Regards,

Peter
0
aikimarkCommented:
use an autonumber primary key.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterdeBAuthor Commented:
Hi aikimark, I already have one which I hide since the indexes make no sense. For instance when I delete all records and add a single one this is automatically numbered 47 the next is 48 and so on. I hoped it would start with 1 again but it doesnt. So that is why I created another field using the procedure I attached.

Peter
0
aikimarkCommented:
This PC doesn't have Access2003, so I haven't been able to see inside your database.  I wanted more information about your problem before I started peeking into your data/code.

================
I do something similar:
I created an ordering field in one of my applications.  There is a 'working' table into which I append a set of applicable rows from the historical (last visit) table.  The working table has an autonumber field.

After appending the rows, I run a query to find the MIN() of the autonumber values for the person (current visit).

I then run a query that updates the person records in the working table, changing the sequence number field, relative to the MIN() for the set.  As designed, this sequence starts at 10 and has an inter-row difference of 10.

Although you might be able to do this sequencing update in one query, I've found that I get better performance with two queries, passing the results of the first query, MIN(), into the second query as a parameter.

================
You can reset the autonumber sequence in two ways.
* compress the database after deleting the rows.  You would probably want this table to be in a separate database as an attached table.  You will need detach the table before compression and reattach it after compression.

* create a new 'temp' table each time you need a sequence.

* If you are inserting rows one-at-a-time, you supply your own (overriding) autonumber value, starting at 1.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.