Table field mandatory if

[Guest]

I have a two fields in a table. The fields are DescID and DescSub.

I need to make DescSub a mandatory field only if DescID is a 5, 7 or 9.

In the table you can only choose Required yes or no.

Is there another way this can be accomplished?

Thanks bunches,

 

[Arvin Meyer [MVP]]

You cannot employ triggers in an Access database so the only way you can do
it is through an Access form. 99,999% of the time that the database is in
use, that is the preferable method of adding individual rows of data. In the
form's BeforeUpdate event write a piece of code to force the user to enter
the data. Here's an aircode sample:

Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Handler

Select Case Me.DescID
Case 5, 7, 9
If Len(Me.DescSub) = 0 Then
MsgBox "The DescSub field is mandatory", vbOKOnly, "Data
Required"
Me.DescSub.SetFocus
Cancel = True
Exit Sub
End If
Case Else
End Select

Exit_Here:
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

 

[John W. Vinson]

I have a two fields in a table. The fields are DescID and DescSub.

I need to make DescSub a mandatory field only if DescID is a 5, 7 or 9.

In the table you can only choose Required yes or no.

Is there another way this can be accomplished?

Thanks bunches,

You can use a Table Validation rule: open the table in design view, right
mouseclick it, and use the Validation Rule and Validation Text properties.

([DescID] IN (5,7, 9) AND DescSub IS NOT NULL)
OR ([DescID] NOT IN (5, 7, 9)

Put something reasonable in the table's ValidationText property.

Better might be to do this validation in your data entry Form's BeforeUpdate
event - the message will be friendlier and easier to maintain.

John W. Vinson [MVP]