How can I intercept and modify the system warning message

[kcdoell]

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.

[kcdoell]

Any thoughts out there on this topic???

[6360MGrist]

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]

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]

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. :

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]

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:

On Error GoTo Err_Form_BeforeUpdate

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

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:

DoCmd.SetWarnings = False

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

DoCmd.SetWarnings = True

Regards,
Scott

[isoquin]

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.

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.

[kcdoell]

Thanks I will try that and get back to you both.

Keith.

[kcdoell]

Both ideas worked great!

Thank you,

Keith.

[Scott Price]

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