How to make a field mandatory (1 Viewer)

hilian

Episodic User
Local time
Today, 13:09
Joined
May 17, 2012
Messages
130
I am updating a data entry application for a case reading study. In the past, we have found that case reviewers, who do the data entry, sometimes skip certain fields, and we’d like to make those fields mandatory. The easiest way would be to make the fields required in the table, but that gives an incomprehensible error message. I tried making the validation for the text box and combo boxes that need to be filled in ,“Is not null” and adding a message to fill the box in, but that doesn’t seem to work: you can still save a record with the missing data, and no message shows.

I also tried code, which seems to work better.
There is one text box, txtSampleNo, and four combo boxes, cboECS, cboIndicated, cboIRT, and cboHighPriority,. I used this code:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtSampleNo) Then
  MsgBox "Please enter a sample number "
  Me.txtSampleNo.SetFocus
  Cancel = True
End If
If IsNull(Me.cboECS) Then
  MsgBox "Did this case originate with ECS? "
  Me.cboECS.SetFocus
  Cancel = True
End If
If IsNull(Me.cboIndicated) Then
  MsgBox " Is this case indicated? "
  Me.cboIndicated.SetFocus
  Cancel = True
End If
If IsNull(Me.cboIRT) Then
  MsgBox " Is this an IRT case? "
  Me.cboIRT.SetFocus
  Cancel = True
End If
If IsNull(Me.cboHighPriority) Then
   MsgBox " Is this case High Priority? "
   Me.cboHighPriority.SetFocus
   Cancel = True
 End If
End Sub

The problem is that only the first "If" seems to work. If the txtSampleNo is empty, you get the message and can’t save the record until you've entered the data, but you can save the record and don't get a message if any of the four combo boxes are empty.

What have I done wrong?

Many thanks,

Henry
 
Last edited by a moderator:

Ranman256

Well-known member
Local time
Today, 16:09
Joined
Apr 9, 2015
Messages
4,339
if do a check before user clicks the 'Save' or 'Run' button so they can leave.

If IsValidForm then run" this"

Code:
Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(dtpWeekOf)
      vMsg = "Date field missing"
   Case IsNull(cboUser)
      vMsg = "Teacher name is missing"
   Case IsNull(cboSubj)
      vMsg = "Subject field is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:09
Joined
Jul 9, 2003
Messages
16,245
I think you might find Colins example on how to enable/disable (and the like) various groups of controls handy.... I don't know if he actually shows an example of control validation, but I reckon it's a good place to start.

http://www.mendipdatasystems.co.uk/set-controls/4594398114

Sent from my Pixel 3a using Tapatalk
 

hilian

Episodic User
Local time
Today, 13:09
Joined
May 17, 2012
Messages
130
Thanks Ranman256,

Are you saying that I should substitute this code for the code I tried. I can see that it will give a message, but it won't stop the user from saving the record, will it?

Also, which event is the code attached to? It isn't before update. Perhaps I don't understand (private) functions.
 

hilian

Episodic User
Local time
Today, 13:09
Joined
May 17, 2012
Messages
130
Hi Uncle Gizmo,

I'm at work, and our IT department has created all sorts of complications for downloading Access files. I'll this from home.

Thanks,

Henry
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:09
Joined
Jul 9, 2003
Messages
16,245
BTW, I forgot to mention, Colin is a member here by the name of isladog,,..

Sent from my Pixel 3a using Tapatalk
 

Micron

AWF VIP
Local time
Today, 16:09
Joined
Oct 20, 2018
Messages
3,476
It's a very commonly asked question. Colin and I use pretty much the same approach I believe, possibly excepting that I prefer to use the label caption in the message because "Sample Number" or whatever it is, is often much more intuitive than txtSampleNo. I also create a message string and add the captions for the missing values at once. That way, you don't prompt 5 times for each one of 5 missing values - unless you have a very obstinate user! If you need a procedure, I don't mind writing it - just specify if all controls involved have their labels associated/attached. You would have to select all combos/textboxes in design view (can do all at once) and on the Property sheet, enter a value for the Tag property such as "reqd" (no quotes).
 

hilian

Episodic User
Local time
Today, 13:09
Joined
May 17, 2012
Messages
130
Thanks Micron,

