Multi User Duplicate (1 Viewer)

[rangersedge]

I have a DB that is used by multiple people. It has a "PO" field that uses a VBA code to generate the next number instead of Autonumber due to needing the numbers to actually be in order. The problem is that if 2 people are creating new records at the same time then it will create the same PO number twice. I cant have the same PO number repeated. So how do I prevent this from happening? Is there a way to lock the new number as soon as the person BEGINS creating the record instead of when the record is saved?


Here is the "Autonumber" code that I'm using...

Private Sub Form_Current()
If Me.NewRecord = True Then Me.PONumber = Nz(DMax("PONumber", "POLog")) + 1

If Me.NewRecord = True Then Me.Requestor = UserOnName

End Sub

 

[here4real]

How about if you create a table with one record that contains the next PO number? Instead of getting PO from your DMax, you get it from this table. Once you give a new PO out, you update this table immediately BEFORE actually updating the POs. Theoretically, you may end up wasting some PO numbers, e.g. if you discard a PO without saving it but this is no different than having a printed form where you make a mistake and throw out the form.

 

[rangersedge]

Would this keep from creating duplicates when 2 people are creating records at the same time? I need a way to do this without skipping or wasting numbers.

 

[here4real]

The problem you are having is the lag time between when a number gets assigned and the time that the PO record actually gets saved. Here, when the number is assigned, the next PO number should be automatically updated so that the next user will get the next number even though the first user didn't save their PO record yet. The only issue would be if somebody cancels their PO and doesn't save it, that number would then be "wasted" but, as I said, that is no different than me taking a paper PO, making a mistake and throwing it away - that number is gone.

 

[rangersedge]

Before this they printed out a sheet of paper from excel with the numbers already on it. They would fill in the rest of the information like job number and supplier when they needed to order something. If someone messed up then they just erased the info and reused the number. I'm trying to make this electronic so we can use this system while outside the office. They are also wanting me to use this system to assign job numbers which absolutely cannot be skipped or duplicated.

 

[here4real]

An alternative is adding the PO record immediately when the number is assigned so that the next user will see an already existing record. Where you will get in trouble is if the user discards the updates to the PO so that it reverts back to a blank PO. The easiest thing would be to allow voided POs, in which case you can just go with the previous solution. If that is not an option, I would probably add a boolean field to each PO indicating whether the PO is being used or not. For a PO that is saved, it would be set to True otw False. What would change though is that when assigning a PO, you would first have to check if there are any available PO #s already entered (DMax wouldn't be sufficient). If so, you would want to use one of those first. If not, then DMax + 1.

 

[rangersedge]

Let me try this first instead of re-inventing the wheel... How do I get it to save the record and the used number as soon as the new record begins being created? This way as soon as someone types into the new record and the number is assigned, it will be saved and the next user will not get the same number. I think my problem is the lag between record creation and saving as stated earlier. If I can make it save immediately then that should correct my issue... in my mind anyway...

 

[here4real]

You do an Add Record to the table with the PO # or you can do an SQL Insert with the PO # (which I assume is key). Then, the changes the uses makes are Edits. You can Google that.