customise warnings on wrongful data entry (1 Viewer)

[anthonyevans]

Dear All,

Is there a way to customise the in-built Access warning when a user input data in a field in the wrong format ?

Thankyou in advance,

Anthony

 

[Rich@ITTC]

Hi Anthony

Yes this is possible in Access. You need to identify which error message you get that you want to replace - they all have code numbers. Below is an example that I prepared earlier:

--------------------------------------------------
Private Sub Form_Error(intDataErr As Integer, intResponse As Integer)
' Comments :
' Parameters : intDataErr
' intResponse -
' Returns : -
' Created :
' Modified :
'
' --------------------------------------------------------
On Error GoTo Err_Form_Error
Const INPUTMASK_VIOLATION = 2279
Const DATAREQUIRED_VIOLATION = 3314

Dim strMsg As String
If intDataErr = INPUTMASK_VIOLATION Then
Select Case Screen.ActiveControl.Name

Case "cboTitle"
Beep
MsgBox "The Title you entered is invalid!", 48

Case "txtAge"
Beep
MsgBox "For dates you must enter the full year - dd/mm/yyyy (e.g.22/10/2001)", 48

Case "txtFlightDate"
Beep
MsgBox "For dates you must enter the full year - dd/mm/yyyy (e.g.22/10/2001)", 48

Case Else
Beep
strMsg = "An input mask violation occurred in control "
strMsg = strMsg & Screen.ActiveControl.Name & "!"

End Select

intResponse = acDataErrContinue

End If

If intDataErr = DATAREQUIRED_VIOLATION Then
MsgBox "Certain fields cannot be left blank. " _
& "Please ensure that Family Name is completed.", 48, "Data Entry Required"
intResponse = acDataErrContinue

End If

Exit Sub

Err_Form_Error:
MsgBox "The following error occurred: " & Error$
Resume Next
End Sub
-------------------------------------------------

HTH

Rich Gorvin

 

[anthonyevans]

Thanks Rich for such a quick response! I will give it a go.

Anthony

 

[anthonyevans]

Rich,

Where do I find these error messages and codes ?

Anthony

 

[Rich@ITTC]

Hi Anthony

Try this site (competition for this one I know ... but it also is excellent!).

http://www.accesswatch.co.uk/html/microsoft_access_downloads/access_watch_download _files.htm

Scroll down to the end and download the "All Access Error Codes" demo database.

- Download the self extracting file awerrors.exe 571Kb
This file contains the complete database awerrors.mdb -

This will give you all the error codes. An alternative to this is coding within your database that generates a list in a new table of error codes:

---------------------------------------------

Function AccessAndJetErrorsTable() As Boolean
' Comments :
' Parameters : -
' Returns : Boolean -
' Created :
' Modified :
'
' --------------------------------------------------------
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim lngCode As Long
Dim strAccessErr As String
Const conAppObjectError = "Application-defined or object-defined error"

On Error GoTo Error_AccessAndJetErrorsTable
' Create Errors table with ErrorNumber and ErrorDescription fields.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("AccessAndJetErrors")
Set fld = tdf.CreateField("ErrorCode", dbLong)

tdf.Fields.Append fld
Set fld = tdf.CreateField("ErrorString", dbMemo)
tdf.Fields.Append fld

dbs.TableDefs.Append tdf
' Open recordset on Errors table.
Set rst = dbs.OpenRecordset("AccessAndJetErrors")
' Loop through error codes.
For lngCode = 0 To 3500
On Error Resume Next
' Raise each error.
strAccessErr = AccessError(lngCode)
DoCmd.Hourglass True
' Skip error numbers without associated strings.
If strAccessErr <> "" Then

' Skip codes that generate application or object-defined errors.
If strAccessErr <> conAppObjectError Then
' Add each error code and string to Errors table.
rst.AddNew
rst!ErrorCode = lngCode
' Append string to memo field.
rst!ErrorString.AppendChunk strAccessErr
rst.Update
End If
End If
Next lngCode
' Close recordset.
rst.Close
DoCmd.Hourglass False
'Refresh DatabaseWindow
MsgBox "Access and Jet errors table created."

AccessAndJetErrorsTable = True

Exit_AccessAndJetErrorsTable:
Exit Function

Error_AccessAndJetErrorsTable:
MsgBox Err & ": " & Err.Description
AccessAndJetErrorsTable = False
Resume Exit_AccessAndJetErrorsTable
End Function
---------------------------------------------

Copy this into a module, save it as basErrorCodes (or similar) and then run it. It will generate a list of errors in a table for you. Apologies to whoever created this - I picked it up ages ago and can't remember where. So none of the credit for this coding should go to me .. and if anyone out there knows where it comes from please post it here so the real author can be rightfully praised!

HTH

Rich Gorvin


[This message has been edited by Rich@ITTC (edited 04-19-2001).]

 

[anthonyevans]

Sorry Rich for being a pain !

I've downloaded the code database and had a look.

Now my problem is that for example in a date field that has been with medium date i want a warning that i have customised to come up telling the user how exactly to enter it.

