How to make a field mandatory

[GinaWhipp]

@Pat Hartman... if that reply was in reference to the code I provided it does not go in the Before_Update event, it would go on the Save button. There are only two ways out of the From I use Save or Cancel, no other way to close the Form.

If not referencing my code then *nevermind*.

 

[GinaWhipp]

@Pat,

While I would agree with you 99.9% of the time in the case that caused me to have to do something different I used this snippet of code. I am not disagreeing with you only posted the other way to *skin the cat*.

Let me also say in all the time I have doing this I only had to use that snippet 3 times. That should give you an idea of how often it comes up.

 

[NauticalGent]

...using the BeforeUpdate event to control the validation is akin to euthanizing the cat before you try to skin it rather than having it be a moving target while you skin it alive.

Now THAT was brillant!

@Gina, for the record, I plagiarized your code about a year ago, made a few mods to fit my needs and life has been good. Thanks for making it available.

 

[GinaWhipp]

@Pat,

That statement was brilliant!

I say we are going to agree to disagree, since the few times I used it it did exactly what I wanted and was the best fit for the situation.

@NauticalGent

Glad I could help. I actually updated the code last time I used, with another Function I found online, it to tell you the name of the Control with the prefix, i.e. txt, cbo, etc. One day, I'll get a chance to update that.

 

[GinaWhipp]

Also love cats, had one till the doctor informed me that it was not dust I was allergic to but the cat! Still kept her till it was time for her to go cat heaven but never got another one.

Well, databases were created around 2004 thru 2007 and been upgraded and so far so good. So, I guess my luck is holding out.

 

[Deleted member 147267]

The original request seems to be OK with the attached. I didn't really follow the update debate which might be relected in my next comments.

This case requires 2 ways of updating as there are both navigation controls and a save button. Best place for the code IMHO is the form BeforeUpdate event. However, there is also a need to execute it if save button is clicked, thus that event sets Dirty property to False if it is True. That will call the update event, which will be canceled, which will cause the click event to error on cancellation, which is trapped and suppressed.

Notes to Hilian:
had to undo some startup settings in order to make it easier to navigate the design.
Probably messed up the layout in order to fit it to my screen.
Really only concerned myself with the face sheet tab
you have code for controls that no longer exist
Your code won't compile without a lot of commenting out (possibly for the above reason)
usually if one adds their own navigation, the built in controls are removed. Perhaps that is planned. Custom controls require much more code (e.g. so as to prevent clicking 'next record' when there is no next), otherwise causes errors.
you have no user interface for canceling a record, thus if user starts record and needs to back out, your nav buttons will generate error messages and possibly continue to run validation code. Unless they know that they can cancel a record via Esc key, they will find this to be a nuisance.


View attachment QPR3_Master June 2019.accdb

 

[hilian]

Micron, Thanks very much for all your help.

I know I have code for controls that I've removed. I intended to clean up the superfluous code as a final step before making the application available to users.

I wanted to have the navigation buttons because I was afraid that users wouldn't see the built-in navigation. We've been using an earlier version of the current application for a few years, and so far, we haven't had any major problems. If I haven't added something this time that will cause new problems, I'd just as soon leave it as is, although I could be persuaded otherwise.

Partly completed records are an issue from time to time, although less so lately. Before users got used to the app, they occurred fairly frequently. I'd like to give users the ability to cancel a record, but isn't that a new thread? Clicking next record when there is no next record starts a new record—I just tested this.

The code you supplied seems to do exactly what I need it to do, but there’s one thing I’d like to add. Since there are multiple tabs on the form, a user could try to leave the record (save or go to another record) from a different tab from the unfilled mandatory control. There is a message that tells them which control(s) need entries, but isn’t it also possible to set the focus to the first unfilled control, then the next, and so forth until all the mandatory controls are filled in?

 

[Deleted member 147267]

There is a message that tells them which control(s) need entries, but isn’t it also possible to set the focus to the first unfilled control, then the next, and so forth until all the mandatory controls are filled in?

If you mean present a message, cancel the update and set focus to control A, then yes. If you mean do that, then set focus to control B after A is filled then I think not.

What is suggested in post 54 was covered way, way back in this thread. I don't see what's so complicated with the approach taken, which is quite common except for the curve ball of having a specific message for each one instead of a generic message. Besides, it teaches more advanced techniques, which is never a bad thing IMHO. To each their own.

 

[hilian]

Pat Hartman’s code (thank you Pat) looks a great deal like code I already tried:

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

What happened was that the code only worked for the first If, but once that control was no longer null, the code for the rest of the controls didn’t fire: for example, if the sample number was missing, the record wouldn’t save and the message would appear, but once the sample number was entered, the record did save, even with other required data missing.

The code Micron supplied works, but I’d like to set the focus to the first empty required field, then, once it’s filled in, to set the focus to the next empty required field, etc. I’m guessing that the set focus fits somewhere in this code, but, if so, where? Does it come after “ctl.BackColor = vbYellow,” and if so, what’s the syntax? I can imagine the syntax for an individual control, but this code works in a more general away.

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

For Each ctl In Me.Controls 'start a loop over form controls
'Debug.Print "Type: " & ctl.ControlType & " Name: " & ctl.Name & " Tag: " & ctl.Tag

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

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

End Sub

 

[Deleted member 147267]

If you want focused answers from me, then you have to answer my questions and/or musings:

If you mean present a message, cancel the update and set focus to control A, then yes. If you mean do that, then set focus to control B after A is filled then I think not.

The following should set focus to the first control that is found to be blank - first being whichever control Access comes to in the collection that satisfies the tests you're doing. Again, if that's not the requirement this mod is of little use I guess, otherwise add the red parts in the locations shown. Don't remove anything just because I didn't repeat the whole procedure
Please use code tags with indentation when you post more than a few lines of code...

Dim sArray() As String, strList As String[COLOR=Red], strCtlName As String[/COLOR]

If sArray(0) = "reqd" And Nz(ctl, "") = "" Then
  'make strList = what it already is (1st time = "")
  'then concatenate dash, message part after comma & carriage return/line feed
  strList = strList & " - " & sArray(1) & vbCrLf
  'set backcolor to yellow
  ctl.BackColor = vbYellow
 [COLOR=red] If strCtlName = "" Then strCtlName = ctl.Name[/COLOR]
End If
End If
Next

If strList <> "" Then 'if "" no controls satisfied the checks
MsgBox "Required values are missing:" & vbCrLf & strList
Cancel = True
[COLOR=red]Me.Controls(strCtlName).SetFocus[/COLOR]
 End If

I did not test those changes.


EDIT - wait until I test it. May not work as is.
- changed <> to =

 

[Deleted member 147267]

Not possible if you did it correctly. I would need to see that code.

I agree and I think it was posted long ago. I didn't pursue that path because the decision was made to message about all missing fields at once rather than nag one at a time, again, long ago. Thus it's kind of irrelevant unless hilian wants to know where that went wrong, in which case you should be able to find it in the very beginning of this thread.

 

[hilian]

Micron,

First I didn't know about code tags, and I was wondering why I couldn't get the indentations to stay. Now I do. Thanks.

Then, I pasted in the pieces of code in red and tested it. It works just the way I need it to. I couldn't be more pleased.

Finally, thank you to everyone who contributed to this thread. I've learned a great deal, and I'm very grateful.

Henry

 

[Deleted member 147267]

I've learned a great deal

Some of it about cats.
Good luck going forward.

 

[hilian]

I've had many years of cats. They taught me everything I know.