Advertisement

02.19.2008 at 02:29AM PST, ID: 23173888
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Set initial auto number value
Tags: Access Mdb
Hi
I have found so many references to MS Access help about how tables with NO data can have the auto number start value modified, and if you have records then you have to use an append query bla bla.

However no one (or Access help) seems to ever actually say how to set the number when you have no records. I don't have any yet, it's a new table andI just want to start the niumbers from 020811 as it's continuing from an old invoicing system.

Maybe ths is so easy I'm missing the point, just can't find a simple way to set the number....

Thanks
Start your free trial to view this solution
Question Stats
Zone: Microsoft
Question Asked By: swordfishsoup
Solution Provided By: ericpete
Participating Experts: 6
Solution Grade: A
Views: 152
Translate:
Loading Advertisement...
02.19.2008 at 03:53AM PST, ID: 20927401

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.19.2008 at 03:56AM PST, ID: 20927413

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.19.2008 at 04:06AM PST, ID: 20927465

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.19.2008 at 05:01AM PST, ID: 20927811

Rank: Wizard

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.19.2008 at 06:48AM PST, ID: 20928767

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.19.2008 at 07:39AM PST, ID: 20929360

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.19.2008 at 08:32AM PST, ID: 20929916

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
02.19.2008 at 03:53AM PST, ID: 20927401

Rank: Guru

have you checked this explanation: http://www.mvps.org/access/tables/tbl0005.htm (its title is "Have Autonumber field start from a value different from 1").


--bluelizard


 
02.19.2008 at 03:56AM PST, ID: 20927413

Rank: Guru

addendum to my post: the method on that site works for empty tables, like this:

1) create the table, leave it empty
2) create a *temporary* table with 1 record with the desired number-1
3) append the contents of the *temporary* table to your table (this will fill 1 record in your table)
4) delete that record again
5) delete the *temporary* table

this leaves you with an empty table that will start numbering from where you decided.


--bluelizard
 
02.19.2008 at 04:06AM PST, ID: 20927465
Thanks I'll give that a go now

Dave
 
02.19.2008 at 05:01AM PST, ID: 20927811

Rank: Wizard

Advice

You'd better keep 1 record in the table if you follow the previous explanation.  If you compact a database, all autonumbers reset to the lowest possible value (1 if no records exist).
Otherwise, there is no controlling autonumbers.  These have a very simple and specific purpose - to assign a unique number (primary key) to a record - and that's it.
Many folks have tried to use this autonumber for a variety of purposes and each purpose causes issues.

Scott C
 
02.19.2008 at 06:48AM PST, ID: 20928767

Rank: Genius

In Access, the above experts are correct in that the only way to pull this off is by manually inserting 'dummy' records, then deleting them.

In SQL SERVER, it's DBCC CHECKIDENT (tablename, RESEED, your_start_number)

Sorry dude.
Jim
 
02.19.2008 at 07:39AM PST, ID: 20929360
I have an ex-boss who used to keep a CSV file around to import; it has the same effect, and it allowed her to simply change it to whatever starting number she wanted -- a little copying and pasting of lines.

ep
Accepted Solution
 
02.19.2008 at 08:32AM PST, ID: 20929916

Rank: Genius

Here you go - put this code in a standard module, then you can call it from the Immediate window.  Change the Start and Increment values to your liking.

To set your own starting point and incrementing value:
Public Function mResetAutoNumber() As String

    Dim sSQL As String
    Dim lStartVal As Long, lIncrement As Long
    lStartVal = 20811 ' *** or whatever
    lIncrement = 1   ' *** or whatever
    sSQL = "ALTER TABLE Table4 ALTER COLUMN AN COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"

End Function

*Note - there will not be any leading zero.

mx
 
 
02.19.2008 at 08:59AM PST, ID: 20930185
"swordfishsoup"

Did you try my post?  It works!

mx
 
 
02.19.2008 at 08:59AM PST, ID: 20930189
So many right answers...Thanks.

Went for what was the easiest approach and copied and pasted data to make a huge excel column then pasted into access, and deleted.

Thanks for all your efforts, but being lazy went the easy route

