Autonumber trouble

[herbivorous]

I just wrote a function that takes the primary key/autonumber field and from it combines fields in a text string. But I noticed some errors and eventually looked at my source table -- about 16 months ago there is a big jump in the autonumber field, and this function call works on all records prior to the jump and none after. There seems to be some sort of indexing or reference problem for all records after. If I generate the autonumber anew, the function works fine, so clearly the problem is in that field. I don't want to have to renumber all of my linked tables, though, and so hold out hope that somebody can tell me how to smack some sense into this freaking ID field.

 

[smedvid]

This is why I do not use AutoNumber... Here is what I hit, and you may have something similar. I permitted users to delete/restore data from a table. Delete was great, but when I restored, the autonumber would get confuzed and not know where to start.

Consider using a simple DMax() to get the highest ID and then simply add 1 to that value. In my case ID is not an autonumber field, but an Integer. htwh

Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[herbivorous]

You use DMax()+1 as the default value in the table definition?

 

[smedvid]

I've used a couple of approaches.... But, not that one. It may work???

I usually have a data entry page and place a unbound field that holds the next available number. Which is fine for single user systems. In other cases, I use the before save event to insure the unbound field value is DMax()+1. I've used thin approach in multi-user systems with no trouble so far. I've seen other approaches on this site... just do a search on Autonumber... lots of stuff should pop up. Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/