Solved

Mandatory field in Access 2003 form

Posted on 2013-01-28
2
360 Views
Last Modified: 2013-01-28
Hi all.

I have an Access data entry form that are end users enter some information into. There are a total of 30 fields, of which there are about 20 that are mandatory. They cannot be left blank. How can I check the form before the end user hits the save button to make sure all of those 20 are filled in.

If any fields are not filled in I would like to fill in the text a pink color. If let's say there are 5 that are left blank then those 5 fields should be filled in pink.

Thank you in advance!
0
Comment
Question by:printmedia
[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
2 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 38826990
try this codes in click event of your button

Dim sCtlName As String, NullCtl As String, i As Integer
sCtlName = ""
Dim ctl As Control
For Each ctl In Me.Controls


    If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
            If IsNull(Me(ctl.Name)) Or Len(Me(ctl.Name)) = 0 Then
                sCtlName = sCtlName & ctl.Name & ";"
                Else
               
            End If
    End If



Next ctl
If Len(sCtlName) > 0 Then
    NullCtl = Mid(sCtlName, 1, InStr(sCtlName, ";") - 1)
    Me(NullCtl).SetFocus

    msgbox "Please complete all fields!"
    Exit Sub
    Else
   
End If 

Open in new window

0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 38827077
Note that if you have some fields which are mandatory while others are not, you'll have to modify cap's code a bit.

Many use the Control's "Tag" property to determine which controls are to be included in the module. For example, I might include a value of "mand" in the Tag property of each control where I want to check. If I did that, then I'd modify cap's code like this:

 For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
      If ctl.Tag = "mand" then
            If IsNull(Me(ctl.Name)) Or Len(Me(ctl.Name)) = 0 Then
                sCtlName = sCtlName & ctl.Name & ";"
                Else
               
            End If
      End If
    End If
Next ctl

This would only consider those controls with a value of "mand" in their Tag.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

734 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