Access Autonumber Skipping One

Can any experts help me on the attached Access db?  It is skipping an ID when adding to Jobs table.  I tried to look for any uneccessary refresh and requery, but that isn't it.  Thank you so much for any help!!  
You will need to rename the file extension to .mdb.  BTW I'm using Access 2003.  Thanks again.
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
< that is the current relationship structure.  Any suggestions?>

Only that there are about 14 tables, only 5 of which appear to be related.

But again, post a new question to get more Experts involved.
What exactly do we do to see the problem?
Jeffrey CoachmanMIS LiasonCommented:
Whenever you submit a sample, please provide Full instructions for us to see the issue please.

After figuring out on my own what was happening, I was able to add new records to the Jobs table with no skips in the Autonumber.

Please remember that once a number is used in an autonumber field, and then that number is deleted, that number can not be used again.

This is probably the "skip" you are referring to.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

kbrianAuthor Commented:
Thanks for getting back to me.  I'm sorry for not providing enough info.  
To understand better, please click on "View Job Reports" on the main screen.  Then from the lists, select a company, location and contact.  Next click "Add Job" and fill in at least the Job number(just any text).  Next click "Save Record".  The job info is added to the Jobs table, but it does exactly skip every other ID in the sequence!  The first 18 or so are from importing existing data.  This is driving me nuts trying to figure it out!  If anyone can give me some insight, I would really appreciate it.
Thanks in advance!!!  
Jeffrey CoachmanMIS LiasonCommented:

You keep saying "ID"
Can you clearly state which ID in which table?

I am having trouble following your code logic.

You open the form in Add mode, then you update some data.
Then you have a "Save" button that does not save.
It runs two update queries and runs a macro.

That is where I stopped.

When you open a form in add mode the frist data you put into the form generataes the next autonumber for the Recordsource.
If one of your Update queries or something in the macro "Updates" this in some way, then this may be why you get the "Skip".

FWIW, records in Access are updated automatically, generally there is no need to "Save" the record explitly.

You must examine both of these update queries and tha macro to figure out:
Why they are there
What they are doing
Are they needed
can they be moved
can they be changed.

Perhaps, if peter57r is still with us, he can decipher this a little more...

Sorry - I'm out...
I started to look at it but it looks like it would take (me) some hours to understand what is going on with all the coded queries , repeated data values, and codes and descriptions stored on the same table and I don't have such chunks of time available at the moment.

My instinct is that most of it is unnecessary - i can't even understand why the job record is not just saved instead of being used to feed an append query.  But I don't know the requirements so I might be barking up the wrong tree.

kbrianAuthor Commented:
Can you please help me out a little more on the following statements you sent?
1) "After figuring out on my own what was happening, I was able to add new records to the Jobs table with no skips in the Autonumber."
2) "Please remember that once a number is used in an autonumber field, and then that number is deleted, that number can not be used again."
My question to you is where am I deleting the autonumber? I don't understand.
kbrianAuthor Commented:
To Anyone:
I know the database I posted contains bad logic!  It just seems to me that from at least one of the posts I read that it can work with some tweaking , so if anyone can help, I will Greatly Appreciate it!!  Thanks to anyone that can help me further on this in any way.  I have really tried, and I think I got into this way over my head.
Jeffrey CoachmanMIS LiasonCommented:

The issue here is that you have a lot going on in the database.

Are you the original author?

As Peter states, this will take an expert an hour or so just to figure out what is happening,

Then perhap a dozen or so "Back and forths" with you to explain the Requirments, purposes and reasoning behind this application.

In other words, we would have to be intimately familiar with your application.

Most experts here are paid to provide this type of service, so it is hard to justify doing it here for free.

Might I suggest that you start from the very beginning?

First post a question regarding the overall databse project and ask for basic advice.

Then play it by ear from there.

Post one question at a time.


kbrianAuthor Commented:
Thanks for your reply and suggestion.  By 'start at the beginning',  do you mean that you need me to post the whole database? Not a problem, it's not big, and not complicated (except to me). Please let me know. I'm just not experienced in coding, as you can see.  I am the only one that actually coded with help here and there and Changes~ Sorry, I am a Beginner!!!
I'm not sure about this, but I think if I can solve the issue I have right now with the JobID in the database that I posted, I can solve a lot of problems.  Thank you, Jeff and all Experts!
Jeffrey CoachmanMIS LiasonCommented:

What I mean is to first post a question regarding the overall purpose of the database.
Then ask for advice on how to start buliding it.

"I need to make a database that Manages/Tracks _______________."
"I need advice on how to start bulding it"

Keep it simple, one step at a time.

Then play it by ear.

Again, if you post the db in it's current form, Experts will not want to "Figure it out".


kbrianAuthor Commented:
The database tracks jobs and customer information.  Needs to track uploads of files associated with the job, location and customer and customer contact.  I think it's too late to start over though - due a couple of months ago.

Jeffrey CoachmanMIS LiasonCommented:
You may not have to start over.

The hints you may get may help you to simplify some things.

At least you will be getting help.

Then the only other option I can see (if this is crunch time) is you partnering with a Databse consultant to help you with this.
Or, buy an Off the Shelf product like:
Or, investigate using Microsoft Project.

kbrianAuthor Commented:
I guess I need to start with relationships then.  When I get home from work (about 5 hrs from now) I will post my relationship structure.  Buying software is not an option at this point.  Acually, if you still have the database you downloaded, that is the current relationship structure.  Any suggestions?

Can you tell me how to get the jobID to quit skipping though?  I want to get that part working and send it off for them to evaluate, then go from there.
peter57rConnect With a Mentor Commented:
I am just talking speculatively ...
If you have a bound data entry form, as soon as you start to enter data you are generating a new autonumber ID in the unsaved form.
If you then post the same data via an append query to the table you are generating in the table a second autonumber id one greater than the one in the form.
If , somehow, you don't save the record that you can see in the form, then the autonumber that was generated in the form is used up. SO you would get alternate numbers in the table.

I don't know whether this is the case here, but I guess you can see what I mean.
kbrianAuthor Commented:
Thanks to everyone for all suggestions and comments.  You guys have helped me greatly. Here's what I did to solve the "skipping".  I created a field in my problem table and bound a control on the form to it. Then I set the Control's default value to: =nz(DMax("JobSeq", "Jobs"), 0) + 1.  So now I can control when it increments and it's always in sequential order.  

I'm still going to normalize this database and have already started - I need to get rid of the spaghetti code in case any more changes are needed!

Thanks again!

kbrianAuthor Commented:
I really appreciate the time it took you guys.  What I posted did it and I appreciate your suggestions also.  I have really learned a lesson on communication and planning!!  Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.