Best way to re-number data in fields

[stonward]

This project has been unutterably miserable; after almost a dozen retail system out there working great, this one update has stripped all the joy from software development from me: I no longer trust any part of what I thought I knew about Access! This is my last project (I have been unable to just drop it).

The system I'm trying to update let the user enter their own Invoice Number, which was also the OrderID....and, a mistake I know, a PK.

Anyhow, several thousand records in, his numbering methods change - daily it seems. I need to replace this now with an auto-incrmemting field. I ahve tried all manner of methods, but cannot seem to make anything but a mess. Has anyone come across a similar issue, or is it just me?

Please take pity on someone who promises to only make databases for my friends record collections in future.

Ston the Depressed.

[ADezii]

This project has been unutterably miserable; after almost a dozen retail system out there working great, this one update has stripped all the joy from software development from me: I no longer trust any part of what I thought I knew about Access! This is my last project (I have been unable to just drop it).

The system I'm trying to update let the user enter their own Invoice Number, which was also the OrderID....and, a mistake I know, a PK.

Anyhow, several thousand records in, his numbering methods change - daily it seems. I need to replace this now with an auto-incrmemting field. I ahve tried all manner of methods, but cannot seem to make anything but a mess. Has anyone come across a similar issue, or is it just me?

Please take pity on someone who promises to only make databases for my friends record collections in future.

Ston the Depressed.

Are there related Tables Linked to the PK, and if so, how many?

[NeoPa]

Auto-incrementing.

What exactly do you mean by that? Is an AutoNumber type the sort of thing?

From what I understand of your predicament it sounds quite doable to fix this. Fixing the one changing their spec 1/2 way through is another matter of course. One I'll leave to you if I may ;)

[stonward]

Thanks for the replies, Guys - i've been away weeping....

Yeah, there are hundreds of linked records, ADezii.
Actually, what I was after is quite simple (I think) for a good coder....automa tically add '1' to last record number, like the autonumber does (most of the time)?
Additionally, I thought I'd move my 'primary' queries to SQL in the code window since they are all quite similar (my queries). I've made them public variables, but they still generate an 'Invalid Outside Procedure' error. Surely I've not got to repeat the SQL for every procedure?

Thanks for your help.

Ston the Shameless... ;)

[NeoPa]

To implement an AutoNumber-like field in your form you can use DMax() with appropriate criteria to set the field at the point of adding the record.

NB. That last bit was important. The reason the AutoNumber value is not shown until the record has already been created is because the sequence would not work very well if values were assigned at any point prior to usage being guaranteed (In other words, if it's still possible for the save to be stopped then the value should not yet have been assigned).