Solved

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

Posted on 2011-03-15
3
1,100 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying 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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

749 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