Cannot clear runtime error 3022

[grendell2099]

Hi all, I am having trouble trapping runtime error 3022 (duplicate value in a field with no duplicates).
For the table in question, when a user adds a record I use code to find the max index number and add one to it for the new record.
This works well. However, my current project has about 6 users who make a lot of entries during the day and sure enough yesterday two of them received the same index number. The second user received the runtime 3022 error.
I have tried to trap this thing, but I can only get my code to handle the first occurrance of the error. It may be over kill, but I would like a robust error handler that could deal with multiple 3022 error events. I bomb out on the me.requery line.
Seems like I am not successfully clearing the error after an occurance- any ideas would be greatly appreciated.

On Error GoTo ERR_HNDL

dim MyIndexTries as integer

Application.Echo false
Me.AllowAdditions = true
MyIndexTries = 1

DoCmd.GoToRecord , , acNewRec
TRY_AGAIN:
Me.INDEX = GetTableIndex("InspectionRecords", "INDEX") 'get the index number for a new record
Me.txtInspector = Environ("username")
Me.InspectDate = Format(Now, "mm/dd/yy")
Me.Requery
Me.cboPartNum.SetFocus

EXIT_SUB: 'CLEAR VARIABLES
Application.Echo True
Me.AllowAdditions = False
DoCmd.SetWarnings True
ErrorMessage = ""
MyIndexTries = 0
vStatusBar = SysCmd(acSysCmdClearStatus)
Exit Sub

ERR_HNDL:
If Err.Number = 3022 Then 'multiple users pulled the same index number
If MyIndexTries = 4 Then
DoCmd.GoToRecord , , acPrevious
MsgBox "There appears to be a problem generating an index number... contact the administrator"
Resume EXIT_SUB
End If
MyIndexTries = MyIndexTries + 1
Err.Clear
Pause (1)
GoTo TRY_AGAIN
End If
Resume EXIT_SUB
End Sub

 

[ajetrumpet]

you may want to try just using a variable to indicate that the lookup code is running and stop users from entering a record until the other one finishes. something like:

public running as boolean

if running=true then
  msgbox "Another record is currently being entered. try again in a few"
     exit
end if

then when a user enters a record, do something like:

'WHEN THE LOOKUP CODE STARTS...
running=true

'WHEN THE LOOKUP ENDS...
running=false

maybe on database open, running=false? not sure what the default is. never looked into it, but i think it's already false. either that or nothing at all.

 

[grendell2099]

Thanks for the idea. I should have mentioned it is a split database, with each user having their own front end. I do not think your solution would work in this environment (though I am very tired so if I may be missing something obvious) .
Is there some way to know if a record is being added to the table?

 

[ajetrumpet]

Thanks for the idea. I should have mentioned it is a split database, with each user having their own front end. I do not think your solution would work in this environment (though I am very tired so if I may be missing something obvious) .
Is there some way to know if a record is being added to the table?

don't public variables work on networks too?

 

[grendell2099]

I do not have any code in the backend, not a single line... so I do not believe there is any way for one front end to know what another front end has its variables set to.
I think I would have to be running the code from the shared back end for everyone to see it.

 

[ajetrumpet]

how about this:

dim dup as boolean

on error goto handler

dup = false //YOU HAVEN'T GOTTEN THE ERROR YET

CodeStart:
//ALL YOUR CODE HERE
e.g. - if dup = false then //attempt the formula first (skip if errored out)
              run the original formula
            end if

exit function

handler:
   if err.number = 3022 then
   dup = true //ERROR OCCURED
      THE NUMBER = YOUR FORUMULA + 1
         goto CodeStart //ENTER THE RECORD AGAIN USING THE NEW NUMBER ABOVE
   end if

 

[gemma-the-husky]

a couple of thoughts

1. you should not retrieve the index until the last possible moment - if you retrieve early in the record entry process, then of course two users could retrieve the same one

so retrieve it at the time you execute the before update event - this will give the shortest window to get duplicates

2. if you REALLY want to avoid duplicates 100%, then store the next number in another table rather than using a dmax - then when you need a number, LOCK the table, get the number, increment the next number, and release the lock.


However, note that in theory either method can leave gaps in the sequence - ie you retrieve no 323, next user retrieves no 324 - then you change your mind, or get a write error with no 323, and decide to abandon the record. 323 is now missing from sequence - and the next number will be retrieved as 325.

note that maintaining complete number sequences with certainty! is extremely difficult.