How to catch error 8511 (Records unable to paste inserted into table 'Paste Errors')?

[jbrumbau]

Hi all,

I'm having difficulty catching error 8511, which states:
"Records that Microsoft Office Access was unable to paste have been inserted into a new table called 'Paste Errors' In the Database window, open the new table to see the unpasted records. After you fix the problems that resulted in the paste errors, copy and paste the records from the new table."

What generally happens is people copy data from Excel and paste it over data in MS Access, but if they mismatch the columns or if some data was pasted into a combo box without a matching item in the list or a field has too much text for the back end table to accept, then it fails to update the record and gives the above message.

My goal is to rewrite the message since people are locked out from seeing the back panel with all the tables but only get a switchboard, thus making the above message meaningless. My desired error handler code is below:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Call basicErrors(DataErr, Response)
End Sub
 
...
 
Sub basicErrors(ByRef DataErr As Integer, ByRef Response As Integer)
    If DataErr = 2113 Then  
        Call MsgBox("The value you entered isn't valid for this field (i.e. text in numeric field)." & Chr(13) & Chr(13) & _
            "To undo entry for this field, press ESC.", vbInformation, "Lists & Registers Database")
        Response = acDataErrContinue
    ElseIf DataErr = 2237 Then  
        Call MsgBox("The text you entered isn't an item in the list." & Chr(13) & Chr(13) & _
            "To undo entry for this field, press ESC.", vbInformation, "Lists & Registers Database")
        Response = acDataErrContinue
    ElseIf DataErr = 8511 Then  
        Call MsgBox("This record could not be updated, likely due to invalid text entered into some of the drop down boxes " & Chr(13) & _
            "or some of the text exceeding the maximum allowable text length for a field." & Chr(13) & Chr(13) & _
            "If pasting from excel, make sure the columns in excel line up exactly with the columns in MS Access " & Chr(13) & _
            "and that any data that is part of a drop down box is actually selectable from the drop down box.", vbInformation, "Lists & Registers Database")
        Response = acDataErrContinue
    End If
End Sub
 

I cannot catch it with the Form_Error event, so please let me know how to catch the error.

Thanks!

[Lysander]

I can see what you are talking about, and the errors you are getting, but I think the error you want to trap is not trapable as it is the result of a block operation, not a record by record operation. i.e. Someone is trying to update 500 records, 3 of them fail, but you only get the error message at the end, so you don't know which of the 3 had failed.

Instead of your users updating Access tables directly from Excell, maybe you could have them import the Excel data into a clean Access table that will accept all data, and after the import has been done, go through that table record by record and update the relevant Access data, reporting one by one on each record that had incorrect format.

[jbrumbau]

Sounds like quite a bit of work, I'm assuming I would have to create a separate form (i.e. EXCEL IMPORT) that has 100 generic 255 character text fields, with probably 1000 records? The user would then need to paste into it and then another button on the form would copy data from the EXCEL IMPORT form and paste it into the form of interest? I could see the potential for pasting information in the wrong columns, unless it automatically applied a column name to each column based on what form you select as the form you're trying to paste the data into.

[NeoPa]

This is a common problem. It is basically down to the data being imported, not matching the requirements there are for that data.

The solution is to import the data directly into a table with minimum restrictions set (so that all the data imports successfully, regardless of how rubbish it is), then checking the data imported, before processing that data into your desired table. This leaves you with full control over what is allowed in, as well as how you handle any records that fail your checks.

[jbrumbau]

So I'm assuming the proper approach is to link my form to an unrestricted entry dummy table, then in the form's Before_Update event is where you populate the real table?

[NeoPa]

No. That's not right.

You need to import the data, as you say, into a dummy table, then check the data fits what you need, then copy the valid data from the dummy table to the proper table. How you handle any invalid data is up to you, but I'd suggest you let the operator know where it can be reviewed.

[jbrumbau]

So I think the final answer is to associate all input controls in a form to either unbound fields or to a dummy table? Then after that, how data is handled is up to me?

[NeoPa]

I'm not sure why you're saying what you are. I thought I was clear in what I was saying, but your responses don't seem to indicate any real appreciation of that, so maybe the best thing for you to do is to explain to me where my explanation is unclear.

This is not about creating a form to manage the dummy table. The rules for determining valid data should not depend on human intervention at all. They should be clearly defined. Your query to copy the validated records should be built to copy only the valid records, and your report (or whatever you choose to use to show the invalid records to the operator) should be designed to show the invalid ones that need attention. I can't easily imagine any scenario where a form would come into the mix for this.

[Lysander]

Sorry jbrumbau and NeoPa not to have got back to this thread, having a little problem of my own computer:)

What I said, and NeoPa expanded upon was you can't do a mass import into Access and expect Access to handle individual record errors. You need to get the data into Access, and then validate each record one by one.

As an example, suppose your users were trying to import monthly wages from Excel into a table called tblMonthWage.

What you can do is have a button on your control panel to import the excel data into a brand new table, say tmpWage. On the code behind that button, you initiate the excel import (filetransfer I think is the command of the top of my head) and then, open a recordset based on tmpWage and loop through each record, 1 by 1.

Validate all the fields according to your own rules, such as, Is the Employee in the employee table, does the tax and NI match the gross pay, whatever validation you need to do.

Those records that pass your validation rules you write to tblMonthWage, those that fail you write to an exception table and report as errors to your user.

At the end, delete tmpWage ready for the next import.

[jbrumbau]

Lysander,

I think your method is certainly a surefire way to get data imported from excel without any errors. As for if the user attempts to paste excel data into a form in datasheet view, I just thought of using key traps to catch ctrl+v, then when it is hit, it will use the excel data import mechanism you are suggesting and replace the key command with return or something that has no effect.

However, right mouse click with paste and using the Edit+Paste menu command in datasheet view won't be caught, I'm not sure how to catch those commands, unless the form itself is linked to a dummy table and then the data is formatted and copied over to the proper table as NeoPa is suggesting.

Thanks again for the help from both of you.