How to get user to fill out a form without leaving any fields blank.

Posted on 2011-03-08
Medium Priority
Last Modified: 2013-11-28
I have a form that user fill out. Each field must have a record. How do I stop the user from skipping a field and leaving it blank?
Question by:gigifarrow
  • 3
LVL 22

Accepted Solution

Kelvin Sparks earned 500 total points
ID: 35073275
There are a variety of ways to achieve this. Possibly the simplest if to use the forms before Update event and test each field with code i.e.

If IsNull(me.fieldname) then
    Msgbox "you must enter data into the field dieldname"
    Cancel = True
     Exit sub
End if

Repeat this for each field

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 35076392
Well you can't easily prevent them from "Skipping a field".
(This would require you to create code to force the user into a specific Tab Order, and is, IMHO, not worth the trouble, as this will frustrate users.)
Also remember that there are times when a value is just simply not going to be known at the time the record is created.
This will "Force" user to enter "Anything" just to get past your "Alerts"
(Invalid data is worse than missing data in some cases)
Also realize that a user can hit the space bar (or period, ...etc) to easily defeat your "All fields must be filled in" logic.

Finally you will have to clearly define what you mean by "Blank"

All this being said, another technique would be to set the Required Property of each field in the source table to: Yes



Author Comment

ID: 35087571
This code works but it doesnt prevent them from going to another field. The message comes up,but you can still type into the next field.

Private Sub DC_PIN_Click()

If IsNull(Me.DC_PIN) Then
    MsgBox "You must enter data into the field"
    Cancel = True
     Exit Sub
End If
End Sub
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35087938
That is what we were trying to tell you.
With Kelvin's suggestion of using the Before Update event of the form (Not a click event)
...and with my suggestions on the potential interface pitfalls, or using the Required property of the field in the table.

Remember, in a website Order form, it will "Accept" all of your Order fields, then when you click "Submit", it will alert you of any missing or invalid data.
It won't "Stop" or "Alert" you on each field (Again, very annoying and Non-standard)

Don't get me wrong, ...what you are asking can be done, but sometimes it creates other issues, and is not worth the trouble.
Anything beyond that may require somewhat custom coding for all your contingencies and anticipated user actions.
(Also keep in mind that you never answered my question about whet you are considering "Blank")

In any event, try something like this on the *Before Update event of the form*

Dim ctl As Control
    For Each ctl In Me.Controls
        If IsNull(ctl) Then
            MsgBox ctl.Name & " Is Null, please enter a Value.", vbInformation
        End If
    Next ctl

Now, again, this will trigger once for each Null control in sequence.

You can do fancy things like collect all the null control names and display them all at once in a message box, ...
Or highlight all the null fields in red, ...but that is a bit more complicated.

So mull over all of this, and get back to us.



LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35095453
To be clear,

What you are asking can be tricky to accomplish depending on your exact needs.
For this reason, (As I illustrated in my "Web Page" analogy), it is not commonly done.
Again, ....
Doing things like this will frustrate users, because you will "Stop them cold" if they can't (For any reason) fill in a certain field. (Missing the data, Data not available, Correct spelling not known, parent record not created, ...etc)
From working with database systems for over 10 years, and concurring with the other experts here, experience simply tell us that doing things like this are more trouble than they are worth.
This is because you will have to allow for other user actions (User closing the form unexpectedly, ...etc)

And finally, yet again, ...if you "Force" a user to complete a field before moving to the next field, user will simply "Type in Anything" just to bypass whatever system  you implement.
Like I said, Invalid data is sometimes worse than missing data

For the record,  you never addresses these concerns...

If your experience with database design tells you that this functionality is required then I am sure you can use the OnExit or LostFocus events of the control to do this.

This type of question is typical from new developers, as it seems like a "Great idea to *Make Sure* that users fill in all the fields"
It is a common question here and typically ends up being an "Endless" thread, with the eventual answer being full of potential pitfalls, but "Good enough for now..."

But in reality it is just not as practical to implement as you might think.

Finally, and yet again, ...If some of the largest Web Databases and Commercial applications in the world don't bother with functionality...



Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

809 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