Custom Message for Required Fields. 4

[InfoNow]

I have a field called Description in a table. It is set as required. When a user leave this field blank in a form, a message stating that is field cannot be null pops up.
What I would like is to customize this message. Is there any way to do this?

TIA

 

[Rick39]

What I do is to put "Validate" in the Tag property of the control(s), create a txtbox named BackColor (set it's default value to 16777215 {white}) and insert the following into the Save button's event procedure:

Dim currctl As Integer, numctls As Integer
Dim ctl As Control

numctls = Screen.ActiveForm.Count

For currctl = 0 To numctls - 1
Set ctl = Me(currctl)
' Check the status of Controls with the Validate Tag
If ctl.Tag = "Validate" Then
If IsNull(ctl) Then
MsgBox "Please fill in the field(s) highlighted in red and click the Save button again.", vbOKOnly + vbCritical + vbDefaultButton1, "FIELD(S) EMPTY"
ctl.BackColor = 255
ctl.SetFocus
Exit Sub
ElseIf Not IsNull(ctl) Then
ctl.BackColor = Me!BackColor.BackColor 'Set backcolor of control back to original
End If

End If
Next currctl
End If


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

This will look at all controls on the form and turn an empty control (with the Validate Tag) red if it is not filled in and abort the save until it is.

 

[InfoNow]

Thanks Rick,
I will give this a go.

 

[JoyInOK]

There's a thread on a similar topic at
thread702-167591
that seemed to be helpful

 

[InfoNow]

Hi Rick
I just tried it out. It's giving me a message "End If without Block If." Any idea??

TIA

 

[Rick39]

My mistake - delete the last End If in the code - should be:

Dim currctl As Integer, numctls As Integer
Dim ctl As Control

numctls = Screen.ActiveForm.Count

For currctl = 0 To numctls - 1
Set ctl = Me(currctl)
' Check the status of Controls with the Validate Tag
If ctl.Tag = "Validate" Then
If IsNull(ctl) Then
MsgBox "Please fill in the field(s) highlighted in red and click the Save button again.", vbOKOnly + vbCritical + vbDefaultButton1, "FIELD(S) EMPTY"
ctl.BackColor = 255
ctl.SetFocus
Exit Sub
ElseIf Not IsNull(ctl) Then
ctl.BackColor = Me!BackColor.BackColor 'Set backcolor of control back to original
End If

End If
Next currctl



DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

 

[InfoNow]

Rick,
It works now. I commented the last End IF statement and it works great.
Just what I was looking for.

Thanks again.

 

[Rick39]

Glad it helped

 

[JoyInOK]

InfoNow, how about giving Rick39 a star for being so quick on the draw?

 

[Rick39]

Joy,
Not necessary, but thanks for the thought!

 

[WildHare]

Or, you could let the database take care of it. Set the VALIDATION RULE to "IS NOT NULL", and set a VALIDATION TEXT that contains the admonishment you wish to lay on your users.

Since a VALIDATION RULE can be trapped easier, I generally prefer to do it this way rather than set the REQUIRED to YES.

You can also trap a null entry on a form right away with the control's EXIT or LOST FOCUS event, and don't even let 'em go past the field...

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[InfoNow]

Joy,
I would love to!! How do I go about doing that?

TIA

 

[InfoNow]

oh, I see it now.

Rich, deserved two starts! Thanks again for your fast help.

 

[CTOROCK]

this is good

"The greatest risk, is not taking one."

 

[jdegeorge]

Rick

I'm liking this code. I tried to use it but it didn't trap one of my fields. Then I noticed that the field was numeric and your code only trapped text. I modified it as follows:

[tt] Dim currctl As Integer, numctls As Integer
Dim ctl As Control

numctls = Screen.ActiveForm.Count

For currctl = 0 To numctls - 1
Set ctl = Me(currctl)
' Check the status of Controls with the Validate Tag
If ctl.Tag = "Validate" Then
If IsNull(ctl) Or ctl = 0 Then
MsgBox "Please fill in the field(s) highlighted in red and click the CLOSE button again.", vbOKOnly + vbCritical + vbDefaultButton1, "FIELD(S) EMPTY"
ctl.BackColor = 255
ctl.SetFocus
Exit Sub
ElseIf Not IsNull(ctl) Then
ctl.BackColor = Me!BackColor.BackColor 'Set backcolor of control back to original
End If
End If
Next currctl[/tt]

This worked great if the user then clicked the CLOSE button to exit the form. If the user fixed the trapped errors and then tabbed through to a blank input screen to enter a record, the last trapped field remained red!

How do you set BackColor back to white in this instance?

Jim DeGeorge

 

[jdegeorge]

Rick

I even changed this line:

ElseIf Not IsNull(ctl) Or ctl <> 0 Then

and that didn't help.

I give in!

Jim DeGeorge

 

[Rick39]

I don't beleive that you can change the backcolor of a checkbox but here's a possible work around:

Create a textbox named txtChecked, size it, set the font size to whatever size you want), set Enabled and Locked properties to Yes, and add this code to the On Click event:

If Me![txtChecked].Value = &quot;X&quot; Then
Me![txtChecked].Value = &quot;&quot;
ElseIf Me![txtChecked].Value = &quot;&quot; Then
Me![txtChecked].Value = &quot;X&quot;
End If

You can now set the Tag property of txtChecked for use with the other code....

Hope this helps

Rick

 

[jdegeorge]

Rick

Is this a response to my question? If so, I didn't say anything about a check box.

What I'm trying to do is use your really great code but figure out how to return the trapped fields back to white after they're updated/edited, without having to do that in code in each field's AfterUpdate or OnChange event.

The generic code tied to the Close button is really super!

Jim DeGeorge

 

[Rick39]

My mistake.....I originally attached the validation code to a Save button (it always reset validated fields), but if you're going straight to the close button, you may want to make reference to a save routine in the close button's On Click event ( or the On Close event)

 

[jdegeorge]

Rick

The red coloring is a nice touch, but without it the code does what I need to do as part of the Close button's OnClick event. So I don't believe that I'll need a save routine.

Thanks, and enjoy another star!

Jim DeGeorge