Process to Allow Operation to Complete (1 Viewer)

[Paul Lemelin]

A few years ago I wrote some VBA code for my company in Access. The code issues serial numbers of a particular format, 1234AA, 1235AA, 1236AA...9999ZZ. This is a shared database with just a few (5-8) users in total, and each department is issued their own set of alpha suffixes. The user enters some information about the product, including quantity, and Access runs some code to store the pertinent information and spit out 1 or more serial numbers. If there is a quantity greater than one, the serial numbers are expected to be consecutive. In the past there was never an issue with non-consecutive serial numbers because each department only had one person issuing serial numbers and they were only pulled from their pool of suffixes.

Now the company would like all departments to pull from one pool of serial numbers, starting with AA0001. The problem I'm having is a situation where two or more people are pulling serial numbers at the same time. Some requests are for up to 50 or even 200 serial numbers. How can I top person2's request from running until person1's code has finished?
Disclaimer, I'm not a programmer. I cobbled together the current code using the VERY little bit of knowledge I gleaned from playing around with VB 20+ years ago, and a whole bunch of code samples from the web.

 

[MarkK]

Set a flag at the start of the process, and clear the flag at the end of the process. If the flag is set, don't allow a second user to start the process.

 

[Paul Lemelin]

We're gonna need more details. I can only think of a function along the lines of this:

Public Function GetNextNumber(tableName As String, fieldName As String, prefix As String, trailingZeros As Integer) As String
      Dim db As DAO.Database
      Dim rs As DAO.Recordset
      Set db = CurrentDb
      Set rs = db.OpenRecordset(tableName)
      rs.MoveLast
      Dim nextNumber As String
      nextNumber = prefix & Format(rs(fieldName) + 1, String(trailingZeros + 1, "0"))
      rs.Close
      Set rs = Nothing
      Set db = Nothing
      GetNextNumber = nextNumber
End Function

To use it, you'd do this:

Dim nextID As String
nextID = GetNextNumber("Customers", "ID", "AAAAA", 3)

I'm sorry about that. The current code finds the last serial number entry for the requesting department, ex. 1235PA, strips the alpha, adds one to the integer, adds the alpha back then stores the serial number with the other info (part number, work order...). There is a bunch of other code that checks to see if you're at 9999 and if so creates the new alpha pair 0001PB, makes sure there are leading zeros and whatnot, so it's not "pulling" a serial number, it's "creating" a serial number.
How does the code above stop somebody else's request from running?

 

[Paul Lemelin]

Set a flag at the start of the process, and clear the flag at the end of the process. If the flag is set, don't allow a second user to start the process.

I have absolutely no idea how to set a flag. That works on a shared database? Would everybody have to be working from the same network file?

 

[561414]

I'm sorry about that. The current code finds the last serial number entry for the requesting department, ex. 1235PA, strips the alpha, adds one to the integer, adds the alpha back then stores the serial number with the other info (part number, work order...). There is a bunch of other code that checks to see if you're at 9999 and if so creates the new alpha pair 0001PB, makes sure there are leading zeros and whatnot, so it's not "pulling" a serial number, it's "creating" a serial number.
How does the code above stop somebody else's request from running?

I deleted that post because that was not what you were requesting.

Take the flag suggestion, I've never done it but it probably has something to do with a shared table with a boolean field called "allow_new_records", if the value is set to true, they can start inserting new records with the alphanumeric consecutive number, if the value is false, then you use the timer to check the value a few times to see whether it has changed to true, if a certain amount of time passed and it didn't change, then exit the routine. Let's see what others have implemented, it's interesting.

 

[MarkK]

Would everybody have to be working from the same network file?

Are the serial numbers stored and distributed from the same network file?

 

[Paul Lemelin]

Are the serial numbers stored and distributed from the same network file?

I'm not sure if you saw the other answer but they are created when the request is submitted. Here's what wrote then:

The current code finds the last serial number entry for the requesting department, ex. 1235PA, strips the alpha, adds one to the integer, adds the alpha back then stores the new serial number with the other info (part number, work order...). There is a bunch of other code that checks to see if you're at 9999 and if so creates the new alpha pair 0001PB, makes sure there are leading zeros and whatnot, so it's not "pulling" a serial number, it's "creating" a serial number.

If the quantity requested is more than 1 then it just loops to find the last serial number again, rinse and repeat. I'm sure the code isn't efficient but like I said, there aren't many users and the requests are fairly infrequent, maybe a 50 individual requests per day between the 5-8 users.

I did some tests and 50 serial numbers are generated in under a second for some users, but up to 10 seconds for others, depending on model of computer and network connection. It's not likely that requests will collide at some point but if they do it won't bode well.

 

[MarkK]

If the serial numbers are stored in a table in an enterprise-global database, then put your flag in a different table in that same database.

 

[Paul Lemelin]

If the serial numbers are stored in a table in an enterprise-global database, then put your flag in a different table in that same database.

It's nothing like that, just an Access database with a few tables stored on a network drive.

Thanks for the help, everybody, I'll try adding a flag stored in a table.

 

[The_Doc_Man]

Something you said triggered a sudden mental response...

