Create a Validation Rule

[Valery]

Hi,

I have two fields:

1) App_AsNeeded - Yes/No field
2) App_Frequency - Number field

I would like the user to NOT be able to enter data in both fields at the same time. Therefore, if the Yes/No field is "ticked" - you should not be able to enter data (a number) in the App_Frequency field and, vice-versa.

Also there HAS TO BE data in ONE of the two fields. They should not both be left null.

I have no idea how to write this in the table's (called Appointments) validation rule property.

Thank you in advance for your help

 

[pbaldy]

I can't either! Personally I'd use the before update event of the form:

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

and code your logic there.

 

[Valery]

Hi, ty for answering. I cannot transform what you provided me with into what I need. I understand where to put it - BeforeUpdate event - but can't write this. Here is what you provided:

If Len(Me.SomeControl & vbNullString) = 0 Then
MsgBox "You need to fill out SomeControl"
Cancel = True
Me.SomeControl.SetFocus
End If

 

[pbaldy]

Just code the logic you've described. In pseudo-code:

If Both Fields are Null then
cancel
ElseIf Both fields have data then
cancel
End If

Adjusting as appropriate. Dive in and try it.

 

[Valery]

I have spent the last week trying to do this, thinking of it as a challenge. But I don't know VBA. I take existing codes and adapt them.

Can someone please help and provide me with a full code?

THANK YOU - much appreciated

 

[Galaxiom]

Having both fields in the table would be a normalization error.

App_AsNeeded could be indicated by a Null in the App_Frequency field. However this would leave the possibility failed to enter the data.

Deal with this situation by entering an out of scope value such as -1 as the frequency.

The validation becoems easier. Simply Not Null in the textbox.

You could still have the checkbox (unbound) on the form. With the appropriate code, ticking it would enter the -1 in the textbox.

The textbox can be made to display "As Needed" for negative values by putting the following in its Format property:
;;"As Needed"

The unbound checkbox could be checked automatically for existing records in the Current Event of the form.

The Update Event of the textbox could be used to automatically uncheck the checkbox if a number is entered.

 

[Valery]

Sounds great, I think.

OK - I enter what, where? In the field(s), in the table, on the form???
Step by step - please - with exact coding and it's position.

TY

 

[Galaxiom]

Let's sort the terminology first. In Access, fields are in the tables. The form has controls (textboxes, butttons etc).

The table only needs the field for the App_Frequency.

On the form is a bound textbox. Here the operator enters the frequency. If the frequency entered is -1 then that indicates what is currently indicated by App_AsNeeded. A frequency of -1 is otherwise meaningless so it can be used to indicate something. Zero would work just as well.

Validation just tests the textbox isn't Null, which would be the case if the operator forgot to enter something. You might want to watch out for other negative values too but you probably get the idea.

It could be as simple as that but the minus one would need to be explained to every new operator. So keep the checkbox on the form but remove its ControlSource property making it unbound. Its value won't be stored iin the table.

Use the Checkbox AfterUpdate event to test its value. Write -1 to the textbox if the checkbox is ticked and Null to it if the checkbox is unticked.

Use the AfterUpdate event of the textbox to run a sub that checks its value. If it is -1 then check the checkbox otherwise uncheck it.

Run the same sub with the OnCUrrent Event.

Note that because the checkbox is unbound the checkbox enhancement only works on SingleForms.

However getting rid of the App_AsNeeded field from the table is important to maintain normalization but you will have to adjust any queries and reports to suit.

Getting the normalization right invariably simplifies problems. As we see here, even validation problems.

 

[Pat Hartman]

The solution Galaxiom provided is the way I would do it also but for future reference - Validation rules are quite limited in what they can do. Starting with the rule for any column can only reference that column. You can't create a rule that says DateA must be less than DateB. You can create a rule that says DateA must be less than today.

The other thing to keep in mind is that validation rules are in the database and therefore, they are enforced by the database engine. The database engine only knows Jet/ACE SQL. it doesn't know VBA. That means you can only use native Jet/ACE SQL functions. You can't use VBA functions or user defined functions. The validation rule must be able to run even if Access is not installed on the PC. Remember, Jet/ACE are independent products and can be used by any software that can use ODBC so the database may be used by a JavaScript applet running in a web browser. The validation rule will still be enforced.

You can create table level validation rules and those can reference any column in the table. There is no logical interface for creating these rules and frankly, I would never create one. Most people don't know it is possible and your successor (or even yourself next year) would have a hard time finding where this rule is defined. A2010 provides Data Macros which are the equivalent of triggers in SQL Server, et al. Those are a better option since they are more flexible and better defined.

For my own apps, I stick to form level events since my applications frequently need to switch between ACE and SQL Server and I don't want to have to maintain Data Macros as well as triggers. If I were to create a database that needed to be used by non-Access applications, I would probably go with SQL Server rather than ACE and use triggers to implement business rules if both apps were allowed to update.

 

[Valery]

Ok - I have "merged" the two fields into one. That is, the user will put in "0" in the number of days (app_freq) and it will mean that it it "app_asneeded" (as if that field had been ticked).

So, in a way, problem solved!

Thank you all.