Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-15-2004, 06:02 AM   #1
smjohns
Registered User
 
Join Date: Mar 2004
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
smjohns is on a distinguished road
Compact/Repair DB Util not updating auto numbers

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

smjohns is offline   Reply With Quote
Old 03-15-2004, 06:45 AM   #2
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
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.
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 03-15-2004, 07:37 AM   #3
scouser
Newly Registered User
 
scouser's Avatar
 
Join Date: Nov 2003
Location: England
Posts: 767
Thanks: 12
Thanked 2 Times in 1 Post
scouser
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.

scouser is offline   Reply With Quote
Old 03-15-2004, 07:44 AM   #4
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
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.
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 03-16-2004, 01:45 AM   #5
smjohns
Registered User
 
Join Date: Mar 2004
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
smjohns is on a distinguished road
Hi,

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

Cheers

Simon
smjohns is offline   Reply With Quote
Old 03-16-2004, 02:41 AM   #6
smjohns
Registered User
 
Join Date: Mar 2004
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
smjohns is on a distinguished road
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
smjohns is offline   Reply With Quote
Old 03-16-2004, 03:02 AM   #7
scouser
Newly Registered User
 
scouser's Avatar
 
Join Date: Nov 2003
Location: England
Posts: 767
Thanks: 12
Thanked 2 Times in 1 Post
scouser
Help

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


scouser is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 06:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World