Solved Some Code executing, some not executing on a form event (1 Viewer)

[Tom d]

I have this code on a TextBox on a form in the 'On Exit' event.
The If (Systolic) > 999 Then, executes and the message is displayed.
The focus is not set to the Systolic text Box, it is set to the next text box on the form.
I tried 3 versions of the SetFocus.
Can any one please tell me what the problem is?



Private Sub Systolic_Exit(Cancel As Integer)

If IsNull(Systolic) Then
MsgBox "Enter a Systolic"
Forms![frm Blood Pressures]!Systolic.SetFocus


End If

If (Systolic) > 999 Then
MsgBox "Systolic Greater Than 999"
Forms![frm Blood Pressures]!Systolic.SetFocus
Me!Systolic.SetFocus
Systolic.SetFocus
End If

If (Systolic) < 1 Then
MsgBox "Systolic Less Then 1"
Forms![frm Blood Pressures]!Systolic.SetFocus
End If

End Sub

 

[Pat Hartman]

For starters, you don't validate data in any event except the control's BeforeUpdate event and the FORM's BeforeUpdate event and the form's event is generally a better place since that allows you to do each edit once and only once. If you do any edits in the control's BeforeUpdate event, you will need at a minimum to check for presence for any required field. So rather than validate a field in two events, I use the Form's event except as mentioned later. The only time the BeforeUpdate event is better is if you need to give an immediate warning to the user because you don't want him to enter any more data until the field in error has been fixed. An example is SSN . Since SSN must be unique and you don't want to save an employee record without one, you check to see if it already exists and if it does, you give the user an error at that point. You must ALSO verify SSN in the BeforeUpdate event because if the user never tried to enter data into the SSN control, no control event would fire and in addition to being unique, SSN must also be present and there is no way to ensure a value is present if you use the control events.

I modified your code so that it works correctly in the form's BeforeUpdate event. When there is an error, just raising an error is insufficient. You must also cancel the BeforeUpdate event to prevent Access from saving the record.

I make each individual edit separate. As soon as an error is encountered, the event is cancelled and the code exits so the user can fix the flagged error. This is simpler than trying to find all the errors at once. In the real world, your users will probably not make a lot of input errors so you don't need to drive yourself crazy and make your code more complicated than it needs to be by trying to be "efficient". Just report the errors one at a time. the user is very unlikely to have more than one anyway.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Systolic & "" = "" Then
    MsgBox "Enter a Systolic"
    Me.Systolic.SetFocus
    Cancel = True
    Exit Sub
End If

If Me.Systolic > 999 Then
    MsgBox "Systolic Greater Than 999"
    Me.Systolic.SetFocus
    Cancel = True
    Exit Sub
End If

If Me.Systolic < 1 Then
    MsgBox "Systolic Less Then 1"
    Me.Systolic.SetFocus
    Cancel = True
    Exit Sub
End If

End Sub

PS, I think you can't set focus to the current event in On Exit because focus has not actually left the event at that point. You would keep the focus in the event by using the Cancel = True method. But since on Exit is the wrong event to use anyway, use the code I posted.

 

[Tom d]

For starters, you don't validate data in any event except the control's BeforeUpdate event and the FORM's BeforeUpdate event and the form's event is generally a better place since that allows you to do each edit once and only once. If you do any edits in the control's BeforeUpdate event, you will need at a minimum to check for presence for any required field. So rather than validate a field in two events, I use the Form's event except as mentioned later. The only time the BeforeUpdate event is better is if you need to give an immediate warning to the user because you don't want him to enter any more data until the field in error has been fixed. An example is SSN . Since SSN must be unique and you don't want to save an employee record without one, you check to see if it already exists and if it does, you give the user an error at that point. You must ALSO verify SSN in the BeforeUpdate event because if the user never tried to enter data into the SSN control, no control event would fire and in addition to being unique, SSN must also be present and there is no way to ensure a value is present if you use the control events.

I modified your code so that it works correctly in the form's BeforeUpdate event. When there is an error, just raising an error is insufficient. You must also cancel the BeforeUpdate event to prevent Access from saving the record.

