Compact/Repair DB Util not updating auto numbers (1 Viewer)

smjohns

Registered User.
Local time
Today, 13:23
Joined
Mar 15, 2004
Messages
20
Hi,

I am still very new to MS Access 2000 but have come across a problem which I do not know how to resolve, even if it is possible.

I have set up my DB and added all the relationships. A couple of the tables have an autogenerated number as one of fields and this is the PK on each of them.

I noticed a problem in that when creating a record, if you exit/escape out of the new record creation before adding the record, the auto generated number is lost forever. I found out that if you use the DB Utility, compact and repair, this will re-enable all these lost numbers and seems to work on most tables.

I have noticed that this does not work on two of my tables with this type of field. I have checked the records added and there are no links to other tables, so there should not be any problems, also it works on other tables with similar autogenerated number PK's. I have tried deleting the column and recreating it, and although this renumbers the existing records correctly, as soon as you cancel the entry of a new record, that number is lost and compacting will not bring it back.

Is this a known issue on Access? Is there any other setting I should be looking for? Is there any other way of updating this autonumber?

Apologies if this question has been asked before. I have had a quick look on the forums, but could not locate the answer.

Kind Regards

Simon
 

neileg

AWF VIP
Local time
Today, 13:23
Joined
Dec 4, 2002
Messages
5,975
The bottom line is that if sequential numbering is important to you, don't use autonumber. As you have discovered there is no guarantee. Autonumbers are best suited to indexes that the users never see. If you want sequential numbers, then use autonumbers as the idexes, and calculate a user visible number probably based on a DSum(). Search these forums for more on this.
 

scouser

Registered User.
Local time
Today, 13:23
Joined
Nov 25, 2003
Messages
767
I Use

For my enquiry / order numbers I was usung autonumber but have changed to:
Code:
Private Sub Form_Current()
If Me.NewRecord Then
        Me!EnquiryID.DefaultValue = Nz(DMax("[EnquiryID]", "tblEnquiries"), 999) + 1
End If
End Sub

Stick the above in the form "Current" event and change control names to suit.
Remember to delete your relationship before amending your table!!
Hope this is what you were after.
:D
 

neileg

AWF VIP
Local time
Today, 13:23
Joined
Dec 4, 2002
Messages
5,975
Scouser's code will give you a sequence starting at 1000. Change the 999 in his code to give a sequence starting at one more than the value entered here.
 

smjohns

Registered User.
Local time
Today, 13:23
Joined
Mar 15, 2004
Messages
20
Hi,

Thanks guys, I will have a play and let you know if I am still stuck.

Cheers

Simon
 

smjohns

Registered User.
Local time
Today, 13:23
Joined
Mar 15, 2004
Messages
20
Fantastic. I have now replaced all my forward facing auto generated ID's with just number fields and added your code.

Rather than adding it as a default number when you enter the create new record screen, I have added the code to just before the save action is performed. This means that if two users access the screen at the same time, the unique number will be created when each of them attempt to save the record so that they will not have duplicate reference number errors.

Thanks again for all your help.

Cheers

Si
 

scouser

Registered User.
Local time
Today, 13:23
Joined
Nov 25, 2003
Messages
767
Help

Finally I have helped somebody! Ha!
Pleased to here you have got it all working.
:D
 

Users who are viewing this thread

Top Bottom