Yes, it seems as though this would come up a lot.

I'll definitely take you up on your offer to write the procedure, although, and I know this is an extra step, if you could explain the logic, I could learn for next time.

These are the controls and the messages that go with them. the messages use the label captions for each of the text/combo boxes.

Code:
Me.txtSampleNo	" Please enter a sample number "
cboECS	                " Did this case originate with ECS?
cboIndicated	        " Is this case indicated? "
cboIRT	                " Is this an IRT case? "
cboHighPriority	        " Is this case High Priority? "

Many thanks,

Henry
 
Last edited by a moderator:

Micron

AWF VIP
Local time
Today, 16:09
Joined
Oct 20, 2018
Messages
3,476
So you can't use the label caption because it says (e.g.) "Priority" and you want a more specific message such as "Is this case high priority? What I had in mind would say
"Please enter values for:
- Priority
- Sample No:
- IRT"
assuming those 3 were missing values. What you seem to be requesting is doable but more intricate so I want to get the facts straight. If not as straight forward as I imagined, something would have to be tweaked - probably the Tag property. Let me know.
 
Last edited:

hilian

Episodic User
Local time
Today, 13:09
Joined
May 17, 2012
Messages
130
Yes. I wanted more than the label caption because I want to be as clear as possible for the users.

In the code I first posted, the intention was, if there was a null value in any of the required text/combo boxes you would get a message and you wouldn't be able to save. I also wanted to set the focus to the control in question so that filling in the missing value would be as easy as possible. At one point, I also tried to change the background color of the text/combo box so that it would be more obvious. That code didn't work, so I decided it wasn't as important as the message, and let it go. Over all, however, the code didn't work as intended. If there was more than one missing required value, it only showed a message for the first one and then allowed the user to save the record with the other value(s) missing.

I see what you're saying about using the Tag property instead of the caption. If it's done that way, and if there is more than one null field, would that result in all of the messages appearing at the same time? Is that good practice?

Again, thanks,

Henry
 

Micron

AWF VIP
Local time
Today, 16:09
Joined
Oct 20, 2018
Messages
3,476
I see what you're saying about using the Tag property instead of the caption. If it's done that way, and if there is more than one null field, would that result in all of the messages appearing at the same time? Is that good practice?
I certainly think so. As a newcomer to the process (novice form user) would you like to get 3 notices as you plonk through this, or one?

I have code ready for "all at once". The backcolor isn't modified but that is easily added.
If you want one at a time, it's a re-write and won't resemble my or Isladog's way - at least not as I recall. Let me know.
 
Last edited:

Micron

AWF VIP
Local time
Today, 16:09
Joined
Oct 20, 2018
Messages
3,476
This works where I have both textboxes and combos where their Tag property is (e.g.)
reqd,message 4 and more text
Note that it is reqd and then a comma and then text with no spaces until the message. You cannot put additional commas in the text string. Here is code with lotsa notes:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim sArray() As String, strList As String

For Each ctl In Me.Controls [COLOR=DarkGreen]'start a loop over form controls[/COLOR]
[COLOR=darkgreen]'Debug.Print "Type: " & ctl.ControlType & "  Name: " & ctl.Name & "  Tag: " & ctl.Tag[/COLOR]

[COLOR=darkgreen]'IF type is textbox OR combo {pair OR comparison to ensure it's evaluated separate from the AND}[/COLOR]
  If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Tag <> "" Then
[COLOR=seagreen]    [COLOR=darkgreen]'Since Tag exists (<> ""), split the 2 value Tag parts based on its comma[/COLOR]
[COLOR=Black]     sArray = Split(ctl.Tag, ",")[/COLOR][/COLOR]
[COLOR=seagreen]     [COLOR=darkgreen]'If 1st Tag part is "reqd" (no quotes) AND the control has no value **NOTE TO FOLLOW[/COLOR][/COLOR]
      If sArray(0) = "reqd" And Nz(ctl, "") = "" Then
 [COLOR=SeaGreen]      [COLOR=darkgreen] 'make strList = what it already is (1st time = "")[/COLOR][/COLOR]
[COLOR=SeaGreen][COLOR=darkgreen]        'then concatenate dash, message part after comma & carriage return/line feed[/COLOR][/COLOR]
        strList = strList & " - " & sArray(1) & vbCrLf
        [COLOR=darkgreen]'set backcolor to yellow[/COLOR]
         ctl.BackColor = vbYellow
       End If
   End If
