Solved

Best way to handle required fields on forms

Posted on 2006-06-29
13
319 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 125 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

707 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

18 Experts available now in Live!

Get 1:1 Help Now