Incrament serial number up or down using txt file and directory search

Hi Everyone,
Any help on this issue would be greatly appreciated, even if it is just to tell me I'm on completely the wrong lines. I have posted on this issue before; http://www.excelforum.com/excel-prog...ml#post3513111

What I am trying to achieve is generating a unique serial number that can not be duplicated.
I want to be able to open a new document from an excel template and the serial number be generated from a text file (got that bit down). Then I want a loop to be run to check the folder the file was saved in to check if a file exists with the same serial number (the file will be saved with the serial number as a name). If a file is found the number stored in the txt file is decrease by -1 then that result is displayed. If no file is found then the result stays the same and that is displayed (when my current loop is run it displays the msg box to say "nothing found" then the serial number disappears).
Sorry if this an incoherent run down, trying my best.

Please Login or Register  to view this content.

Any help or guidance on this issue would be amazing!

Have you considered using Date & Time as a component of the serial number? Last time I checked these values don't repeat

Tony

Perhaps a little less cryptic:

Please Login or Register  to view this content.

Thank you very much for the replies. The issue is, which i should have said in my origianl post is that the serial number must be in a specific format which is 00000 and start at 25000 as it needs to follow on seamlessly from the existing system.

Once more, with less flippancy!

Sounds like what you've got is the classic Resource Locking Problem. Your approach of decrementing a value somewhere is reasonable, except you want the value to increase. How about this:

1) Define a 'hidden' file (may need to be the only file in a 'hidden' folder'.
2) Put a password on it
3) Save a single value in the file (your seed value)
4) GetTheNextSerialNumber algorithm

if we can find the file
open the file and get the value
purge the file
increment the value from the file
write it back with the same constraints (1..3) as above
return new serial number
else
return Resource Locked - try again
endif

The first two constraints makeit very hard to accidentally screw-up the contents of the file - only the routine that knows how to find it will be able to do so.

Purging the file, once you've opened it allows exclusivity - if we can't find the file we report failure, and ask to wait. Then, by recreating the file we allow the next user to get a different serial number.

Latency between uses is Open/Read/Purge/Build, which unless it's a high-speed batch process should be enough time to give you the atomicity that you need.

HTH

Tony

Thank you very much for the response. I have posted this question again as I thought this one had been abandoned.
http://www.excelforum.com/excel-prog...ile-found.html

The biggest problem is not the writing to the file by more than one person. My biggest issue is someone closing the xls file without saving it, therefore the serial will increase again when a new version of the document is opened. This become an issue as one of the numbers has been VOID unintentionally which will be an issue for Accounts as there will be holes in the sequencial numbering.

This is why I'm trying to preform a search on the directory to see if a file exists with a matching serial number to the one just generated.

Hi Beth

So here's a slightly modified approach. The problems you describe are:

1) Users (note the plural) who don't use the supplied value (let's call them wasted values)
2) Need to maintain a gapless sequence

To these I'm going to add a couple of things that we need to consider:
3) Concurrency - what happens when more than one person uses the system at the same time, and
4) Latency - how fast we can handle requests for this control value, even if we end up wasting it!

First off, because you have identified users then the concurrency and latency problems are real, and must be dealt with. Neither of them is very difficult, but you do have to consider them. If more than one user can access your system, it is only a question of when not whether a collision will occur. So we need to figure out the maximum number of users (we assume, in a somewhat perverse manner, that they will all try and access the system at the same time), and how long we are prepared to make them wait while we service "all the others". Let's assume that we have 10 users (YMMV), and we are prepared to wait up to 3 seconds for the system to respond. (Bear in mind that this is a one-time part of the usage - and, with any luck, this planning won't be needed for most of the time).

Those figures mean that we have to be able to create a value in less than 2 divided by 10 (= 1/5) seconds. In general the processing must occur in

Please Login or Register  to view this content.

My earlier suggestion was to open a file, get a value, hide the file until we have saved the new seed value

I think we can meet both of your needs with a slight modification:
Look for a specific file
Rename it to a random name
Work with the file
Rename it back to the known name

This uses the concurrency management of the file system to do the really tricky stuff, ad gives us a lot more freedom in how we process the file.

If we look for the file and can't find it, we need to back off and wait for a while, before trying again. After a certain number of tries (MaxTries) we give up. This is basically the algorithm used for Ethernet, and that's an overwhelmingly successful way of handling both issues. I'm happy to copy it!

So our high level code looks like this:

Please Login or Register  to view this content.

If we have no problems getting the control value, we get in and out very fast, with only the overhead of one attempt to manage the file (which has so far not yet made an appearance). Then we can wait a short while, up to MaxTries (10 in this case) times before giving up.

MakeAttempt is the main procedure that will do all the file processing, and BackOff takes care of waiting and - if necessary - providing some sort of visual feedback.

Here's what BackOff lookslike, at least as a starting point:

Please Login or Register  to view this content.

Note that WaitTime is a global variable - we may want to set its value in some sort of initialization routine, rather than every time we hit backoff.

Make the attempt implements the rest of the algorithm:

Please Login or Register  to view this content.

So - there we have the outline, in four procedures. They're all pretty skeletal as yet, but I'll get back to you later with some more fleshed out code. Along the way, you might like to think of a file-type that can store a SeedValue in one place, a whole list of possibly WastedValues in another, and a set of ActiveValues (which may yet turn out to be wasted) in yet a third place. (Hint, it's near and dear to the users of this Forum!

Talk with you soon!

Tony

I hope you had a good weekend.Thanks very much for the code. I will have a look at fleshing it out and implementing the code today (hopefully).
Thanks
Beth.

Hi Beth

I think my earlier reply got lost (can't find it anywhere) so I'm resending it!

Here is a more complete solution with several user accessible rotines:
UIGetControlValue
UIOnSave
UIOnClose

UIReset
UISetSeedValue

and one which is not working yet!
UIInstallControlSystem.

They all attempt to get control of a resource (the attached CTL.XLSX workbook), and then manipulate it.

The code listing is too long, so I'v also attached a listing that I created as a Word document.

HTH

Tony

Hi -

try this one and let me know;

Please Login or Register  to view this content.

event

Thank you very much both for the replies. I will look them over and come back to you later. Thank you again. Beth