Solved

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

Posted on 2011-03-15
3
1,189 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 62 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 63 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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