Alternative error message

[Mgomp]

Hi all.

I just can't find soultion for the following prooblm:

On the table - field proprties, I have set on of the field to be Required and not to alow Zero Length values.

On the form, when skipping entering a value in the bound field, I get the standard system error message, but I would like change this message, because it refernces to a cryptic field name, and not to field name like "Enter First Name".

I have tried many combinations on IsEmpty and IsNull in the field LostFocus and AfterUpdate events, and I have tried to use the Validation Rule - but all those will not cover the situation where I jump from field to field with the TAB-button, or just go to an another record without entering the field at all.

So, does anybody have a solution for usining the table validation, but with an another error message?

Regards,

 

[missinglinq]

There are a number of ways to do this but I wouldn't use the Required at Table level because of the enigmatic error messages you mentioned. Instead, many developers simply use the Form_BeforeUpdate event to validate, prior to saving the Record, that a Control or Controls have not been ignored. Here's an example:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 If Nz(Me.Control1,"") = "" Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If
 
If Nz(Me.Control2, "") = "" Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If

End Sub

Note that the line

If Nz(Me.Control1,"") = "" Then

checks for both Nulls and Zero-Length Strings, which IsNull() doesn't.

Linq ;0)>

 

[SmallTime]

Thanks for that pointer missinlinq.

There go all the isNull's that I've been using.

SmallTime

 

[Mgomp]

Hi Missinglinq.

Thank You very much for your solution! It work perfectly - just the way I needed!

And as SmallTime wrote, I will go through the whole app and check out all use of the IsNull function used (and there are several...)

Regards,

 

[missinglinq]

Glad we could help! As for using Nz in that way as opposed to IsNull, most experienced developers do eschew the IsNull, but it really depends on your app and how data is being entered.

If data is entered physically, i.e. thru the keyboard or by pasting data in, the chances of a Zero-Length String getting in is slim. Using the Space Bar to enter all 'spaces' won't create a ZLS. Neither will using Backspace to delete data that has been entered nor will highlighting and hitting the Delete key. All of these situations will still leave the Control in a Null state, even if the Allow Zero-Length Strings is set to Yes in the Table Defs.

But some apps do allow for ZLS in Fields and data can be imported from these apps, either directly into Access Tables or into Tables used as the RowSource for Comboboxes/Listboxes, and can, in some cases, end up populating the Controls with ZLSs, which could cause problems.

Rather than having to think about this each time they do this kind of Validation, most developers simply use

If Nz(Me.Control1,"") = "" Then

or another, similar construct, such as

If Len(Me.Control1 & vbNullString) = 0 Then

all the time.

Linq ;0)>

 

[Galaxiom]

BTW. In answer to the original question about changing error messages, it can be done.

Although best avoided by using the techniques already posted, there are circumstances where there is no alternative such as with an Input Mask on a control. None of the usual solutions like BeforeUpdate get a look in before the Input Mask error fires.

In this circumstance use the OnError event which calls the Form_Error procedure.

http://support.microsoft.com/kb/210343

 

[missinglinq]

Sure! As I said, there are a number of ways to do this. And as my signature says, "There's ALWAYS more than one way to skin a cat!"

Here's an example for a couple of Date Fields, with Control-Specific error messages:

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

If DataErr = [B][COLOR="Red"]2279[/COLOR][/B] Then 'Data not entered as Input Mask requires

If Me.ActiveControl.Name = "StartDateField" Then
 Response = MsgBox("The Start Date Field Must Contain a Valid Date!", vbExclamation, "Not a Valid  Date!")
 Response = acDataErrContinue
 Me.ActiveControl.SelStart = 0
End If

If Me.ActiveControl.Name = "EndDateField" Then
 Response = MsgBox("The End Date Field Must Contain a Valid Date!", vbExclamation, "Not a Valid  Date!")
 Response = acDataErrContinue
 Me.ActiveControl.SelStart = 0
End If

End If

End Sub

For other Errors you'd simply replace 2279 with the appropriate Error Number.

Linq ;0)>

 

[Galaxiom]

As a general principle I try to avoid the error capture unless it is the only option.

A minor point on this line:

Response = MsgBox("The Start Date Field Must Contain a Valid Date!", vbExclamation, "Not a Valid  Date!)

Where the return from the message box is not used it is better practice to use it like a Sub rather than a function.

MsgBox "The Start Date Field Must Contain a Valid Date!", vbExclamation, "Not a Valid  Date!

The implication of using MsgBox function with a return code is that you are preparing to respond to the user's selection.

In this particular case the return is then obviously discarded as Response is immediately overwritten. However this my not always be the case.

Of course the single OK button also indicates the case but it is more obvious what is going on if the return code is discarded in the original line.

 

[smig]

do NZ also check for strings like " " (spaces) ?
or do I need to use:
If Nz(Trim(Me.Control1),"") = "" Then

 

[Galaxiom]

Nz checks only for The Null. Not Null Strings and definitely not white space so you need to Trim if you want to avoid spaces.