The warning that presently comes up is not listed in that code database! The warning is : "The value you entered isn't valid for this field. For example you may have entered text into a numeric field or a number that is larger than the FieldSize setting permits."

I just want to fiddle with that text above, and it is just a matter of finding out where it is stored.

Thankyou for your kind help,

Anthony

 

[Rich@ITTC]

Hi Anthony

Yes you are right it isn't in the error database. Oh heck!

Try this error code number: 2113

HTH

Rich Gorvin

 

[anthonyevans]

Thanks Rich, yes I did find out it was that code but now how do i find out where that error code is stored in my database so that i can change the warning text.

Anthony

 

[Rich@ITTC]

Hi Anthony

As far as I know you can't alter that text - it is part of Access coding and you may find you need it elsewhere (it is a generic error statement - not specific to your particular database). You need to use the code I gave above replacing

-------
On Error GoTo Err_Form_Error
Const INPUTMASK_VIOLATION = 2279
Const DATAREQUIRED_VIOLATION = 3314
.....
.....

-----------

with a description for the error code and the code number eg.

Const FIELDSIZE_ERROR = 2113

This is then used in the coding given earlier and you add your own messages as in the examples provided. The idea is that by using an If/Then/Else/EndIf section of coding you programme the OnError event of your Form to look out for and identify error 2113 and then your customized user-friendly message is displayed (using the MsgBox function).

HTH.

Rich Gorvin




[This message has been edited by Rich@ITTC (edited 04-19-2001).]

 

[Rich@ITTC]

Hi Anthony

Try this:

--------------------------------------------
Private Sub Form_Error(intDataErr As Integer, intResponse As Integer)

Const FIELDSIZE_ERROR = 2113

On Error GoTo Err_Form_Error

If intDataErr = FIELDSIZE_ERROR Then
MsgBox "Your date has too many numbers. " _
& "Please use this format 01-Jun-01.", 48, "Date incorrect format"
intResponse = acDataErrContinue
Else

Err_Form_Error:
MsgBox "The following error occurred: " & Error$ & " (" & intDataErr & ")"
Resume Next
End If

End Sub
--------------------------------------------

HTH

Rich Gorvin

 

[anthonyevans]

Thanks Rich I reckon we're almost there !

Do I put that code into the On Exit Event handler for my particular field on the form.

Thanks for your kind help and patience !

 

[Rich@ITTC]

Hi Anthony

No - it applies to the whole form and you put it in the CBF - "coding behind form".
It is the Form_Error Event.
This coding example has all the correct names etc - so you open the module window for coding (View->Code on the Menu Bar)when in design view for this form. Copy and paste the coding as it is. It then is linked to the OnError event for that Form (so when an error occurs on that form this coding runs and picks up if it is this particular error - you add more customised error-trapping as and when necessary).

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 04-19-2001).]

 

[anthonyevans]

Thank you so much Rich. It works !!!!!

I owe you a beer !

Best Regards,

Anthony

 

[Rich@ITTC]

Hi Anthony

Why the multiple exclamation marks!!!!

Did you ever doubt that it would work?

... and what do you mean A beer? Surely a six-pack if not a firkin!

Rich Gorvin

 

[anthonyevans]

never had doubts. that's what makes this forum so great! great people, great help.

a six pack would be fine!

Anthony

 

[accesswatch]

competition for this one I know ... but it also is excellent!).

Not so much competition more a complimentary site, more than happy to direct people to this fine site when appropriate.

On the error number not being found. The error number is in the database but some of the characters in the text string have been changed during the extraction phase so looking for a complete string does not allways return the result you may expect.

Regards Trevor

 

[RossG]

Hi everyone

I'm new to Access but have used this code in my first project. It traps a few different errors (including errors caused by the rotten wheel mouse) and responds with a customised message.

I'd be grateful for any suggestions on improvements!


Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr

Case "2279"
Select Case Screen.ActiveControl.Name
Case "ctlStartDate"
Beep
MsgBox "You have entered the date in the wrong format.@Please enter the date in 'dd mm yy' format.@Note: you do not have to enter a divider such as - or /.", _
vbExclamation + vbOKOnly

Case "ctlETA" Or "ctlETD"
Beep
MsgBox "You have entered the time in the wrong format.@Please enter time in 'hh mm' format.@Note: you do not have to enter a divider between the hours and minutes.", _
vbExclamation + vbOKOnly


End Select

Case "3314"
Beep
MsgBox "Your last action has generated an error.@If you are using a mouse with a scroll wheel you may have tried to scroll a list without first opening the list.@Please try again.", _
vbExclamation + vbOKOnly


Case Else
Beep
MsgBox "An error has been generated.@Please try again. @If the problem persists please notify Security.", _
vbExclamation + vbOKOnly


End Select




Response = acDataErrContinue

End Sub


[This message has been edited by RossG (edited 04-21-2001).]

 

[paulmortlock]

maybe this should be a new topic but would i use this same method to change the error that appears when i try to add a record in my subform that causes a duplicate key error.
Id like it to say,'product already selected'