Solved

Mandatory field in Access 2003 form

Posted on 2013-01-28
2
358 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
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 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