Replace 2105 runtime error message with custom msgbox (1 Viewer)

[jp80]

Having set a table short text field to 'Indexed (No Duplicates)', I have a form which produces a '2105 runtime error' when the user attempts to submit a duplicate value. I would like to replace this default error message with a more user-friendly MsgBox.

My code for the SaveRecord button is:

 Private Sub SaveRecord_Click()
    
    DoCmd.GoToRecord , , acNewRec
    MsgBox "Record successfully saved", vbOKOnly + vbInformation, "Record Saved"
 End Sub

My code to capture the 2105 runtime error is:

 Private Sub Form_Error(DataErr As Integer, Response As Integer)
   
    If DataErr = 2105 Then
    MsgBox ("This villa booking has already been logged.")
    Response = 0
    End If
 End Sub

Unfortunately when the save button is clicked (when attempting to save a duplicate value), the 2105 error still runs. What are I doing wrong? I have searched extensively for a solution and nothing seems to work.

 

[Uncle Gizmo]

>>>when the user attempts to submit a duplicate value<<<

It is considered good practice to avoid producing error messages, and bad practice using an error as a decision making structure in your code.

In this particular case, the solution is NOT to pass a duplicate value to the table.

When the user attempts to submit a duplicate value, you run code to check it first, to see if the value is already present. If it is you notify your user.

 

[vbaInet]

Actually, it's fine producing errors and in some cases you need to cause it to error in order to make a decision. One of the reasons why error handling should be put in place for releasing memory (for example).

But I can see the point Uncle Gizmo is making, i.e. resolve the duplicate in the front end instead of doing it in the server (or backend).

 

[Uncle Gizmo]

>>>Actually, it's fine producing errors<<<

Yes it is, and thats how error checking works, and there are some errors you cannot avoid.

But it remains true:-
Avoid producing error messages
Avoid using errors as a decision making structure in your code.
But as vbaInet indicates in some situations it is necessary to break this rule, but this isn't one of those... (from what I see currently)

 

[vbaInet]

With all that said, it's still necessary to anticipate that even after all your checks it may still attempt to save a duplicate value. The chances of this happening is very slim but in a multi-user environment don't chance it, always give a meaningful error message.

So the error message for duplicates is 3022 and not 2105. Create this as a CONST too.

 

[jp80]

Thanks both for the super-fast replies. So that I can prevent the problem, rather than just mask the error, I have added the following code to the SaveRecord button (where villasT is the name of the table):

 Private Sub SaveRecord_Click()
    
Dim rs As Recordset
     
    Set rs = CurrentDb.OpenRecordset(villasT, dbOpenSnapshot)
    rs.FindFirst "CompassRef = """ & Me![CompassRef] & """"
    If Not rs.NoMatch Then
    Cancel = True
    MsgBox "This villa booking has already been logged."
    End If
    
    DoCmd.GoToRecord , , acNewRec
    
    MsgBox "Villa booking successfully saved", vbOKOnly + vbInformation, "Record Saved"
 End Sub

I am now getting a 3078 run-time error (cannot find the input table). There is only one table on by DB and it is definitely spelt correctly in the above coding. I'm sure it's something very straightforward; however I'm clearly a VBA novice..!

 

[vbaInet]

Since the form is bound, do it in the Before Update event of the form and set its Cancel argument to True if a duplicate is found. Something like this:

Private Sub Form_BeforeUpdate([COLOR="blue"]Cancel[/COLOR] As Integer)
    Dim intDupCnt As Integer

    intDupCnt = DCount("*", "TableName", "CompassRef = '" & Me![CompassRef] & "'")

    If intDupnt > 0 Then
        [COLOR="Blue"]Cancel[/COLOR] = True
        MsgBox "Duplicate found, cannot proceed!"
    End If
End Sub

 

[jp80]

I added the code to Form_BeforeUpdate (replacing the table name as required). It now just returns the 2105 error message again. What else could I be missing?

 

[vbaInet]

You keep saying error 2105 without describing the exact error description. What is error 2105?

 

[jp80]

It says 'You can't go to the specified record'. On debug, it highlights 'DoCmd.GoToRecord , , acNewRec' in the SaveRecord button code.

 

[vbaInet]

Ok, you need to handle that error within the SaveRecord function.

However, did you test to see if the duplicates are being caught?

 

[jp80]

I've checked the table and there are no duplicate records. The form stops upon generation of the error message and nothing is sent to the table (as has always been the case). If non-duplicates are added to the CompassRef field, the record saves fine. As mentioned in my first post, I have attempted to handle this error in the SaveRecord_Click function - however it still produces the 2105 error...

 

[vbaInet]

I've checked the table and there are no duplicate records. The form stops upon generation of the error message and nothing is sent to the table (as has always been the case). If non-duplicates are added to the CompassRef field, the record saves fine.

It's not working as expected. intDupCnt is probably never greater than 0 so you probably haven't tested this bit properly.

As mentioned in my first post, I have attempted to handle this error in the SaveRecord_Click function - however it still produces the 2105 error...

I mentioned to put handle it within the SaveRecord function like so:

Private Sub SaveRecord_Click()
On Error GoTo ErrHandler
    
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    
Exit_ErrHandler:
    Exit Sub

ErrHandler:
    If Err.Number = 2105 Then
        Err.Clear
    Else
        MsgBox "Some other error occured"
    End If
    Resume Exit_ErrHandler
End Sub

And move this Msgbox to the After Update event of the form:

    MsgBox "Villa booking successfully saved", vbOKOnly + vbInformation, "Record Saved"