Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Best way to handle required fields on forms

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
SteveL13
Asked:
SteveL13
  • 4
  • 4
  • 3
  • +2
1 Solution
 
Raynard7Commented:
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
 
jerryb30Commented:
Another way is to not have the other textboxes enabled until the prior textboxes have (legal) values.
0
 
Raynard7Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
BillPowellCommented:
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
 
SteveL13Author Commented:
Need a little clarification... where do I enter "Required" (without quotes) at in the properties of each field?

--Steve
0
 
BillPowellCommented:
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
 
SteveL13Author Commented:
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
 
Raynard7Commented:
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
 
SteveL13Author Commented:
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
 
Raynard7Commented:
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
 
SteveL13Author Commented:
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
 
BillPowellCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now