• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1503
  • Last Modified:

MS Access - Make a field mandatory based on the value of another field in form

I am have a form called APTT and there are two fields - Requirements and Other Explanation.  I want to make the "Other Explanation" mandatory if the person selects "Other" in the "Requirements" field.  I have little knowledge of VBA but I am learning.  How do I do this?
0
dcmennealy
Asked:
dcmennealy
2 Solutions
 
wipnavCommented:
If you create a save button you can do something like this. It depends though, how is your form set up - is it bound or unbound? When do you want the event to execute?
Private Sub cbSave_Click()

    If Me.tbRequirements.Value = "Other" And IsNull(Me.tbOtherExplanation.Value) Then
        MsgBox "Other explanation required.", vbCritical
    Else
        DoCmd.Save
    End If
            
End Sub

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Use the form's Before Update  event to perform teh evaluation.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)

 If Me.txtRequirements = " Other" and IsNull(Me.txtOtherExplanation) Then  
    
  Msgbox "Other Explanation is mandatory"
  Cancel = True
  Me.txtOtherExplanation.SetFocus 

End If

End Sub

Open in new window

0
 
Raland9966Commented:
The previous examples answers your question but checking that the user has filled in all required fields is something you're going to be doing a lot. Here is a little more complex version. Even if you don't use it today it will give you something to build on. The advantage to this is you don't have to specifically write your code for every control you want to be required.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim ctl As Control
   Dim frm As Form
   
   Set frm = Me.Form
   For Each ctl In frm.Controls
      If ctl.Tag = "Required" Then
         If IsNull(ctl.Value) Or ctl.Value = "" Then
            ctl.SetFocus
            MsgBox "Other Explanation is mandatory"
            Cancel = True
         End If
      End If
   Next
End Sub

You just set the .Tag property of the control to "Required" either staticly if it's required all the time or dynamically based on other controls. In your case you would use this.

Private Sub tbRequirements_AfterUpdate()
   If Me.tbRequirements.Value = "Other" Then
      Me.tbOtherExplanation.Tag = "Required"
   Else
      Me.tbOtherExplanation.Tag = ""
   End If
End Sub
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now