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

Posted on 2011-03-08
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 125 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 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now