Trapping Errors for Duplicate Index

[whdyck]

I'm using MS Access 2003.

I have a subroutine with the following statement at the top:

OnError GoTo ErrorHandler

along with the corresponding :ErrorHandler section at the bottom.

I also have a table with a column that is indexed with No Duplicates.

I notice that if Access encounters a duplicate key error with this table within this subroutine, the ErrorHandler section does not fire. Rather, an error message appears saying Runtime error: Type mismatch.

How can I make this error message more user-friendly if it won't invoke the ErrorHandler section?

Thanks for any help you can give.

Wayne

 

[pr2-eugin]

Could you please show us the 'ErrorHandler' coding that you have in place.. maybe a bit more coding??

 

[whdyck]

Hi,

Sorry, I thought that there was perhaps a different approach that was required by this type of error.

The relevant code is below. It fails on step #4 where it tries to run qryLoadLiaSourceDetail, which inserts records into a table having an indexed column that allows no duplicates.

Private Sub cmdImportLia_Click()
On Error GoTo ErrorHandler
 
'Set up all the declarations and begin the transactions
Dim myWorkspace         As DAO.Workspace
Dim myDatabase          As DAO.Database
Dim inTransaction       As Boolean
Dim liaHeadersCount     As Long
Dim liaDetailsCount     As Long
Dim liaStagedCount      As Long
Dim headerUpdateCount   As Long
Dim liaArchivedCount    As Long
Dim stageDeletesCount   As Long
Dim strMsgBoxReturn     As Long
Dim RecCountBefore      As Long
Dim RecCountAfter       As Long
Set myWorkspace = DBEngine.Workspaces(0)
Set myDatabase = CurrentDb
 
' 1. Ensure that the temp table is empty
myDatabase.Execute "DELETE * FROM tblLiaSourceImport", dbFailOnError
 
' 2. Import the CSV file into temp table
RecCountBefore = DCount("*", "tblLiaSourceImport")
DoCmd.TransferText acImportDelim, "LiaImportSpecification", "tblLiaSourceImport", Me.txtLiaImportFile.Value, False
RecCountAfter = DCount("*", "tblLiaSourceImport")
Me.txtLiaInputRecords.Value = RecCountAfter - RecCountBefore
 
myWorkspace.BeginTrans
inTransaction = True
 
' 3. Populate the LIA Source header table
myDatabase.Execute "qryLoadLiaSourceHeader", dbFailOnError
liaHeadersCount = myDatabase.RecordsAffected
 
' 4. Insert the detail data into the EDI details table
myDatabase.Execute "qryLoadLiaSourceDetail", dbFailOnError
liaDetailsCount = myDatabase.RecordsAffected
 
myWorkspace.CommitTrans
inTransaction = False
 
.
.
.
 
ErrorHandler:
 
    If inTransaction = True Then
        myWorkspace.Rollback
        MsgBox "Transaction Rolled Back", Err.Description
        inTransaction = False
    Else
        MsgBox "Error", Err.Description
    End If
    Resume CleanUpAndExit
 
End Sub

Thanks for any help you can give.

Wayne

 

[vbaInet]

The error is a Form error. So you need to trap it in the form's On Error event.

 

[whdyck]

The error is a Form error. So you need to trap it in the form's On Error event.

I don't believe that's the problem. If I add a breakpoint in the Form_Error event, it never reaches that breakpoint.

In the code I previously posted, the ErrorHandler section does indeed execute when this error happens. But I just get the "Microsoft Visual Basic" dialog that says "Run-time error '13' - Type mismatch", rather than the MsgBox I specified.

Wayne

 

[vbaInet]

I didn't say anything about putting error handlers there. Have a look at the following link re the form's Error event and its use:

http://msdn.microsoft.com/en-us/library/office/ff836345.aspx
http://www.techonthenet.com/access/forms/custom_error.php