The problem I'm having is a situation where two or more people are pulling serial numbers at the same time. Some requests are for up to 50 or even 200 serial numbers. How can I top person2's request from running until person1's code has finished?

IF this is NOT a split database, you need to make it so IMMEDIATELY, particularly if it contains data important to the company. If you have not done so already, start making regular backups of the data until you can implement a split and a data preservation regimen.

If multiple users can simultaneously open the app, you are risking massive data corruption due to file locking collisions and a phenomenon called "destructive interference" (shorthand for "left hand doesn't know what the right hand is doing"). Other euphemisms include "Too many cooks spoil the broth." You need to read up on SPLITTING A DATABASE (with respect to Access, of course.)

A stand-alone single-user database can get away with murder, procedurally speaking, because there will be few or no lock collisions. The MOMENT you start sharing, you lose the armor of isolation.

 

[Pat Hartman]

The "flag" needs to contain information. It can't be just Yes/No. Use two fields. One for the user doing the number generation and the second for the date/time the process started. When the process ends, update both fields to null. When a user starts the process, the code reads the "flag". If it is null, the code updates the value with the user name and Now() and saves the record.

If you have a situation where there is a lock set that hasn't been cleared, you know who started it and when which will help to determine the problem. Then you can have a form with a reset option to clear the "flag". Add a password if you think you need it.

 

[561414]

Here is a didactic implementation of the flag system I described in post #5
This is a split database, so you can test it with two instances of the frontend either in your same machine or in a local network. This will not make sense if you do not open at least two instances of the frontend, so duplicate frontend1 file if you're gonna test in your same machine, or open the frontend file in two different computers. We need at least two because we will simulate the scenario of two people adding records in bulk at the same time.

Instructions if on the same machine:
1. Open the frontends
2. Link the tables if necessary
3. In the Ensure consecutives combobox, choose YES <-- very important
4. Give it a name, this is just to identify your batch later in the table
5. Read the warning
6. Specify how many records you want to add in bulk, I like to test with 500, but if your machine is good, then you can specify more.
7. Make sure both frontends are filled before adding the records and both have different identifiers
8. QUICKLY! add the records in one and then in the other
9. Check the table, if you chose YES in the consecutives combobox, you should see consecutive records of the same identifier, if you chose NO in any of the frontends, then you will see how Access does NOT wait.

Let me know how it went.

 

[Paul Lemelin]

Something you said triggered a sudden mental response...

IF this is NOT a split database, you need to make it so IMMEDIATELY, particularly if it contains data important to the company. If you have not done so already, start making regular backups of the data until you can implement a split and a data preservation regimen.

If multiple users can simultaneously open the app, you are risking massive data corruption due to file locking collisions and a phenomenon called "destructive interference" (shorthand for "left hand doesn't know what the right hand is doing"). Other euphemisms include "Too many cooks spoil the broth." You need to read up on SPLITTING A DATABASE (with respect to Access, of course.)

A stand-alone single-user database can get away with murder, procedurally speaking, because there will be few or no lock collisions. The MOMENT you start sharing, you lose the armor of isolation.

It is a split database. From my reading when I started this whole thing I learned that's the only way to go. Thank you

 

[Paul Lemelin]

The "flag" needs to contain information. It can't be just Yes/No. Use two fields. One for the user doing the number generation and the second for the date/time the process started. When the process ends, update both fields to null. When a user starts the process, the code reads the "flag". If it is null, the code updates the value with the user name and Now() and saves the record.

If you have a situation where there is a lock set that hasn't been cleared, you know who started it and when which will help to determine the problem. Then you can have a form with a reset option to clear the "flag". Add a password if you think you need it.

After learning about setting a flag I decided it should be the userID but didn't think about the date/time or that it may become corrupted. Thanks for that! I started coding it yesterday but ran out of time as this is just one of my side projects at work that go along with my regular duties.

 

[Paul Lemelin]

Here is a didactic implementation of the flag system I described in post #5
This is a split database, so you can test it with two instances of the frontend either in your same machine or in a local network. This will not make sense if you do not open at least two instances of the frontend, so duplicate frontend1 file if you're gonna test in your same machine, or open the frontend file in two different computers. We need at least two because we will simulate the scenario of two people adding records in bulk at the same time.

Instructions if on the same machine:
1. Open the frontends
2. Link the tables if necessary
3. In the Ensure consecutives combobox, choose YES <-- very important
4. Give it a name, this is just to identify your batch later in the table
5. Read the warning
6. Specify how many records you want to add in bulk, I like to test with 500, but if your machine is good, then you can specify more.
7. Make sure both frontends are filled before adding the records and both have different identifiers
8. QUICKLY! add the records in one and then in the other
9. Check the table, if you chose YES in the consecutives combobox, you should see consecutive records of the same identifier, if you chose NO in any of the frontends, then you will see how Access does NOT wait.

Let me know how it went.

Thank you. I'll give this a shot a little later

 

[arnelgp]

Some requests are for up to 50 or even 200 serial numbers. How can I top person2's request from running until person1's code has finished?

that is not a problem.
you can make the "displayed" serial number as "provisionary".
before saving, you check "your table" again if nobody has used the number.
if it does already exists, just get new one from the pool, and put it to your
textbox on your form and check again. continue until you can find an "empty" slot
then save your record.