?
Solved

Best way to handle required fields on forms

Posted on 2006-06-29
13
Medium Priority
?
365 Views
Last Modified: 2006-11-18
I have a database I am creating which so far contains three input forms.  Each of the forms has 3-4 fields that I want the user to have to enter data into before they can move on to the next field.  I am looking for advise on the best way to handle this.  The way I would like it to work is if the user leaves one of the required fields blank, a message box should pop up letting them know that it cannot be blank.  Then on to the next field and so forth.  Any advise much appreciated.

--Steve
0
Comment
Question by:SteveL13
[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
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17015052
What you need to do is to have an "on focus" event on each of the text fields.  You should then check to see if the first, then second, then third box are empty - if one is then msgBox "First Text Box is Empty" please complete
and then setFocus on the first field. This will ensure that the fields will be complted in order and without any blanks
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17015102
Another way is to not have the other textboxes enabled until the prior textboxes have (legal) values.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17015109
I agree - these could be enabled on update on the text fields - with a check that there is data - and disable if there is no data
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 11

Expert Comment

by:BillPowell
ID: 17015436
Looking at this from a user point of view, this might get a bit annoying.  Unless it is necessary for the fields to be filled out in a specific order, I would recommend waiting until the user attempts to save, or leave the record before notifying him of his mistakes.  This way he finds out about it all at once, in a single message.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl as Control
Dim msg as String
For Each Control In Me.Controls
  If ctl.Tag = "Required" and ctl.Value Is Null Then
    msg = msg & ctl.Name & vbcrlf
  End If
Next ctl
If msg <> "" Then 'Some required fields left blank
  msg = "You must enter a value in these fields:" & vbcrlf & msg
  MsgBox msg
  Cancel = True
End If
End Sub

For this procedure to work properly you need to go to the properties of each required text box and put in the string  "Required" (no quotes needed)

Just an idea
0
 

Author Comment

by:SteveL13
ID: 17038019
Need a little clarification... where do I enter "Required" (without quotes) at in the properties of each field?

--Steve
0
 
LVL 11

Expert Comment

by:BillPowell
ID: 17038118
There is a property called Tag in the properties sheet for each control.  This property has no specific meaning.  Basically its a spare property that means nothing unless you place something in it and then use that property in code.  For your text boxes, hold down the shift key and select all applicable text boxes and you can place the text "Required" in all of those boxes at once.
0
 

Author Comment

by:SteveL13
ID: 17044575
Ok, so if I have two fields that I  want to be required... one is txtFirst and the other is txtLast... than exactly what is the syntax for the BeforeUpdate event of the form?  I'm just not getting it right.

--Steve
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17047793
Copy exactly what bill suggested - and go into txtFirst and go to the properties and go to the tag property and enter the text "Required" this should then work
0
 

Author Comment

by:SteveL13
ID: 17049497
No, what I meant was... in the copy below, where does txtFirst and txtLast go?...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl as Control
Dim msg as String
For Each Control In Me.Controls
  If ctl.Tag = "Required" and ctl.Value Is Null Then
    msg = msg & ctl.Name & vbcrlf
  End If
Next ctl
If msg <> "" Then 'Some required fields left blank
  msg = "You must enter a value in these fields:" & vbcrlf & msg
  MsgBox msg
  Cancel = True
End If
End Sub


--Steve
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17049561
The beauty of Bill's code is that you do not need to add them  so if you want to add more in the future you can change the tags;

the line

msg = msg & ctl.Name & vbcrlf  

will add the name of the control (the text box) with the tag value of Required and insert that into the message.

All you need to do is ensure the tag is completed.
0
 

Author Comment

by:SteveL13
ID: 17049954
I get an error when I try to save the record without filling out the required fields...

Compile Error:  Invalid Next control variable reference

What does that mean?

--Steve
0
 
LVL 11

Accepted Solution

by:
BillPowell earned 500 total points
ID: 17050608
Sorry, my mistake.  Heres the corrected code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim msg As String
For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then
    If ctl.Tag = "Required" And Nz(ctl.Value, "") = "" Then
      msg = msg & ctl.Name & vbCrLf
    End If
  End If
Next ctl
If msg <> "" Then 'Some required fields left blank
  msg = "You must enter a value in these fields:" & vbCrLf & msg
  MsgBox msg, vbInformation, "Missing Data"
  Cancel = True
End If
End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17067163
SteveL13,

Perhaps an easier alternative would be to do this at the "Table Level"
(No coding is required, and it conforms to BillPowell's post of waiting until the Insert/Update is made.)

Open your table in Design View
Select the Field you want to be "Required"
In the bottom half of the screen,
Select the "Required" Property, then set it to "Yes".

Done!

Now repeat for all the other fields you want to be "required"

One note though, the error message is not very "user friendly"

Just offering another alternative.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

752 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