PLEASE HELP - I am 2 hours past my deadline...

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

When using a form - how do i make access run a validaition
rule with out ever entering the field... ie I setup a form
with 23 fields - I need 3 of these fields to be required,
but I do not want to use the required field command in the
table design view - because to my knowledge you can not
customize the error message. That being said I created
validation rules in each required field ( > " "). I then
set up my validation text. The problem that I am having
is that the validation rule is not running unless you
enter the field and type text. How do make it check the
field if the field is skipped completely...
 
Hi,
Have you tried putting the validation code in the form's
Before Update event?
 
Mike,
Enter the expression,
Is Not Null
in the Validation Rule property of either the table or the
form containing the field. Enter the text you want to
display in the Validation Text property.
 
When using a form - how do i make access run a validaition
rule with out ever entering the field... ie I setup a form
with 23 fields - I need 3 of these fields to be required,
but I do not want to use the required field command in the
table design view - because to my knowledge you can not
customize the error message. That being said I created
validation rules in each required field ( > " "). I then
set up my validation text. The problem that I am having
is that the validation rule is not running unless you
enter the field and type text. How do make it check the
field if the field is skipped completely...
Click to expand...

You can use code in the form's "BeforeUpdate" event procedure to make sure there
is data in *all* of the "required" fields:

'*****
If Len(Trim([Field1]) & Trim([Field2]) & vbnullstring) = 0 Then
Cancel = True
Msgbox "You have not filled in a required field."
End If
'*****

.... or, if you want to get fancy (and helpful) ...

Set the "Tag" property for each bound control that you want to check for data to
something like "reqd" and then use code similar to the following (again, in the
form's "BeforeUpdate" event procedure):

'*****
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.Tag = "reqd" Then
If Len(ctl.value & vbnullstring) = 0 Then
Cancel = True
MsgBox "You must enter a value into """ & ctl.Controls(0).Caption &
"""."
ctl.setfocus
exit sub
End If
End If
Next
'*****
 
All these solutions are quite educational and very fancy.
It appears we are using a sledge hammer to drive in a nail.
I use the isnull function in the before update event.

If IsNull(Me![startdate]) Then
Beep
MsgBox "*** Start Date must be entered ***"
else .....

Jim
 
All these solutions are quite educational and very fancy.
It appears we are using a sledge hammer to drive in a nail.
I use the isnull function in the before update event.

If IsNull(Me![startdate]) Then
Beep
MsgBox "*** Start Date must be entered ***"
else .....
Click to expand...

Unfortunately, you didn't state which "BeforeUpdate" event you were referring
to, nor does your code prevent the record from being saved without the required
data, nor does your validation check accommodate fields that do not contain
data, but are not null (as in fields where "Allow Zero Length" is set to "Yes").
So ... maybe one shouldn't use a cooked spaghetti noodle to drive that nail,
either. <g,d&r>

By the way, that "Beep" was a nice touch. <g>

:-)
 
Looks like you got some help already but I thought I would
give you another way of doing the validation.

I often have an 'Update' or 'Save' or 'Go' button. I then
check the various fields that I must have filled out
before proceeding. (I don't validate each field as it is
entered) This way I can control the message and return the
cursor to the appropriate field for updating.

You can also test(if necessary) for other validation other
than null. me.field1 > X or me.field2 is > today's date
or field1 <> ?? -- you get the idea.

Example: (and I happen do do this for each required field)
if isnull(me.field1) = true then
msgbox "This field is ....." < custom message
me.field1.setfocus
Exit sub
end if

if isnull(me.field2) = true then
msgbox "This field is ....." < custom message
me.field2.setfocus
exit sub
end if
 
Bruce thanks very much - but I am having problems with
your code below - The MSG section is red on my screen -
please help -- thanks in advance


Dim ctl As Control
For Each ctl in Me.Controls
If ctl.Tag = "reqd" Then
If Len(ctl.value & vbnullstring) = 0 Then
Cancel = True
MsgBox "You must enter a value into """ &
ctl.Controls(0).Caption &
"""."
ctl.setfocus
exit sub
End If
End If
Next



-----Original Message-----
When using a form - how do i make access run a validaition
rule with out ever entering the field... ie I setup a form
with 23 fields - I need 3 of these fields to be required,
but I do not want to use the required field command in the
table design view - because to my knowledge you can not
customize the error message. That being said I created
validation rules in each required field ( > " "). I then
set up my validation text. The problem that I am having
is that the validation rule is not running unless you
enter the field and type text. How do make it check the
field if the field is skipped completely...
Click to expand...

You can use code in the form's "BeforeUpdate" event procedure to make sure there
is data in *all* of the "required" fields:

'*****
If Len(Trim([Field1]) & Trim([Field2]) & vbnullstring) = 0 Then
Cancel = True
Msgbox "You have not filled in a required field."
End If
'*****

.... or, if you want to get fancy (and helpful) ...

Set the "Tag" property for each bound control that you want to check for data to
something like "reqd" and then use code similar to the following (again, in the
form's "BeforeUpdate" event procedure):

'*****
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.Tag = "reqd" Then
If Len(ctl.value & vbnullstring) = 0 Then
Cancel = True
MsgBox "You must enter a value into """ & ctl.Controls(0).Caption &
"""."
ctl.setfocus
exit sub
End If
End If
Next
'*****

--
Bruce M. Thompson
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.
Click to expand...
 
mike said:
Bruce thanks very much - but I am having problems with
your code below - The MSG section is red on my screen -
please help -- thanks in advance


Dim ctl As Control
For Each ctl in Me.Controls
If ctl.Tag = "reqd" Then
If Len(ctl.value & vbnullstring) = 0 Then
Cancel = True
MsgBox "You must enter a value into """ &
ctl.Controls(0).Caption &
"""."
ctl.setfocus
exit sub
End If
End If
Next
Click to expand...

The code line got wrapped by the newsreader. Try it like this:

MsgBox "You must enter a value into """ & _
ctl.Controls(0).Caption & """."
 
The code line got wrapped by the newsreader. Try it like this:
MsgBox "You must enter a value into """ & _
ctl.Controls(0).Caption & """."
Click to expand...

Thanks, Dirk. I though I was just under the linewrap point, but I guess I
wasn't.

:-)
 
Back
Top