Table field mandatory if

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
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
 
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,
Click to expand...

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]
 
Back
Top