Next

If strList <> "" Then [COLOR=darkgreen]'if "" no controls satisfied the checks[/COLOR]
   MsgBox "Required values are missing:" & vbCrLf & strList
   Cancel = True
End If

End Sub
** re: Nz(ctl, "") = ""
if ctl holds "" (zero length string) then it is not null, thus expression evaluates to ""=""
if ctl is null, Nz converts to "" thus same evaluation result. Either way, result of "" means the control has no value
if ctl is not null, then there is something in it, so bypass it

From your code, it's not obvious why Cancel = True seemed to be ignored. My guess is that the combo was not Null, which you can always check. It may have been a zls ("") or you could see no value due to incorrect visibility on the columns. That's why I always check for null and zls.

In the end, this turned out to be not what I originally referred to because of the way you wanted to provide specialized messages rather than just announce the label caption of the controls which are missing data. Hope it works for you. If not, double check your Tag property value and make sure to flag what line has an error, if any - speaking of which I have no error handler in the code. I've run out of time for tonight.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:09
Joined
Sep 21, 2011
Messages
14,047
In the code I first posted, the intention was, if there was a null value in any of the required text/combo boxes you would get a message and you wouldn't be able to save.

What should happen if I just choose to put a space in them?
 

isladogs

MVP / VIP
Local time
Today, 20:09
Joined
Jan 14, 2017
Messages
18,186
Hi
I've not got involved with this thread until now as you are receiving such expert assistance from Micron.
However as my approach has been referenced a couple of times, this is just to confirm that I use code very similar to that provided by Mcron.

If you do look at the link on my website, let me know if you have any questions about its use.
Good luck with your project
 

hilian

Episodic User
Local time
Today, 13:09
Joined
May 17, 2012
Messages
130
Thank you both, Micron and Colin,


I'm at home now, and I don't have access to the db. I will try the code on Monday and also check out the link.



I'll report back Monday or Tuesday.


Henry
 

Micron

AWF VIP
Local time
Today, 16:09
Joined
Oct 20, 2018
Messages
3,476
What should happen if I just choose to put a space in them?
AFAIK, if the field cannot accept zls then it will be null. Then again, I know if you try to add only a space to a table field, Access will remove it, hence still null.

Possibly something like this needs to be added to undo the yellow backcolor after inputs are fixed:
Code:
If sArray(0) = "reqd" And Nz(ctl, "") = "" Then
            strList = strList & " - " & sArray(1) & vbCrLf
            ctl.BackColor = vbYellow
        [COLOR=Red]Else
            ctl.BackColor = vbWhite '<< or whatever color value is required[/COLOR]
        End If
 
Last edited:

missinglinq

AWF VIP
Local time
Today, 16:09
Joined
Jun 20, 2003
Messages
6,423
This kind of code is fine when only validating a single Control.

But when validating multiple Controls, as you're doing...to avoid the problem you're seeing you have to do each validating, adding an Exit Sub command...i.e.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtSampleNo) Then
  MsgBox "Please enter a sample number "
  Me.txtSampleNo.SetFocus
  Cancel = True
  [COLOR="Red"]Exit Sub[/COLOR]
End If

If IsNull(Me.cboECS) Then
  MsgBox "Did this case originate with ECS? "
  Me.cboECS.SetFocus
  Cancel = True
  [COLOR="Red"]Exit Sub[/COLOR]
End If
.....
......
.......

etc
 
End Sub
Linq ;0)>
 

hilian

Episodic User
Local time
Today, 13:09
Joined
May 17, 2012
Messages
130
What should happen if I just choose to put a space in them?

The simplest way to avoid that is to use the field's validation property. As long as there is a validation rule that doesn't include a space as a valid entry, the field won't accept a space.
 

isladogs

MVP / VIP
Local time
Today, 20:09
Joined
Jan 14, 2017
Messages
18,186
The simplest way to avoid that is to use the field's validation property. As long as there is a validation rule that doesn't include a space as a valid entry, the field won't accept a space.

True but unnecessary. See Micron's answer in post #17.
 

Users who are viewing this thread

Top Bottom