Dave
 
 
02.19.2008 at 09:02AM PST, ID: 20930216
" it's a new table andI just want to start the niumbers from 020811 "
"However no one (or Access help) seems to ever actually say how to set the number when you have no records. "

That is *exactly* what to code I posted will do.  A 30 second operation one time.

mx
 
 
02.19.2008 at 10:16AM PST, ID: 20930969
D..MX,

If you want to ask the Mods to reopen this and split the points, I have no objection. My ex-boss actually did use an Excel file until she decided to build one database starting at 100001 -- at which point she ran into the limitation of the number of rows in an Excel file (it was a while ago).

ep
 
 
02.19.2008 at 10:19AM PST, ID: 20931001
np
 
 
02.19.2008 at 10:26AM PST, ID: 20931066
Oh dear...I didn't mean to offend anybody.......

DMX - there were other valid answers 'before' your equally valid answer. Would you like it to be last in gets the points, or points going to the answer I actually chose to use as it was a simple solution for someone of my limited experience. I haven't encountered  >>>put this code in a standard module, then you can call it from the Immediate window>>> double dutch to me I'm afraid. I'm sure I would have figured it out though.

So the points went to Mr Shortcut ep thank you, just right for me and I've moved on.

Hope you don't mind

Thanks
 
 
02.19.2008 at 10:32AM PST, ID: 20931136
everyone chill ...there is no problem here.

mx

 
 
02.19.2008 at 01:17PM PST, ID: 20932680
ahem... hand out your points the way you want, but when i saw what answer you accepted (without any splits), i just thought, "you gotta be kidding...": the method that ericpete proposed is good, no doubt about that, but: in your question you say that things are too complex for you, and then you accept an answer that hardly explains anything...?  imagine: if that post had been the only post, would that really have been a valid answer for you?  i doubt it --- i think that it was a combination of several answers that gave you (a) the basic idea of how to modify autonumber initial values and (b) the simplest way how to apply this idea: for me, the classical situation for a point split.

well, never mind --- i may be wrong, of course.
 
 
02.20.2008 at 01:47AM PST, ID: 20936246
Oh Dear.....
I'm replying to this Mr Lizard, purely as I'm delaying getting on with work for the day and this has become an interesting if not rather bizarre discussion:) So, in the style of a back street courthouse....
I refer to my initial question that says I have 'already' found loads of info - (on EE and Google and Access help) about various methods of achieving this (specifically your solution which crops up quite often). But in the Acess help file before this explanation it states that if no records exist you can 'just change the starting value' that's all - doesn't say how - implies it's a simple number change. It then leads on to say that if you DO have records and then lists the append query example. My question was to find this somewhat mysteriously implied ' just change the number method' when no records exist.

I had assumed I could just type in this number to my record less table somewhere and that would be that. That's why I asked. The closest to this simple way for me was the answer I accepted.

Hope that makes some sense.

If your still feeling cheated of your points then I'll happily refer this to a Mod

Have a good day

Dave
 
 
02.20.2008 at 08:38AM PST, ID: 20939407
I kind of have to agree with BL ... not to take anything away from ericpete, but I don't exactly see how that is a solution either.  What I posted gives you pretty much complete control over the AN starting value and increment, irrespective of what the Help file says.

mx
 
 
02.20.2008 at 08:40AM PST, ID: 20939444
I'm starting to wish I'd never asked!
 
 
02.20.2008 at 08:43AM PST, ID: 20939488
It's all a moot point at this juncture.  Lets all let it go now :-)

mx
 
 
02.20.2008 at 11:39AM PST, ID: 20941044
http:/Q_23178859.html

Now I know why I don't answer questions. <unsubscribe>
 
 
02.20.2008 at 06:12PM PST, ID: 20944360
To me, this one is pretty simple.
The Asker posted the question, evaluated the suggestions made and then picked one that worked.
End of story.

The answer stays as is.

If anyone wants to comment about my (non)decision write me at: vee_mod@(you know the rest) and we'll discuss it - but not here.



Vee_Mod
Experts Exchange Moderator
 
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628