?
Solved

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

Posted on 2011-03-15
3
Medium Priority
?
1,275 Views
Last Modified: 2013-11-27
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
Comment
Question by:dcmennealy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Expert Comment

by:wipnav
ID: 35144061
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
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 248 total points
ID: 35144084
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
 

Accepted Solution

by:
Raland9966 earned 252 total points
ID: 35202103
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question