I make each individual edit separate. As soon as an error is encountered, the event is cancelled and the code exits so the user can fix the flagged error. This is simpler than trying to find all the errors at once. In the real world, your users will probably not make a lot of input errors so you don't need to drive yourself crazy and make your code more complicated than it needs to be by trying to be "efficient". Just report the errors one at a time. the user is very unlikely to have more than one anyway.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Systolic & "" = "" Then
    MsgBox "Enter a Systolic"
    Me.Systolic.SetFocus
    Cancel = True
    Exit Sub
End If

If Me.Systolic > 999 Then
    MsgBox "Systolic Greater Than 999"
    Me.Systolic.SetFocus
    Cancel = True
    Exit Sub
End If

If Me.Systolic < 1 Then
    MsgBox "Systolic Less Then 1"
    Me.Systolic.SetFocus
    Cancel = True
    Exit Sub
End If

End Sub

PS, I think you can't set focus to the current event in On Exit because focus has not actually left the event at that point. You would keep the focus in the event by using the Cancel = True method. But since on Exit is the wrong event to use anyway, use the code I posted.

I enter 9999 in systolic and the greater than 999 error message happened. Next a message box came up "You can't save the record at this Time' with 2 buttons 1 Yes 2 NO. If yes is pressed, you exit the form. If no is pressed, you get a message box saying error 3021 action name = CloseWindow and a stop macro button, if you press the stop macro button you go to the original form with the 9999 highlighted.
This would be to confusing to the user.
How about use validation rule for the fields to check if them are < 1 or greater then 999 and is not null.
WI will use Validation Rules.
Thank You for all your help.

 

[Pat Hartman]

Feel free to ignore me about which event to use if you think you know more than I do.

There is NOTHING in the code you posted that is stopping bad data from being saved. You may get an error message but when you close the form or scroll to a new record or click into/out of a subform if there is one, the bad data gets saved.

The Form's BeforeUpdate event is the LAST event to run before a record gets saved and it cannot be bypassed regardless of what prompted the save. Think of it as the flapper at the end of the funnel. If the flapper is closed, NOTHING gets saved. If the flapper is open, the record gets saved.

 

[Tom d]

Feel free to ignore me about which event to use if you think you know more than I do.

There is NOTHING in the code you posted that is stopping bad data from being saved. You may get an error message but when you close the form or scroll to a new record or click into/out of a subform if there is one, the bad data gets saved.

The Form's BeforeUpdate event is the LAST event to run before a record gets saved and it cannot be bypassed regardless of what prompted the save. Think of it as the flapper at the end of the funnel. If the flapper is closed, NOTHING gets saved. If the flapper is open, the record gets saved.

I do not know more then you, but I may be stupid but your code does not catch a blank field.

 

[Pat Hartman]

I'm not sure why you think stopping the update with a field highlighted and an error message is confusing. The user fixes the error and saves again. Assuming there are no other errors, the record saves.

Form and control events are not arbitrary. The MS Access development team chose each event specifically to give you a place to put certain types of code. The BeforeUpdate events were the events intended to be used for validation on add and update. The On Delete Confirm event, you get to cancel a delete command, on the Current event, you get to show/hide fields if you have some condition where if the value of fieldA is 1, you want to show fldx and fldy but if it is 2, you only want to show fldx. etc. Every event has a purpose and although you can get away with misusing events in some cases, If you don't do the validation where it belongs, you end up having to have code in multiple events and there are still situations where you may not have totally trapped the errors.

If you understand and use correctly the Form's BeforeUpdate event, you are way ahead of the game.

 

[Tom d]

I'm not sure why you think stopping the update with a field highlighted and an error message is confusing. The user fixes the error and saves again. Assuming there are no other errors, the record saves.

Form and control events are not arbitrary. The MS Access development team chose each event specifically to give you a place to put certain types of code. The BeforeUpdate events were the events intended to be used for validation on add and update. The On Delete Confirm event, you get to cancel a delete command, on the Current event, you get to show/hide fields if you have some condition where if the value of fieldA is 1, you want to show fldx and fldy but if it is 2, you only want to show fldx. etc. Every event has a purpose and although you can get away with misusing events in some cases, If you don't do the validation where it belongs, you end up having to have code in multiple events and there are still situations where you may not have totally trapped the errors.

If you understand and use correctly the Form's BeforeUpdate event, you are way ahead of the game.

I do not know more then you, but I may be stupid but your code does not catch a blank field.

 

[Gasman]

