Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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,401 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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: …

609 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