Trapping error 3022

[aidan]

My table has an auto generated key as a string.

So on the offchance that two clients are trying to autogenerate a key at exactly the same time, I'm trying to trap the 3022 error raised when a duplicate key is entered so that I can repeat the operation with a goto.

I'm raising the error with requery like this:

            DoCmd.Save acForm, Me.Name    'next line triggers 3022
            DoCmd.Requery

After I realised that the normal handler wouldn't trap it I got the database from this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=77529&highlight=3022

But it doesn't seem to work for me:

http://www.imagedump.com/index.cgi?pick=get&tp=288631

Anyone?

 

[RuralGuy]

What event is this code located and do you have error trapping code in there? What makes you believe a normal handler will not trap this error? I do it all of the time.

How about posting all of the code in this subroutine or function?

 

[aidan]

It's just a button event:

Private Sub SaveChanges_Click()
On Error GoTo trap
    Dim second_attempt As Boolean
    second_attempt = False
try_again:
            GenerateKEY()  'while debugging this returns existing key..            
            DoCmd.Save acForm, Me.Name
            DoCmd.Requery
            DoCmd.Close acForm, "Additions"
        
Exit Sub
trap:
    If Err.Number = 3022 Then
        If second_attempt = False Then
            second_attempt = True
            GoTo try_again
        Else
            Display ("Duplicate Key Error")
        End If
    Else
        Display ("Unknown Error")
    End If
 On Error Resume Next
    Me.Undo
    DoCmd.Close acForm, "Additions"
End Sub

The handler isn't picking it up though, I'm always getting the box with the debug and end buttons.

 

[RuralGuy]

While viewing this Button Event code go to Tools>Options then General tab. On the right side you have Error Trapping. S/B set to Break on Unhandled Errors.

I'm not familiar with the Display function. Where is it in the Help system?

 

[aidan]

It's already set to unhandled errors unfortunately. Display() is just a pseudocode representing the display error function - sorry for any confusion.

Aidan

 

[RuralGuy]

You understand that DoCmd.Save acForm, Me.Name saves the form and not the record, right?

To save the record you would need: DoCmd.RunCommand acCmdSaveRecord

 

[aidan]

Um, late last night I didn't no. I had "DoCmd.RunCommand acCmdSaveRecord" to begin with though - I just checked and either this on it's own or requery brings up the error. With either of these it's still not getting trapped by either error handler though.

 

[RuralGuy]

I'm sorry, I'm out of ideas. Without seeing the db I don't know what else to try.

 

[aidan]

Thanks for trying. I can't post the whole thing I'm afraid though. It's about 7 meg unzipped without data. Maybe someone out there will post a solution.

There are only going to be 2 people using it at once, so the chances are pretty slim anyway, but you said you do this all the time? Did you mean for the this reason?

 

[RuralGuy]

Not for the reason you are anticipating. I meant I catch error 3022 and put up my own message.

 

[aidan]

Error 2448 is what it's bringing up in a test database I just made. Do people usually trap this error also?

Aidan

 

[RuralGuy]

Cannot set value. (Error 2448)
You are trying to assign a value to an object that is read-only or does not have a value.

It is trapable but I would fix it!

 

[aidan]

Thanks for all the help last week. I got the error trapped in the test, but it still wouldn't work in the main db.

If anybody stumbles on this thread wanting to trap 3022 errors, then unless they're having the same problem I was the following thread should help:

http://www.saicomsystems.com/AccessPearls/err-01.asp

aidan