Auto numbering Problems

[KevW]

Not sure as to whether or not this query shoulsd go here or else where? But here is the problem. I have been asked to repair a database that has lost the autonumbering facility. Apparently this was caused by somebody leaving the database open when the network crashed.

Each team can access the database via forms but now when the team responsible for registring new pieces of work tries to enter a new piece of work the autonumber reverts to one already within the database.

I was told that the only fix to this problem was the DMax function which I am unfortunatley not familar with. Can anyboby explain how to go abouit this and where it should be used.

 

[burrcm]

Copy the database for safe keeping. Presumably it is one table which has this issue? Open it in design view. Do you see the autonumber field? Delete it. Close the table. Compact & Repair. Open the table in design view. Add an autonumber column (If it was at the top, insert row). Make it the primary key if it was before. Should now work.

Chris B

 

[coyote]

Dmax

Not sure as to whether or not this query shoulsd go here or else where? But here is the problem. I have been asked to repair a database that has lost the autonumbering facility. Apparently this was caused by somebody leaving the database open when the network crashed.

Each team can access the database via forms but now when the team responsible for registring new pieces of work tries to enter a new piece of work the autonumber reverts to one already within the database.

I was told that the only fix to this problem was the DMax function which I am unfortunatley not familar with. Can anyboby explain how to go abouit this and where it should be used.

Hi KevW I have just joined this forum but I think your problem is solvable using DMax.
Something you need to consider is does the field with the autonumber mean anything to the users. Then use the following code on the Onload event of the form the users are using to enter data.
Before anything else open the table in Design view and change the field from autonumber to number (long integer)
Assuming the field containing the autonumber is named txtID and the table updated is tblRegistry(NB you will have to use the field names and table names as they are in your database.) Then use

Me![txtID] = Format(DMax("[txtID]", "[tblRegistry]") + 1)

For this to work you will have to ensure that the first record has a number in it eg 1 or 001 depending on the format you want to use
Try and see

 

[RuralGuy]

Kev,
Here's an article by MVP Allen Browne that you may find useful.

 

[KevW]

DMax Function

I now have this code below for the onload event of the registration form,

Private Sub Form_Load()

Me![WORK REFERENCE NUMBER] = Format(DMax("[Work Reference Number]", "[central]") + 1)

End Sub


The Work Reference Number should be the autonumber and central is the table name. ( I had no part in the naming of either of these unfrotunately and am not sure as to whether or not they can be changed.)

I now try loading the form and get the a Run Time error 2448, obviously I am doing something wrong but am not sure as to what !

 

[coyote]

DMax

Check that the fields DataType is set to Number not Auto-Number. Also you have to delete any relationship to this field first then reset it later.

 

[Pat Hartman]

An autonumber that reissues a duplicate value is a sign of corruption. Start by creating a new database and importing all objects except the broken table. From the old database, export the bad table to a CSV file. In the new database, create a new table to hold the imported data. Make sure that the PK is defined as an autonumber. Then link to the .CSV file and create a query that appends the records to the new table. The final step will be to redefine any referential integrity constraints.

 

[KevW]

Thanks for that I can get the autonumbering working using Pat's solution and have now been given the task of implementing it.

 

[gemma-the-husky]

i think you should definitely take burrcm's advice

copy the data database beofre trying anything else

although access dbs "appear" to contain tables etc, they are really just single files, with the rdb manager handling the data within them. (who knows exactly how? - we just see the useful results) perhaps some internal pointers get corrupted, and this will not be fixable by normal means

eg - i had a database last week that stopped working properly. read queries worked, but update queries failed with a strange message. repairing the database fixed it. If repair fails, try opening a new blank database, and reimporting all the objects into the new database. this may work.



if repair