• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

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
0
PeterdeB
Asked:
PeterdeB
  • 4
  • 3
1 Solution
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now