Validation Rule to not accept blank fields

[dbrasel]

Hi, hope someone can help with my validation list!

Field = Observation Kind: Formal or Informal (combo box)

Validation Rule:=[ObservationKind]=[ObservationKind] Is Not Null
Validation Text: Observation Kind Required

What is happening is that when I tab through the form, it skips the Observation Kind field altogether. I can go back and enter my choice, delete it, tab to the next field, and then I get the text message. What I want it to do is show the text if you tab through the Observation Kind field without choosing either formal or informal, and you can't move forward in the form until missing information is provided.

I'm working on Access 2000 database. Any suggestions will be greatly appreciated!

Deb

 

[jackd]

Hi, hope someone can help with my validation list!

Field = Observation Kind: Formal or Informal (combo box)

Validation Rule:=[ObservationKind]=[ObservationKind] Is Not Null
Validation Text: Observation Kind Required

What is happening is that when I tab through the form, it skips the Observation Kind field altogether. I can go back and enter my choice, delete it, tab to the next field, and then I get the text message. What I want it to do is show the text if you tab through the Observation Kind field without choosing either formal or informal, and you can't move forward in the form until missing information is provided.

I'm working on Access 2000 database. Any suggestions will be greatly appreciated!

Deb

This is from Access Help. (I am running 2003)

Open a form in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
Select a control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.), and then click Properties on the toolbar to open the control's property sheet.
In the ValidationRule property box, do one of the following:
Type the validation rule.

In a Microsoft Access database (.mdb) (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), click the Build button to use the Expression Builder to create the validation rule.

In the ValidationText property box, type the error message that you want displayed if the text entered violates the validation rule.
Note In an Access database, if you set the ValidationRule property for the field in table Design view and also set the ValidationRule property for a control bound to that field, Access enforces both rules— the control validation rule doesn't override the field validation rule.

Here is a reference: http://allenbrowne.com/ValidationRule.html

 

[dbrasel]

Jackd:

that's how I set my validation rule up. this is a form with fields on it that must have data entered in them, they can't be left empty. Is my validation rule not written correctly?

Deb

 

[jackd]

Jackd:

that's how I set my validation rule up. this is a form with fields on it that must have data entered in them, they can't be left empty. Is my validation rule not written correctly?

Deb

I don't have a lot of experience with validation rules, I did some searching to find what I posted.
Have you tried to Set the Focus to this field on your Form when you open the Form?
Or on the OnCurrent event?

 

[SydneyGeek]

If you absolutely require the control to be filled in, and it's bound to a table field, do the validation at the table level instead.

In design view in the table, select the field.
In the properties change Required to Yes, and Allow Null to No.
In the validation message enter the comment that appears when you try to save a record without entering the value.

The advantage of this is that *any* form using this field will already have the validation in place.

Denis

 

[dbrasel]

Denis,

Is "Allow Zero Length" the same thing as Allow Null? I'm not seeing the Allow Null field in the properties of my table.

Deb

 

[rconverse]

I beleive so. This will not allow the record to be updated if you set allow zero length to no.

Thanks,
Roger

 

[SydneyGeek]

Denis,

Is "Allow Zero Length" the same thing as Allow Null? I'm not seeing the Allow Null field in the properties of my table.

Deb

As Roger said, Yes it is. Sorry, I didn't have Access in front of me so I was going by memory.

Denis

 

[dbrasel]

Denis,

I've done as you suggested, no message and data is not being saved to the table. I want the message to pop up when user tries to tab to the next field without entering data in the first field. Does it matter that the form is based off of a query, or that the field is a combo box with two choices?

I'm banging my head against the wall on this one! Sure appreciate your help! Any suggestions?

Deb

 

[rconverse]

On the lost focus event of your combo box put in:

if isnull(me.cmbYourName.value) = true then

     msgbox "You must choose Informal or Formal.", vbcritical, "Error"

     me.cmbYourName.setfocus True

     Exit Sub

end if

HTH
Roger