Solved

Best way to handle required fields on forms

Posted on 2006-06-29
13
328 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

947 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

22 Experts available now in Live!

Get 1:1 Help Now