I do not know more then you, but I may be stupid but your code does not catch a blank field.

I would have thought the first block of code would catch that?
Have you walked through the code?

 

[Pat Hartman]

If Me.Systolic & "" = ""

Finds a null field or a ZLS field. If the field is filled with space characters it is not empty if that is what you are talking about.

 

[Tom d]

I would have thought the first block of code would catch that?
Have you walked through the code?

I know that the code is being looked at because the other code works.
I am new to access and vba so I do not know how to do things.

 

[Pat Hartman]

It isn't clear what isn't working about the code I posted. Please post your version, preferably in a database.

 

[Gasman]

Set a breakpoint in the first line of executing code in that event. Click in the column left of the code. Then you can inspect values by hovering the mouse over them, BUT when you stop on a line, that line has NOT yet executed. Then press F8 to move to next line. F5 will carry on running.
Click on the red dot to remove the breakpoint or use the menu option.

 

[isladogs]

As already stated, the BeforeUpdate event should do exactly what you want.
Assuming your Systolic field is a number it must either have a value or be null.
It cannot be an empty string or a space unless its a text field
The event code will prevent the null value being accepted as well as limit the range of values allowed.

The only way I can manage to bypass the event code is to empty a Systolic value from a record in the table or by using an update query thereby setting it to null.
it is true that the form will not pick up that null value because there is nothing to update.
However, data entry / editing should only ever be done using forms where you can control the outcome using event code.
Users should NEVER be allowed to access tables as you can't then control their actions

So are you doing what I've just described or something similar?
If not, please can you describe exactly what you are doing that allows a blank record to be accepted by the form.

 

[Tom d]

Here is my Data Base Attached

 

[Gasman]

You have an input mask on the date control? I hate input masks.
Anyway I entered todays date with ctrl & ; and get 'Datcannot be in the future????

I changed it to 23/07/2022 as you can see, then tried to move to next record. Pic below

Why is form selections using the table? Could not open table in design view until I close a form that should have nothing to do with tables?, it is a switchboard form type after all?

Edit: I found out why, you have < Date() in the validation rule on the control properties. ? Should be <= Date() ???
FWIW I would have your validation in one place. You have bits in the table, bits in the control properties and bits in code?
Absolutely a nightmare to track down, especially when you do not know the DB.

The experts here will advise the best method.

 

[Tom d]

You have an input mask on the date control? I hate input masks.
Anyway I entered todays date with ctrl & ; and get 'Datcannot be in the future????

I changed it to 23/07/2022 as you can see, then tried to move to next record. Pic below

Why is form selections using the table? Could not open table in design view until I close a form that should have nothing to do with tables?, it is a switchboard form type after all?

View attachment 101984

I will try and make a switchboard.

 

[Gasman]

I do not know if it is my laptop or your DB, but it runs as slow as hell on my laptop? When I start it, it shows 'Updating tables in the status bar?, and I have 2007 ?

No need to create a switchboard, that just adds complexity, which you do not need right now.
Your current form acts as one adequately.

Here is your DB back.
I changed the date validation in that property to allow today's date.
I removed the mask and use the date picker.
I removed Data Entry Yes. I just use the * on the navigation bar at the bottom to add new record. That then allows me to see the other record. I use the other buttons to move forward back start or end etc.

Oh, and I removed table validation for systolic.

 

[Tom d]

I do not know if it is my laptop or your DB, but it runs as slow as hell on my laptop? When I start it, it shows 'Updating tables in the status bar?, and I have 2007 ?

No need to create a switchboard, that just adds complexity, which you do not need right now.
Your current form acts as one adequately.

Here is your DB back.
I changed the date validation in that property to allow today's date.
I removed the mask and use the date picker.
I removed Data Entry Yes. I just use the * on the navigation bar at the bottom to add new record. That then allows me to see the other record. I use the other buttons to move forward back start or end etc.

Oh, and I removed table validation for systolic.

when opening frm Blood Pressures it was data already filled in should not have any data in the fields.
Data Base opens very fast on my computer.
I am running office 365.

 

[Gasman]

Yes, could be my laptop, yet all mine open fine?
If you have data entry yes, you can ONLY enter new data.
If you want to add a new record when it loads, I would use goto newrecord, but still prefer the *
Just matter of preference.

 

[isladogs]

@Tom d
Did you read my comments in post #13?