?Multiple Validation Rules for Table?

[babamc4]

Hi Experts,

I have just completed designing a new DB and my boss has asked me to put some complicated (I think!) validation rules on to it. My table is below:

1. Index - Autonumber
2. Hospital Number - Text (FK)
3. Unexpected Adverse Event - Text (with combo box)
4. SAE - Yes/No
5. Date event start - Date/Time
6. Date event end - Date/Time
7. Grade - Text (with combo box)
8. Event ongoing - Yes/No
9. Event resolved - Yes/No
10. Treatment required - Yes/No
11. Comment - memo

What my boss is looking for is:
1. 'Event ongoing' cannot be ticked if there is no date in for 'event end'
2. 'Event resolved' is automatically ticked if there is a date in for 'event end'
3. 'Event ongoing' cannot be ticked if 'event resolved' is ticked.

I have tried searching for how to do this and the only thing that I have come up with is a table validation rule but it seems that you can have one rule for this.

Can anyone point me in the right direction, am I looking for a validation rule or is it code that I need.

As usual thanks a million for your time.

Baba

[ADezii]

You may be better off setting the Validation Tests in the BeforeUpdate() Event of a Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Debug.Print Me![Event End] & " ==> & Me![Event Ongoing)"
If IsNull(Me![Event End]) And Me![Event Ongoing] = True Then
  MsgBox "If there is no Event End Date, then Event OnGoing " & _
         "cannot be checked", vbExclamation, "Rule Violation"
  Cancel = True
    Exit Sub
ElseIf Not IsNull(Me![Event End]) Then
  Me![Event Resolved] = True
ElseIf Me![Event Resolved] = True Then
  Me![Event Ongoing] = False
Else
  'fall through
End If
End Sub
 

[missinglinq]

As has been said, this type of validation is best done at the form level, and in point of fact, all data entry should be done thru a form. Allowing users to enter data directly into a table, as your table field list suggests, is a sure recipe for corruption.

Welcome to Bytes!

Linq ;0)>

[babamc4]

Hi All

Sorry I should have said I have a mainform with the above table as a subform on it. All the data will be entered via the mainform as I will be putting Allen Browne's Audit trail on to the DB.

Can I use code along with the audit trail?

Is the table safe from corruption if data entry is done via forms?

Thanks again for your replies

Baba