I ‘m working on a “Returns” logging DB for a new customer. The problem is I need consecutive “Return Numbers” (or Batch numbers). I am a lazy programmer, and I have always used the auto number provided by the programme, I know I’m not alone in this “Bad practice” but I am now forced to change my ways because when a new entry is aborted a number is missed out of the sequence.

One reason I have continued using auto number is that I know it’s not a straight forward problem to solve as there are Issues.

From:How To Implement Multi-user Custom Counters in DAO 3.5 (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q191/2/53.asp&NoWebContent=1)

Extract
Jet has a read-cache and lazy writes, you can get duplicate values in your custom counter field if two applications add records in less time than it takes the cache to refresh

Is this still a problem, or has it been resolved in A2K?

This MS article:- (http://support.microsoft.com/?kbid=210194)

- looks the business so I am considering this approach.

I would be grateful for any observations or comments you may have Good, bad or even rude!

PS: One reason I stuck with auto numbers is that they can’t be changed by a stupid or malicious person. However I intend to continue using an auto number primary key for linking the data together which should negate this problem.

This is a very good article on the subject:-
http://www.applecore99.com/tbl/tbl012.asp

'The problem is I need consecutive “Return Numbers” (or Batch numbers). '

Are you still looking for suggestions on this issue?

Are you still looking for suggestions on this issue?


Yes please! I need to be confident in the method I use. I’m at the learning stage, with little experience in this particular area. I know there are serious issues with the auto number facility in Access so I am reluctant to use the first solution I find.

I know this is going to sound extremely simplistic and others may shoot it down, but...

Could you do a field that would look up the largest existing number in the field in the existing records, add 1 to it and use that number as the 'Return' number? Basically a dmax() plus 1.

???

ken

Ken, sometimes simple is good. Like in this case. DMax + 1 is probably the preferred solution, even though it means a scan of something will occur.

Ken, sometimes simple is good. Like in this case. DMax + 1 is probably the preferred solution, even though it means a scan of something will occur.

You joker - :rolleyes: