Access Autonumber Skipping One
Hello,
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.
JobTracking.txt
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.
JobTracking.txt
peter57r
What exactly do we do to see the problem?
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.
JeffCoachman
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.
JeffCoachman
ASKER
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!!!
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!!!
kbrian,
You keep saying "ID"
Can you clearly state which ID in which table?
Thanks
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...
JeffCoachman
You keep saying "ID"
Can you clearly state which ID in which table?
Thanks
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...
JeffCoachman
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.
Sorry.
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.
Sorry.
ASKER
boag2000:
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.
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.
ASKER
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.
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.
kbrian,
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.
;-)
JeffCoachman
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.
;-)
JeffCoachman
ASKER
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!
kbrian
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!
kbrian
No,
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".
;-)
Jeff
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".
;-)
Jeff
ASKER
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.
Thanks,
Thanks,
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:
http://www.cbsoftampabay.com/job-tracking.html
Or, investigate using Microsoft Project.
http://office.microsoft.com/en-us/project/default.aspx
JeffCoachman
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:
http://www.cbsoftampabay.com/job-tracking.html
Or, investigate using Microsoft Project.
http://office.microsoft.com/en-us/project/default.aspx
JeffCoachman
ASKER
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.
Thanks!
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.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
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!