Checking for, then listing Null/empty fields in a message box

I am new to Access, Im using 2007. I am building a data entry form that requires people to enter a score. It is a number field, 0 through 3, using a combo box. I set the default value to 3, but have "#;#;0;"NA"" input in the Format property, under the format tab. So it will display default of "NA".

I was hoping this would give me a default value of 3 but still display "NA" but instead it just leaves the feild null.

The reason for this, is that I have a scorecard where I want to keep the total possible points exactly the same, regardless of whether some fields are "NA". Otherwise you have some cards which only have a small amount of applicable fields, and if the total is only calculated by these applicable fields then getting a low score on only 1 field is very detrimental.

This is not that big of an issue, since I was able to come up with a query formula that assigns a value of 3 to all null fields.

However, this does present a validation problem for me. Since all fields default to display "NA" (I could not figure out an easier work around to having at least a visual stating the field is not applicable within a NUMBER field. If possible I would much rather be able to place a non number in the field so I can more easily pull data on "NA"s and not always have to find workarounds... so maybe that is yet another question) Anyways, since "NA" is a default and only displayed, a data entry tech can easily forget to even score a field.

On other noncombox fields, which are required, I have a conditional format which makes the field red if empty. I cannot do this with the score fields since they are not required.

I also have a msgbox macro event, set to "On Lost Focus" for the required fields" But here again, I cannot use this method for the non-required fields.

I am wondering if there is an easy way to set up a message box or something similar that will look at these nonrequired score fields, check to see if they are null or empty, and then list in the message box which fields are null/empty, and ask they data entry tech if he/she really meant to leave these fields as "NA". I'd like this to pop up either before saving, or updating, or moving to the next record.


Ideally, or as a bonus I would love if there was a checkbox by each listed null field that asked the yes/no question, and if the data entry tech pressed "yes" for a feild, then it would accept the null value, if "no" was selected, a score combo box would be right next so the proper score could be entered.

Sorry for such a long explanation. But thanks in advance.

Mike

EggHeadCafe - Software Developer Portal of Choice
Lightweight Remote Scripting with Cookies
http://www.eggheadcafe.com/tutorials...te-script.aspx