Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Mandatory field in Access 2003 form

Posted on 2013-01-28
2
Medium Priority
?
364 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 1000 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

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…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

885 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