How can I intercept and modify the system warning message

Hello:

I have a continuous form where the user can add records. One of the fields “Binding_Percentage” is a required field, so if left blank the user will receive the following message:

“The field ‘tblAllForecast.Binding_Percentage” cannot contain a Null value because the required property for this field is set to True. Enter a value in this field.”

I would like to customize this message so that it is more meaningful to the End User. How would I go about doing that via VB?

Any help would be appreciated.

Keith.

6360MGrist

Newbie

5 Posts

March 28th, 2008
03:07 PM
#3

Re: How can I intercept and modify the system warning message

Hi there,

Not sure if this is what you're after, but in the properties of the text box in the Validation Text Box you could type your message to your user, like this

Validation Rule : Is not Null
Validation Text : Please Enter A Value

This would then throw up a warning box with your personal message. Hope this helps.

Mark

Scott Price

Moderator

1,303 Posts

March 28th, 2008
03:35 PM
#4

Re: How can I intercept and modify the system warning message

Alternatively a strict VBA solution would require you to set the warnings false before the error occurs, then in your error trapping mechanism calling a message box with your custom error message.

Regards,
Scott

kcdoell

Familiar Sight

230 Posts

March 28th, 2008
06:14 PM
#5

Re: How can I intercept and modify the system warning message

Scott:

I would like to use your idea but never tried it before. Below is my code that executes when a new record is created. :

Code: ( text )

Private Sub Form_BeforeUpdate(Cancel As Integer)
'When a user is creating a new record the following code inserts the MonthID, YearID and
'The LocationsID. It does a Dlookup for the Locations ID when the control cboLocation is
'blank.
Dim frm As Form
Set frm = Forms!Forecast
If Me.NewRecord Then
'If cboLocation is Not Null, grab the value from there
If Not IsNull(frm![cboLocation]) Then
JtnLocationsID = frm!cboLocation
YearID = frm!CboYear
MonthID = frm!CboMonth
Else 'Forms!Forecast![cboLocation] is Null
'Check and see if all 3 Controls have values in them
If Not IsNull(frm![cboDivision]) And Not IsNull(frm![cboWrkReg]) And _
Not IsNull(frm![cboCreditReg]) Then 'values in all 3 Controls
JtnLocationsID = DLookup("[JtnLocationsID]", "tblLocationsMM", "[DivisionIDFK] =" & frm![cboDivision] & _
" And [WrkRegIDFK] =" & frm![cboWrkReg] & " And [CreditRegIDFK] =" & _
frm![cboCreditReg])
YearID = frm!CboYear
MonthID = frm!CboMonth
Else
'no value in [cboLocation], and 1 or more values are missing in [cboDivision],
'[cboWrkReg], or [cboCreditReg]
MsgBox "1 or more values are missing in"
End If
End If
End If
End Sub

The control that is required on my form is called "Binding_Percentage". Where would I incorporate and "error catch" and what would be the verbiage in vb?

Any help would be great.

Thanks for the reply,

Keith

Scott Price

Moderator

1,303 Posts

March 28th, 2008
06:59 PM
#6

Re: How can I intercept and modify the system warning message

It would be a good idea for you to read through some threads dealing with error handling in VBA. This is a link to Allen Browne's page on error handling. I personally use his error logging code.

However, to give you a brief overview:

On line 2 of your code you will have something like this:

Code: ( text )

On Error GoTo Err_Form_BeforeUpdate

At the end of your code block (just before the End Sub) you'll have:

Code: ( text )

Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
MsgBox Err.Description & " " & Err.Number, vbCritical
Resume Exit_Form_BeforeUpdate

Obviously you can make the MsgBox say anything you want, including setting it's title, etc... There really isn't any need to set the warnings false, since this will suppress the default error message by itself, however, for future information the command to do so is:

Code: ( text )

DoCmd.SetWarnings = False

Don't forget to set them on again before you exit the sub routine:

Code: ( text )

DoCmd.SetWarnings = True

Regards,
Scott

isoquin

Member

48 Posts

March 28th, 2008
07:09 PM
#7

Re: How can I intercept and modify the system warning message

First I'll say I'm by no means an Access expert, but I have encountered this and came up with my own solution.

You can have your form's "BeforeUpdate" event check for the field first.

Code: ( text )

if isNull(myVariable) Then
MsgBox ("your own error here")
Cancel = True
Else: Cancel = False
End If

Again, I'm no expert like some of the other pros here, but giv eit a shot.

Last edited by Scott Price : March 29th, 2008 at 12:21 AM. Reason: fix code tag

Scott Price

Moderator

1,303 Posts

March 29th, 2008
12:21 AM
#10

Re: How can I intercept and modify the system warning message

Good call Isoquin! The reason to use the BeforeUpdate event in the first place is for data validation, which includes whether required fields are left blank.

Testing for a null value is 'best practice' :-) In addition to error handling for unexpected occurrences.

Glad it's working for you!

Regards,
Scott