Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Autonumber jumps in Access 97

Posted on 2004-10-13
8
Medium Priority
?
248 Views
Last Modified: 2010-07-27
Im writing some code in excel to append data from a spreadsheet into an acces 97 database.
All is going well except the autonumbering field is jumping quite erratically for no apparent reason. This is quite a big problem and I need to stop it happening.

Here is the part of code that adds the data:

 For i = 1 To UBound(RowList)
JobsRS.AddNew

    If dataArray(i, 1) <> "" Then JobsRS(1) = dataArray(i, 1)
    If dataArray(i, 2) <> "" Then JobsRS(3) = dataArray(i, 2)
    If dataArray(i, 3) <> "" Then JobsRS(4) = dataArray(i, 3)
    If dataArray(i, 4) <> "" Then JobsRS(5) = dataArray(i, 4)
    If dataArray(i, 5) <> "" Then JobsRS(7) = dataArray(i, 5)
    If dataArray(i, 6) <> "" Then JobsRS(8) = dataArray(i, 6)
    If dataArray(i, 7) <> "" Then JobsRS(11) = dataArray(i, 7)
    If dataArray(i, 8) <> "" Then JobsRS(24) = dataArray(i, 8)
    If dataArray(i, 9) <> "" Then JobsRS(42) = dataArray(i, 9)
    If dataArray(i, 10) <> "" Then JobsRS(14) = dataArray(i, 10)
    If dataArray(i, 11) <> "" Then JobsRS(15) = dataArray(i, 11)

JobsRS.Update
Next i

any Ideas?
0
Comment
Question by:cwhitby
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12299410
Your auto-number field in Access is set to Increment and not Random, right?
0
 

Author Comment

by:cwhitby
ID: 12299424
Set to Increment.

It doesnt happen every time, just occasionaly.
0
 
LVL 12

Assisted Solution

by:pique_tech
pique_tech earned 300 total points
ID: 12299475
I don't mean to be pedantic, but I have a history of getting all tripped up over the silliest little things...

Is anyone else doing anything with the same table at the same time that could interfere with the autonumbering?

More fundamentally, what is the importance of the jumps?  I understand wanting to understand the behavior, but it's generally not good to base too much business or process decision making on autonumber fields.  It's better to use record counts or other more controllable things if at all possible.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 300 total points
ID: 12299511
"to base too much business or process decision making on autonumber fields"

i'd agree 100% with that ..all my order/invoice/customer id's are based on formulae
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 900 total points
ID: 12299565
The only thing really consistent about auto-numbering is that it can behave erratically for very little reason.  :)

When you say the autonumber field is jumping, by how much is it jumping?  If it is missing 1 or 2 numbers, your most likely cause is someone/thing adding a record, then canceling the add.  This causes Access to 'reserve'  the auto-number for the newly added record.  Once the record is canceled, that auto-number is retired, and Access will not come back to it unless you reset the seed on the table.  Note that this does have to be because of other users...you could be doing this to yourself as you debug code.  In your example, if you stop the code anywhere between the AddNew and the Update, you will 'lose' your auto-number.  If you can run it straight through twice and get contiguous auto-numbers, this is likely your problem.  This is a 'feature' of Access, meaning Microsoft claims this behavior to be by design.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12299573
Note that this does have to be because of other users...
                   ^ NOT

sigh...still waking up.
0
 

Author Comment

by:cwhitby
ID: 12299733
No one else is adding to the db, its a copy of the main one I have set up for testing purposes. As regards using autonumbering for business practice, I agree, however this is a system thats been running for a number of years, using the autonumer for tracking purposes - that’s why its critical that I stop the big jumps (sometimes over 1000).

The comment about debug mode may have some merit, I have hit a few problems with data types and validation etc. I will investigate this theory further
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question