This message occurs if I try to paste a string from the clipboard into a bound textbox control when the bound field is smaller than the data being pasted. How can I trap this error?
To trap this error and any other error, you can place code into the Form'sOnError event.
The error you are specifically encountering is the Access 2221 Error:
The text is too long to be edited.
and like any error in Access, you can Trap it. Here is a sample:
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
[COLOR="DarkGreen"]'Trap the "2221 - The text is too long to be edited." Error.[/COLOR]
If DataErr = 2221 Then
[COLOR="DarkGreen"]'Extinguish the current Error so as not to display the
'default MS-Access Error Message Box.[/COLOR]
DataErr = 0: Response = 0
[COLOR="DarkGreen"]'Provide our own Error message. Perhaps something more meaningful.
'Within the Message we use the Screen.ActiveControl.Name property
'to determine the Form Field in Error and we display the name to
'the User.[/COLOR]
MsgBox "You tried to supply data to a Form Field that " & vbCr & _
"is greater than what the Field is capable of " & vbCr & _
"handling. Please reduce the length of data." & vbCr & vbCr & _
"Form Field in Error: " & [B]Screen.ActiveControl.Name[/B], vbExclamation, _
"Data Length Too Long"
[COLOR="DarkGreen"]'Clear any qued Errors. Shouldn't realy be required
'but I like to play it safe :)[/COLOR]
Err.Clear
End If
End Sub
Thank you!
I didn't understand Form_Error, and was trying to use Err.Number, which was zero. Where are these DataErr codes listed, and where is information about what the "Response as Integer" parameter is, and what I can do with it?
I can now trap the error, and display a meaningful message. I tried to go further, and correct the error. Logically, I want to do "wk = ClipBoard", but I don't know how, so I tried:
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim wk As String
If DataErr = 2221 Then
Select Case Screen.ActiveControl.Name
. . .
Case "Name"
Me.txtZeroSizeWorkspace.SetFocus
DoCmd.RunCommand acCmdPaste
wk = txtZeroSizeWorkspace
. . .
End Select
. . .
Statement "wk = txtZeroSizeWorkspace" gets me "Run-time error '94': Invalid use of Null", but a Cntl-V into any other window will paste the clipboard, so the clipboard is not empty.
Is there any way I can get what is on the clipboard now that I can trap the error?
I checked your reference, but that is too far beyond my understanding to even consider. I'll stick with DoCmd and accept that it doesn't always work. Thank you for considering may problem.
I didn't say I couldn't do it, I said I didn't understand it. In the past, when I do things I don't understand, like try to include some library, eventually it goes wrong, and I am stuck, 'cause I don't understand so I can't debug.
Apparently Access will not do DoCmd.RunCommand acCmdPaste in an Error routine processing a Paste-from-Clipboard. The code suggested by boblarson does work, and offers me a neat solution. Thanks again!
Good question. DataErr code numbers specificly are not listed anywhere....at least not anywhere I can find, but because the DataErr is part of the Err Object Collection the error number is a Err.Number so to speak. There are lists for Access Error Code Numbers but none that are truely complete. Several DLL's and satellite members are used in the workings of Access and each can contain their own set of Error Code Numbers which need to be translated by Access itself. For example the -2147467259 which is a Run-time Error you can get when you open a form by using Automation in Visual Basic.
If you prefer to make your own Error list, then here is a small procedure I quickly pumped out to fill a Access Table with the Error Code Numbers and their respecitve Error Description. Simply create a new Access Table and name it AccessErrorCodes. This Table should contain two fields (no Primary Key) named AccessErrorNumber (Number - Long Integer DataType) and AccessErrorDescription (Text - Memo DataType). Place the following code into the OnClick event of a Command Button somewhere on a Form:
Code:
'Declare Required Variables
Dim i As Long, ErrStrg As String, StrgSQL As String
'Inform about the length of time this procedure will take.
MsgBox "This procedure will take approximately 2½ minutes " & vbCr & _
"to complete. Please wait until Table is completed.", vbInformation, _
"Please Note..."
'Set the Mouse Pointer to the Hourglass to indicate "Busy"
Screen.MousePointer = 11
'Cycle through all the possible Access Error Numbers
'and retrieve the equivalent Error Description then
'post it to the AccessErrorCodes Table.
For i = 1 To 65535
'Get the Error Description for the provided
'Error Number and place it into the ErrzStrg
'string variable.
ErrStrg = AccessError(i)
'Some Error Decription strings contain apostrophes.
'We need to handle these properly since the extra
'apostrophe in the Error Description string muddles
'up our SQL string and therefore an Error is generated.
'We need to add an additional apostrophe in the Error
'Description string where the current apostrphe exists.
'This eliminates the SQL string error. This particular
'problem holds true for any SQL String that uses apostrophes
'to apply data to a Text Data Type Field. We are using the
'Replace function to handle this problem.
ErrStrg = Replace(ErrStrg, "'", "''")
'Remove Null Characters (if Any) and convert to empty string.
'The Replace function is again used for this.
ErrStrg = Replace(ErrStrg, Chr$(0), "")
'Create the SQL String to insert a new record into the
'AccessErrorCodes Table. We use the INSERT INTO SQL
'statement for this.
StrgSQL = "INSERT INTO AccessErrorCodes (AccessErrorNumber, " & _
"AccessErrorDescription) VALUES (" & i & ", '" & ErrStrg & "');"
'Execute the SQL String.
CurrentDb.Execute StrgSQL
Next i
'Not all numbers contain an error so, we delete
'those records from Table that contain no Error
'Description. The SQL DELETE statement does quick
'work of this.
'Create the DELETE SQL String
StrgSQL = "DELETE FROM AccessErrorCodes WHERE AccessErrorDescription " & _
"IS NULL OR AccessErrorDescription='';"
'Execute the SQL String.
CurrentDb.Execute StrgSQL
'Set the Mouse pointer to normal.
Screen.MousePointer = 0
'DONE
Looks big but it's realy just a lot of comments so you can see what's going on.
In essence, the Forms' OnError event allows you to trap Run-Time Action Errors related to focused Forms, Reports, and of course the Microsoft Jet Database Engine. It does not handle the VBA Run-Time Errors. When you create an application, you obviously want to code so as to ensure that NO ERROR will ever be generated. Unfortunately not everything can be covered and therefore a Error Handling mechanism should be created to take care of any Error business. A Function procedure should be developed to take care of (handle) both VB and Form Errors and placed Publicly in a Database Code Module so that it can be accessed from any code within the application.
In the sample code I provided in my earlier post, you can see that I had set the Response = 0. This is actually the puppy that supresses the Message Box generated by MS-Access. Most would use the VBA intrinsic constant acDataErrContinue which contains the value of 0. On the other hand you could also issue the Constant acDataErrDisplay (1) which tells the Forms' OnError event to display the MS-Access generated Error Message related to the DataErr Code Number encountered. 1 is obviouly the default when a Form (or Report) Error is generated. So, Response Determines whether an error message is displayed using one of the following intrinsic constants: acDataErrContinue ignores the Error and acDataErrDisplay displays the default Error Message. Just so we're clear on that.
As for your clipboard problem....You can use the API Functions shown in the link so graciously provided by Bob or you could use a few lines of code and the addition of a TextBox which we mask so that it can't be seen. Here's how:
- Place a new TextBox into your Form.
- Delete the Label associated with the TextBox (click on label then hit the delete button).
- Completely Colapse the control so that it is just a small line on the Form.
- Name the TextBox "'Dummy" (no quotes) and set the following format properties:
Back Style........ Transparent
Border Style...... Transparent
ForeColor......... The Back Color of your Form or the Back Color of the Control the TextBox is on Top of (if it is).
Special Effect.... Flat
- So far, if all is well you will not see this TextBox when the Form is opened.
Now, the code to use in the Forms' OnError event might look like this:
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Trap the "2221 - The text is too long to be edited." Error.
If DataErr = 2221 Then
'Declare necessary Variables...
Dim Strg As String, Ctrl As Control
'Set the Ctrl object variable to the Control
'that initiated the Error.
Set Ctrl = Screen.ActiveControl
'Extinguish the current Error so as not to display the
'default MS-Access Error Message Box.
DataErr = 0: Response = acDataErrContinue
'Provide our own Error message. Perhaps something more meaningful.
'Within the Message we use the Screen.ActiveControl.Name property
'to determine the Form Field in Error and we display the name to
'the User.
MsgBox "You tried to supply data to a Form Field that " & vbCr & _
"is greater than what the Field is capable of " & vbCr & _
"handling. Please reduce the length of data." & vbCr & vbCr & _
"Form Field in Error: " & Ctrl.Name & vbCr & vbCr & _
"Your data will automatically be truncated to a" & vbCr & _
"length ot 50 Characters.", vbExclamation, _
"Data Length Too Long"
'Clear any qued Errors. Shouldn't realy be required
'but I like to play it safe :)
Err.Clear
'*** HANDLE A PASTE FROM THE CLIPBOARD ***
'=========================================
'Set Focus to the Dummy TextBox.
Me.Dummy.SetFocus
'Paste the ClipBoard into the Dummy TextBox.
DoCmd.RunCommand acCmdPaste
'Make sure something was actually pasted into
'the Dummy TextBox.
If Len(Me.Dummy.Text) > 0 Then
'Yup, something was pasted...
'Place the Contents of the Dummy TextBox into
'the Strg string variable then Clear the Dummy
'TextBox
Strg = Me.Dummy.Text: Me.Dummy = ""
'Are there more than 50 characters in our Strg
'Variable. If so then shorten things to 50 Char's.
If Len(Strg) > 50 Then Strg = Left$(Strg, 50)
'Now place the contents of the Strg variable into
'the Control that initiated the Error.
Ctrl = Strg
End If
'Set focus back to the control that initially initiated
'the Error
Ctrl.SetFocus
'Free Memory
Strg = "": Set Ctrl = Nothing
End If
End Sub
Wow Cyber. You're snippet returns 65535 rows of which about 30,000 deleted because they are empty and of the 36436 populated records 34679 are duplicates of "Application-defined or object-defined error"
Now we don't really want to consider the fact that there are actually 65535 Errors designated for MS-Access alone do we?
Or do we?
As for the "Application-defined or object-defined error", it just goes to show you what is indeed allocated to to hold the errors of your favorite home made DLL or control.
if ya'll want to blot out the Application-defined or object-defined error from the list then merely add this line of code direct after the For i = 1 to....:
If AccessError(i) <> "Application-defined or object-defined error" Then
and and this line directly before the Next i code line:
End If
As for the other 30,000 not listed....what the heck Raise your own Errors.
It was just an observation. I cleaned out the majority a little differently. I figured it would be good to have some of them except where Access chooses to fill in the remaining records from 32772 to 65535 with the same thing or where they are back-to-back identical. This returns 1764 records where about 40 or so are still Application ...
I stripped out the comments for brevity.
Code:
Public Function GetErrCodes()
Dim i As Long
Dim ErrStrg As String
Dim StrgSQL As String
MsgBox "This procedure will take approximately 2½ minutes " & vbCr & _
"to complete. Please wait until Table is completed.", vbInformation, _
"Please Note..."
If TableExists("AccessErrorCodes") Then
DoCmd.DeleteObject acTable, "AccessErrorCodes"
End If
StrgSQL = "Create Table AccessErrorCodes( " & _
"AccessErrorNumber Integer, " & _
"AccessErrorDescription Text(255) );"
DoCmd.RunSQL StrgSQL
Screen.MousePointer = 11
For i = 1 To 65535
ErrStrg = AccessError(i)
ErrStrg = Replace(ErrStrg, "'", "''")
ErrStrg = Replace(ErrStrg, Chr$(0), "")
If AccessError(i - 1) = "Application-defined or object-defined error" Then
Else
StrgSQL = "INSERT INTO AccessErrorCodes (AccessErrorNumber, " & _
"AccessErrorDescription) VALUES (" & i & ", '" & ErrStrg & "');"
CurrentDb.Execute StrgSQL
End If
Next i
StrgSQL = "DELETE FROM AccessErrorCodes WHERE AccessErrorDescription " & _
"IS NULL OR AccessErrorDescription='';"
CurrentDb.Execute StrgSQL
Screen.MousePointer = 0
End Function
Oh, the function above calls a UDF to recognize Physical tables' or logical (query) tables' existence
Code:
Public Function TableExists(ByVal szName As String, _
Optional ByVal szType As String = "Physical") As Boolean
Dim vItem As Variant
Dim fTemp As Boolean
fTemp = False
Select Case UCase(szType)
Case "PHYSICAL"
For Each vItem In CurrentDb.TableDefs
If vItem.Name = szName Then
fTemp = True
Exit For
End If
Next vItem
Case "LOGICAL"
For Each vItem In CurrentDb.QueryDefs
If vItem.Name = szName Then
fTemp = True
Exit For
End If
Next vItem
Case Else
fTemp = False
End Select
TableExists = fTemp
End Function
