Required fields (1 out of 3)

[RichieP]

I know how to make one field in a table mandatory (required). How do I make one of three required? I've got three fields:

Date of conclusion OR
Duration OR
Untill further notice

The user HAS to enter one out of the three.

Help on this would be greatly appreciated!

Rich

 

[FoFa]

Use a form, check it in VBA before saving the record.

 

[RichieP]

I coudn't get this to work?

I typed this in the Before Update field on the Form properties:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([Date of conclusion]) And IsNull([Duration of order]) And IsNull([Until further notice]) Then
MsgBox "Enter Date of conclusion, Duration of order, OR Until further notice", vbOKOnly
Cancel = True
End If
End Sub

 

[FoFa]

Hopefully you did NOT include the following also.

Private Sub Form_BeforeUpdate(Cancel As Integer)

and also this

End Sub

As these should already have been there.

 

[Fornatian]

It is also possible to add table validation rules in table design suchas

[Title] Is Not Null Or [FName] Is Not Null

look at Tools-Properties, although the BeforeUpdate event offers better control.

 

[RichieP]

Sorry, I copied the whole lot, I only typed:

If IsNull([Date of conclusion]) And IsNull([Duration of order]) And IsNull([Until further notice]) Then
MsgBox "Enter Date of conclusion, Duration of order, OR Until further notice", vbOKOnly
Cancel = True
End If

.........

It still doesn't work with all your help, am I doing somthing wrong? It justs moves to the next record or exit's the form without any trouble. Also, would this make the user only be able to enter one of the three fields? I mean if, say, Duration of order was entered and the user tried to enter Date of conclusion then this shouldn't be able to happen. (One of three must be entered but ONLY one of three. An error should come up if they try to enter more than one)!!!

Thanks for your help guys

 

[RichieP]

Ummmmm, still no good. I even made a new DB with a form relating to a table to test the code but it still didn't work. Should I mention that Date of conclusion is a date field, Duration of order is a text field, and Until further notice is a yes/no field (check box) . . . . . .

Is there any chance that somebody could quickly knock up an example for me in Access 97'? That'd be a great help.

Thanks.