Making a field required based on the value of another

[WatsonDyar]

I have a subForm set to continuous forms. I need a field called txtStatusDetails required (cannot be null) if the field txtDutyStatus = "Not Available"

Here's what I have so far:

Private Sub Form_Current()
' This procedure runs after the record changes.
On Error GoTo Err_Handler

ResetAvailability

Exit_Proc:
On Error Resume Next
Exit Sub

Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "Form_Current()"
Resume Exit_Proc
End Sub

Private Sub txtDutyStatus_AfterUpdate()
' This procedure runs after the record changes.

On Error GoTo Err_Handler

Me.txtAvailability = Null

ResetAvailability

If Me.txtDutyStatus = "Not Present for Duty" Then
Me.txtAvailability = "Not Available"
ElseIf Me.txtDutyStatus = "Fulfilling Provisional Duties" Then
Me.txtAvailability = "Not Available"
ElseIf Me.txtDutyStatus = "Fulfilling Primary Duties" Then
Me.txtAvailability = "100% Availability"
End If

Exit_Proc:
On Error Resume Next
Exit Sub

Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "txtDutyStatus_AfterUpdate()"
Resume Exit_Proc
End Sub

Private Sub ResetAvailability()
' This procedure runs after the record changes.
On Error GoTo Err_Handler

Dim strSQL As String

Select Case Me.txtDutyStatus
Case Is = Null
strSQL = "SELECT tDSAvailability.dsAvailabilityID " _
& "FROM tDSAvailability " _
& "ORDER BY tDSAvailability.dsAvailabilityID DESC;"
Case "Fulfilling Primary Duties"
strSQL = "SELECT tDSAvailability.dsAvailabilityID " _
& "FROM tDSAvailability " _
& "WHERE (((tDSAvailability.dsAvailabilityID)<>'Not Available')) " _
& "ORDER BY tDSAvailability.dsAvailabilityID DESC;"
Case Else
strSQL = "SELECT tDSAvailability.dsAvailabilityID " _
& "FROM tDSAvailability " _
& "WHERE (((tDSAvailability.dsAvailabilityID)='Not Available')) " _
& "ORDER BY tDSAvailability.dsAvailabilityID DESC;"
End Select

Me.txtAvailability.RowSource = strSQL

Exit_Proc:
On Error Resume Next
Exit Sub

Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "ResetAvailability()"
Resume Exit_Proc
End Sub

 

[pbaldy]

I'd use the before update event, modifying the test as appropriate.

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

 

[WatsonDyar]

Perfect. Thanks!

 

[pbaldy]

Happy to help!