How to make a field mandatory (1 Viewer)

[GinaWhipp]

Okay, so you have a Save button then for sure it has to go there. As to your question, no that save line only ensures the values that have been entered are saved before it checks to make sure they are entered.

Now, as for your other buttons. If they can go to the Previous or Next record without hitting Save then it has to go there also, same for the Exit button I would put it there as well. In my case I have a Form for entering new which only has a Save or Cancel button. The Save will check for required data and the Cancel will remove the record. I do not put an Exit button on the Form as it will close after *doing its job* on Save or Cancel.

 

[Pat Hartman]

The validation code does NOT go in the save button code, the validation code goes into the Form's BeforeUpdate event. The BeforeUpdate event is the LAST event that runs before data is saved and it CANNOT be bypassed so it is far and away the safest place to put validation. There are always situations where code in other events might not be executed. For example, if you just close the form, your save button code doesn't run but the BeforeUpdate event still runs.

 

[Gasman]

Gasman,

Thank you for the link.

I see where the code goes, but should the field be required in the table or the form?

I tested it on the table.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Debug.Print "DataErr = "; DataErr
If Screen.ActiveControl.Name = "Newdate" Then
    MsgBox "Newdate is required"
    Response = acDataErrContinue
End If
Debug.Print Err & " " & Err.Description
End Sub

HTH

 

[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*.

 

[Pat Hartman]

Gina, the correct place for validation code is the form's (or sometimes the control's) BeforeUpdate event. That is what the Access team built this event for. It is the flapper at the bottom of a funnel. It is the LAST event that runs before a record gets saved and it is never possible to bypass it. If a validation test fails, you cancel the event and that prevents the record from being saved. Very clean, very simple, a single place of control. If you don't use the correct event, you will ultimately need code in multiple other events to try to prevent Access from saving the record and you won't always succeed.

I once removed almost 10,000 lines of code from an application that was saving bad data. Why? Because the developer had put validation code in virtually every event of every control and every form event EXCEPT the BeforeUpdate events. And the app raised lots of errors and still saved all the bad records.

 

[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.

 

[Pat Hartman]

You can put the validation in a separate module if it is common (and I do that also) so it is available to all forms but the line of code that calls the validation belongs in the Form"s BeforeUpdate event.

If we have to use a "skin the cat" metaphor, 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.

 

[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.

 

[Pat Hartman]

I'm sure I'm going to get hate mail from the PC among us. To head that off - I would NEVER skin a cat. I love cats and I especially love cats with fur even though I am allergic

I guess we'll have to agree to disagree Gina. So far you've been lucky that no user has accidentally broken the process. May you always be so lucky.

The image of chasing the cat around brought back memories of a very old cartoon, probably from ComputerWorld in the 70's when we were all working on a mainframe. The picture is of a console in a computer room. One operator is standing and holding the phone speaking to the user and saying - "We'll have your report to you as soon as possible but we're having a little trouble capturing the data." And the second operator has a butterfly net and he is chasing a bunch of little, slightly bent punch cards with stick legs and arms.

 

[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.

 

[Micron]

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?

 

[Pat Hartman]

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?

You do this by checking fields one at a time and exiting once an error is discovered. This may sound inefficient but in reality, there is rarely more than one error found for any given form and so creating a complex process with arrays and looping is simply overkill.

If Me.SomeField & "" + "" Then
    Msgbox "SomeField is required.",vbOKOnly
    Me.SomeField.SetFocus
    Cancel = True
    Exit Sub
End If
If Me.Someotherfield  > Date() then
    Msgbox "Someotherfield must be <= Today's Date.",vbOKOnly
    Me.Someotherfield.SetFocus
    Cancel = True
    Exit Sub
End If
If ....

 

[Micron]

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

 

[Micron]

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 =

 

[Pat Hartman]

What happened was that the code only worked for the first If,

Not possible if you did it correctly. I would need to see the code that didn't work.

The code you posted is very hard to read for two reasons.
1. You didn't use the code tag to retain indentation
2. You left all the commented code in place.

I'm going to move on now. The loop you are using is fine if ALL controls have the same verification rules. If you have different rules for different controls, you have to do the error checks as I suggested - one at a time.

 

[Micron]

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.

 

[Pat Hartman]

Chaos reigns

There are too many contradicting suggestions. Good luck.