I have a form in which the primary key is an autonumber. maybe this is a mistake. If I start a form the automnumber is assigned, if I delete or cancel the form will I be able to reuse that number or will autonumber skip to the next number? Is there a way to reuse that number? I am dealing with ISO standards here. Thank you to anyone who helps out.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Question on Using AutoNumber 1
- Thread starter quest4
- Start date
Autonumbers are assigned by Access and cannot be re-used. They should be used for only one purpose: as relationships and foreign keys between tables. They should never, never be seen by the end user nor have any meaning as data.
If you need to ensure a continuous string of numbers with no gaps you need to create your own system. Write a function to return the next available number and use it when inserting a new record. Either query the table for the next number or (better) store the last used number somewhere convenient, like in your own 'system' table. Be careful to lock that system table while retrieving the 'next number' to avoid multi-user collisions.
Neal
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
If you need to ensure a continuous string of numbers with no gaps you need to create your own system. Write a function to return the next available number and use it when inserting a new record. Either query the table for the next number or (better) store the last used number somewhere convenient, like in your own 'system' table. Be careful to lock that system table while retrieving the 'next number' to avoid multi-user collisions.
Neal
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
- Thread starter
- #3
Thank you 930driver for the response. I understand what you are saying about autonumber, that is kind of what I was worried about. I am still a rusty on my function writing, would you possibly have a simple example of that type of function so that I could play with it and modify it. Thank you very much for the response.
MichaelRed
Programmer
this is -perhaps- NOT the best exercise to "de-oxidize" your skills on. As a STRICTLY simlge (personal) db, sequential values are somewhat trivial. In a multiuser situation (multi-data entry at least), it is at least hazzardous and potentially carrear altering. If you are seriously intent on the course, searhc these fora and hte faqs re "automunber". There are numerous discussions and crossreferences to posts and faqs discussing the issue.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
-
1
- #5
MichaelRed
Programmer
but, again, the technique is for the STRICTLY "single user" app / db. In "Pofessional" (and 'we' ARE all 'Professionals' here -aren't 'we'?) situations, this is seldom (never? the case. Do the search. Do the readiing. Understand the concepts. Apply liberaly.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
quest4
I may a day late and a dollar short weighing in on this one, but "ISO" perked my ears.
You can still use autonumber if you wish but just use the numbers internally, and don't display it on the form or report.
Use a tblCtrlNo (control number table) to store your control number or numbers. Lots of ways to approach this -- for example, use a text field for the primary key and text or numeric field for the control number.
Example:
PKey CtlNo or CtlNo
CTLINV 1000 A2000
CTLPAY 2001 R2001
CTLPO 3002 P3002
You get the picture.
Have ONE central module that updates the control record.
This module advances the control number and updates the control record and returns the applicable control number to the calling module. The neat thing is that you can increment the control number by 1 or 10 or the next alpha character. Hint: On large systems, use record locking to prevent collisions.
Then, from your form that performas the update, call the central module AFTER creating the initial record, i.e., use the after insert event. (example "mdlUpdateCtl CLTInv, strInvNo"
Grab the next control number, and update the trasaction record with the control number.
Word of caution. Since the control number is stored in a table, you should probably use some safe guards, ie security, to prevent a person from manipulating the data. For example, a little hanky-panky in payroll or A/P.
I am sure there are vairation on this. And already some excellent ideas have been presented.
Richard
I may a day late and a dollar short weighing in on this one, but "ISO" perked my ears.
You can still use autonumber if you wish but just use the numbers internally, and don't display it on the form or report.
Use a tblCtrlNo (control number table) to store your control number or numbers. Lots of ways to approach this -- for example, use a text field for the primary key and text or numeric field for the control number.
Example:
PKey CtlNo or CtlNo
CTLINV 1000 A2000
CTLPAY 2001 R2001
CTLPO 3002 P3002
You get the picture.
Have ONE central module that updates the control record.
This module advances the control number and updates the control record and returns the applicable control number to the calling module. The neat thing is that you can increment the control number by 1 or 10 or the next alpha character. Hint: On large systems, use record locking to prevent collisions.
Then, from your form that performas the update, call the central module AFTER creating the initial record, i.e., use the after insert event. (example "mdlUpdateCtl CLTInv, strInvNo"
Grab the next control number, and update the trasaction record with the control number.Word of caution. Since the control number is stored in a table, you should probably use some safe guards, ie security, to prevent a person from manipulating the data. For example, a little hanky-panky in payroll or A/P.
I am sure there are vairation on this. And already some excellent ideas have been presented.
Richard
MichaelRed
Programmer
In my experience, "Autonumber" is not suitable for multiuser dataa entry apps. Ms. A. Does not (at least appear to) lock the record (or any thing about the value), so concurrency conflicts can (therefore DO) arise.
Repeatig the theme, search these fora and particularly the faqs for "Autonumber". There are quite a few references and discussoins on the perils and pitfalls and at least one 'soloution'.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
Repeatig the theme, search these fora and particularly the faqs for "Autonumber". There are quite a few references and discussoins on the perils and pitfalls and at least one 'soloution'.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
- Thread starter
- #9
Thank you for the responses, willr and MichaelRed. I have the mph1 example run now and it appears to do everything that I need it to do, with one minor exception, I can not delete a working ECN and have it reuse the number. So we decided to just complete them or cancell them, and then move them to an archive dbase. Thanks again for the suggestions, and I have found out that what MichaelRed is saying is very true, so thank again.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question