Solved

Check Access Form For Missing Data Programmatically

Posted on 2013-10-22
4
981 Views
Last Modified: 2013-10-22
I have created a form in Access 2007 with about 30 fields (employee information) that I would like to add some code to that can cycle through a number of fields to check if they have data in them. I want to be able to display message box that informs the user to enter data in the text box that is missing information.

Form is linked to a webservice that fills in 90% of the info for the user but there is still a bunch of fields that I want to ensure that have data. I began coding an if statement but it was way to much to type since it contained so many "OR"s and also I want to use the same code on other forms.

I am looking for some code that will cycle through a number of fields using maybe a LOOP and that opens a dialog box that specificlly names the fields that need to be filled in. If at all possible I would love for the code to be dynamic enough to using in other forms throughout the application.
Any assistance with this would be much appreciated. Thank you.
0
Comment
Question by:spaced45
[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
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39593173
This is an example of how to highlight the empty required items in red.


 
Private Function CheckForRequiredItems(frm As Form) As Boolean
Dim cntrl As Control
CheckForRequiredItems = False
For Each cntrl In frm.Controls
    If InStr(cntrl.Tag, "Required") Then
        If IsNull(cntrl) Then
            cntrl.BackColor = 8421631
            CheckForRequiredItems = True
        Else
            cntrl.BackColor = 16777215
        End If
    End If
Next cntrl
End Function

Open in new window


To use the function, put Required in the required control's tag property then call it from the form's before update and unload events like this:

Private Sub Form_Unload ()
If CheckForRequiredItems(Me) Then
   MsgBox "The items in red are required"
   Cancel = True
End If
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:spaced45
ID: 39593187
Coach!! Worked like a charm! Thank you very much for all your assistance!!
0
 
LVL 1

Author Closing Comment

by:spaced45
ID: 39593188
Excellent solution
0
 
LVL 21
ID: 39593192
You're welcome.  Good luck with your project.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 …
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…
Suggested Courses

631 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