Highlight required fields - which method?

[Alex E.]

Hallo
I am confused as to which route to follow. I have a tabbed form with a number of required fields scattered over several tab pages. Therefore, I want to make it easier to determine where the required field that has been missed can be found.

(1) The following link/ article recommends putting code both in the On Current Property and the After Update Event Property. The code is the same for both properties.

The link: http://www.techonthenet.com/access/forms/change_bg.php

The recommended code:
If IsNull(ContactTitle) = True Then
ContactTitle.BackColor = vbRed
Else
ContactTitle.BackColor = vbWhite
End If


(2) There are also a number of references on the Net which recommend the Conditional Formatting feature; i.e. one should put: [FONT=&quot]IsNull(FieldName.Value)[/FONT] as Condition1.


(3) When I briefly looked into articles explaining validation rules, it was always pointed out that the Required Field property should be set to No. I did not find any such recommendation for the conditional formatting feature or in the link I have quoted. Is it yes or is it no for the two methods I am looking at currently?

I have a very limited understanding of code and do not want leaving bits and pieces behind after experimenting.

 

[pbaldy]

Personally I'm most likely to use Conditional Formatting. To me, setting Required = Yes would be independent of whatever method I might use to highlight the field. If a field is required, I'm going to set it to Yes. It's your protection against bad data getting in.

 

[missinglinq]

I agree with Paul, the formatting of the control to alert the user that it's required and the designation of the field as being required are two separate issues.

If you're satisfied with the Access warnings that popup when you try to save a record with blank required field(s) then declaring the field(s) required at the table level is fine. If, like many of us, you prefer warnings that are a little more user friendly, the standard ploy is to

• Leave Required set to No in the Table Definition
• Create your own validation routine in the Form_BeforeUpdate

In the Form_BeforeUpdate event you check the controls to see if it's empty (Null) and if it it is you pop up a messagebox warning the user and moving focus top the control.

BTW, using Conditional Formatting as opposed to code for the formatting has the advanyage that it works for all types of forms, whereas code only works with Single View forms, not with Datasheet or Continuous View forms..

 

[Alex E.]

Hallo
Thank you for the quick and detailed reply. It gives me a far better understanding than I had before.

Now, I am finding that I cannot get the Conditional Formatting to work. The standard error message appears but no colour shows in the text field.

Here is what I do:

• I open the form in Design View
• I select the control
• Format menu/ Conditional Formatting
• Condition1 - I select “Expression is”
• then I type the expression into the box to the right (the problem may be right there = see below)
• then I select a colour from the palette
• last I click ok

For the Expression to be entered I found 3 versions:

Version 1: [FONT=&quot]IsNull([FieldName])=True[/FONT]
Version 2: [FONT=&quot]IsNull=([FieldName])[/FONT]
Version 3: [FONT=&quot]IsNull(FieldName.Value)[/FONT]

I tried versions 1 and 2. But I did not test version 3 since I do not know what is meant by “.Value”.

Version 3 is of particular interest to me since the same article also contains a sample code for multiple fields (and that is what I have):
[FONT=&quot]IsNull(Field1.Value) Or IsNull(Field2.Value) Or IsNull(Field3.Value), etc..[/FONT]

What am I doing wrong??

 

[pbaldy]

This works for me:

IsNull([ControlName])

The app I pulled that from is unbound, which may be why I used the control.

 

[Galaxiom]

.Value is the default property, so:
IsNull(fieldname)
is the same as:
IsNull(fieldname.Value)

IsNull(fieldname) returns a boolean output, so:
If IsNull(fieldname) = true
is the same as:
If IsNull(fieldname)

 

[gemma-the-husky]

i would also be inclined to jump to the first missing field, especially if they are on tabs

ie in validate test (event/function), this sort of thing

if nz(field1, "")="" then
   msgbox("field1 not set")
   field1.setfocus
   cancel=true 'if necessary
   exit sub 
end if

if nz(field2, "")="" then
   msgbox("field2 not set")
   field2.setfocus
   cancel=true 'if necessary
   exit sub 
end if

As far as this usage is concerned the tab control is ignored - you just use the field name

 

[Alex E.]

Hallo
Thanks for the help and the explanations which I really appreciate.

I got the Conditional Formatting working. I do not know what happened when I tried it the first time but, my selections and the expression I typed did not stick. I was quite surprised when I tried again today and found the Conditional Formatting empty. Today I checked twice to make sure everything is still there.

 

[Alex E.]

Hallo gemma-the-husky

I would like to try the code you have provided. The way I understand it, I only have to substitute my field names for “field1”, “field2”, etc. And, maybe expand the message text. Is this correct ?

But the big questions is – where do I put the code?

I suspect it is either the Form’s property sheet or the Tab Control’s property sheet ?
Which of the Event Properties would I use to enter your code ?

 

[pbaldy]

A common place for that type of test is the before update event of the form. More info here:

http://www.baldyweb.com/BeforeUpdate.htm

 

[Alex E.]

Hallo Paul,
I looked at the code on your web page. I assume “Some Control” stands for a control/ field name. But, I have several required fields. The code suggested by gemma-the-husky seems to allow me to just list one control/ field after the other. How would I solve this problem of several required fields on a tabbed form using your code?

The database I am trying to put together is for a volunteer group (including me) and it is a bit of the blind leading the blind. I have to make sure that a minimum of information gets entered so that each record makes sense on its own. The people who will enter the data will not know much more than how to use the navigation bar.

 

[pbaldy]

The code was just a sample. You can test several, as in Dave's example. My point was in using the before update event for the tests.

 

[Alex E.]

Thank you Paul for your patience with my ignorance. I think I got the idea, i.e. where to put the code and that I can stack it just as the other example shows.

With an Arctic Cold Front and snow in the forecast for the weekend, I will have plenty time to get this working.

 

[pbaldy]

No problem; post back if you get stuck. It's gotten colder here, but I'm still planning on some golf this weekend.