vba on focus/lost focus help (1 Viewer)

[miken5678]

VBA as listed

Private Sub IndStatmentHandwrittenComment_BeforeUpdate(Cancel As Integer)
If Forms!WindOptOutV12.IndStatmentHandwrittenComment = "" Or Null Then
MsgBox "Please Enter a Comment Below for Deviations listed as OTHER"
Else
End If
End Sub

I have a field above this where if the selection = Yes then the comment box becomes visible. I have tried on focus/lost focus before/after but only had luck on the lost focus event when i populated it with a word and then deleted the word and tried to go to the next field. Basically deleting the default would trigger the original lost focus "" or null. When I remove a default value and click in the cell and go to another I cannot get the msgbox to pop up. Any help/suggestions on this are appreciated.


I have used me.dirty on a form however is there a way to set if fieldname.dirty = false then
msgbox?

or is this the wrong direction

 

[missinglinq]

Checking for Null or a ZeroLengthString can be done using the syntax

If Nz(FieldName, "") = "" Then

To only check for Null would be

If IsNull(FieldName) Then

But you cannot reliably use any event connected with the Control in question for this; all the user has to do is to never enter the textbox and your validation is bypassed!

This kind of validation has to be in the Form_BeforeUpdate event.

Linq ;0)>

 

[miken5678]

Private Sub Form_AfterUpdate()
If Forms!WindOptOutV12.Other = -1 And Nz(Forms!WindOptOutV12.IndStatmentHandwrittenComment, "") = "" Then
MsgBox "A comment is required when other deviations are noted."
IndStatmentHandwrittenComment.ForeColor = 100
Else
End If
End Sub

I went with this but am curious as this runs when i go to a next record can you point me to a sample or advise me on how you would stop from going to the next record and highlight the cell that needs to be filled? I found the forecolor item. Just wasnt sure how to handle the haulting of the current record.

I guess once I disable the bottom next record buttons I will need to copy this plus a bunch of others under a save record or next record button.

 

[miken5678]

nevermind on this.. going to just try to implement this in a save record button. the only thing that has me slightly worried is how to do this with a split form. If a user selects an item on the form what verifies that it is saved without a save button and if i put all the validation on the save button i assume i would have to be on the next record?

 

[boblarson]

nevermind on this.. going to just try to implement this in a save record button. the only thing that has me slightly worried is how to do this with a split form. If a user selects an item on the form what verifies that it is saved without a save button and if i put all the validation on the save button i assume i would have to be on the next record?

NO, no, no, no, no....

As stated before - you use the form's BEFORE UPDATE event to ensure that all is filled out before saving. When a save is initiated, either by a specific process like using

DoCmd.RunCommand acCmdSaveRecord

or simply by moving to another record, the BeforeUpdate event it fired (as long as there was something changed/added, etc.). So you can verify like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim blnError As Boolean
    Dim strError As String
 
    If Len(Me.YourControlNameHere & "") = 0 Then
        blnError = True
        strError = "YourControlNameHere" & vbCrLf
    End If
 
    If Len(Me.AnotherControlNameHere & "") = 0 Then
        blnError = True
        strError = strError & "YourOtherControlNameHere" & vbCrLf
    End If
 
    ' and so on for how ever many controls you want to validate that
    'something has been entered.  You can also validate other stuff about the values, etc.
 
    If blnError Then
        Cancel = True
        If MsgBox("You need to fill out these controls before we can save the record: " & vbCrLf & _
                  strError & vbCrLf & _
                  "Do you wish to cancel this record?", vbQuestion + vbYesNo, "Validation Failure") = vbYes Then
            Me.Undo
        End If
    End If
End Sub

Then if the validation fails, the question is asked of the user if they want to cancel the record. If they do, the code undoes the form. If they don't it returns them to the record to finish filling it out. And if all is well it doesn't do anything but save the record.

 

[miken5678]

Thanks, I never really fully understood the after/before update when i first started this with the if.visible statements I was using so they were all set to after/before/on form and they worked. I appreciate the catch as I have been on this for a while and am most likely not seeing straight.

question, as part of the memo/text box items should be filled in only under certain circumstances and not on the others what is the best route for this.

for example

Public Function OtherFunction()
If Forms!WindOptOutV12.ComboIndStmtHandWritten = "Yes" Then
Forms!WindOptOutV12.Other = 0
Forms!WindOptOutV12.IndStatmentHandwrittenComment.Visible = False
Else
End If
If Forms!WindOptOutV12.Other = -1 Then
Forms!WindOptOutV12.IndStatmentHandwrittenComment.Visible = True
Else
Forms!WindOptOutV12.IndStatmentHandwrittenComment.Visible = False
End If
End Function

I want to target the IndStatmentHandwrittenComment but only when ComboIndStmtHandWritten = -1 will it be required 0 would not. Not fully understanding your sample code is what I am suggesting possible? Am I on the right track below?



If ComboIndStmtHandWritten = -1 and Len(Me.IndStatmentHandwrittenComment & "") = 0 and Then
blnError = True
strError = "IndStatmentHandwrittenComment " & vbCrLf
End If

NO, no, no, no, no....

As stated before - you use the form's BEFORE UPDATE event to ensure that all is filled out before saving. When a save is initiated, either by a specific process like using

DoCmd.RunCommand acCmdSaveRecord

or simply by moving to another record, the BeforeUpdate event it fired (as long as there was something changed/added, etc.). So you can verify like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim blnError As Boolean
    Dim strError As String
 
    If Len(Me.YourControlNameHere & "") = 0 Then
        blnError = True
        strError = "YourControlNameHere" & vbCrLf
    End If
 
    If Len(Me.AnotherControlNameHere & "") = 0 Then
        blnError = True
        strError = strError & "YourOtherControlNameHere" & vbCrLf
    End If
 
    ' and so on for how ever many controls you want to validate that
    'something has been entered.  You can also validate other stuff about the values, etc.
 
    If blnError Then
        Cancel = True
        If MsgBox("You need to fill out these controls before we can save the record: " & vbCrLf & _
                  strError & vbCrLf & _
                  "Do you wish to cancel this record?", vbQuestion + vbYesNo, "Validation Failure") = vbYes Then
            Me.Undo
        End If
    End If
End Sub

Then if the validation fails, the question is asked of the user if they want to cancel the record. If they do, the code undoes the form. If they don't it returns them to the record to finish filling it out. And if all is well it doesn't do anything but save the record.

 

[boblarson]

Yeah, I think you have the right idea with this:

If ComboIndStmtHandWritten = -1 and Len(Me.IndStatmentHandwrittenComment & "") = 0 and Then
blnError = True
strError = "IndStatmentHandwrittenComment " & vbCrLf
End If

Although I would use something more user friendly for the strError part. You don't need the control name there but the description that the user would recognize. Sorry, I just had put that in to make it a bit easier to understand at the time.

 

[miken5678]

My goal for this is once a user clicks a button that says complete all records including the complete button would be locked at that point and only changed by someone going into the table and manually changing the record field to not complete.

I would have to setup your before update information with the fields below. But also something small on the on/after update to make sure editing the record and fields would be locked but you could still select a new item in the split form area.

If that is right within your sample code the area

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnError As Boolean
Dim strError As String

If blnError Then
Cancel = True

I assume blnerror is storing the count/trigger items so if there are none what is used to reference the opposite so that A msg box with a different prompt tagging it as complete storing it to a field and locking all records?

I have a feeling I would just set vbYes Then lock the form? or just go through and list every associate field. Just curious how to call on no blnerror. Let me know if this makes sense.

 

[boblarson]

No, you don't have to set something up on the After Update.

In the ON CURRENT event just put this:

If Not Me.NewRecord Then
   Me.AllowEdits = False
   Me.AllowDeletions = False
End If

That will lock it for you.

 

[boblarson]

I assume blnerror is storing the count/trigger items so if there are none what is used to reference the opposite so that A msg box with a different prompt tagging it as complete storing it to a field and locking all records?

No, the blnError is just a True/False flag which will be set to True if anything fails validation. If nothing fails then it isn't set to True and therefore if FALSE it just bypasses the code which cancels the update.

 

[miken5678]

In essence none of these are new records as they will have user information and a number attached to them. Extra information will just be attached to them. I wasnt sure how newrecord would work in this situation.

No, you don't have to set something up on the After Update.

In the ON CURRENT event just put this:

If Not Me.NewRecord Then
   Me.AllowEdits = False
   Me.AllowDeletions = False
End If

That will lock it for you.

 

[miken5678]

No, the blnError is just a True/False flag which will be set to True if anything fails validation. If nothing fails then it isn't set to True and therefore if FALSE it just bypasses the code which cancels the update.

so this

If blnError = False Then
If MsgBox("You need to fill out these controls before we can save the record: " & vbCrLf & _
strError & vbCrLf & _
"Do you wish to cancel this record?", vbQuestion + vbYesNo, "Validation Failure") = vbYes Then
Me.Undo
End If
If blnError = True Then
If MsgBox("Do You Wish to Save and Complete This Record" vbQuestion + vbYesNo, "Review Complete") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
Me.ReviewStatusfield = "Complete"
End IF


Just my general thinking but feel free to tell me if I am really going in the wrong direction

 

[boblarson]

Just my general thinking but feel free to tell me if I am really going in the wrong direction

Sorry, wrong direction. You messed up the programming I have you.

This part I did:

If blnError Then

is the same as

If blnError = True Then


and the rest ALL OF IT. Goes inside that. You do NOT need to test for FALSE.

Also, since the Before Update has been initiated, you do not use

DoCmd.RunCommand acCommandSaveRecord because the record is already in the process of being saved. We are just interrupting the process briefly to test whether or not to let it go through.

If blnError is TRUE that means that validation failed. So we go through the part about asking if they want to abandon the record and if they do we do one thing and if not then we do another, but I gave you the EXACT code to use. The only parts you should change are the validation TESTS. The rest - leave as is.

 

[miken5678]

The Reason I was testing for false was to implement another message box that would input "complete" into a field and essentially lock everything.

Sorry, wrong direction. You messed up the programming I have you.

This part I did:

If blnError Then

is the same as

If blnError = True Then


and the rest ALL OF IT. Goes inside that. You do NOT need to test for FALSE.

Also, since the Before Update has been initiated, you do not use

DoCmd.RunCommand acCommandSaveRecord because the record is already in the process of being saved. We are just interrupting the process briefly to test whether or not to let it go through.

If blnError is TRUE that means that validation failed. So we go through the part about asking if they want to abandon the record and if they do we do one thing and if not then we do another, but I gave you the EXACT code to use. The only parts you should change are the validation TESTS. The rest - leave as is.

 

[miken5678]

deleted this as I am not making sense.

 

[miken5678]

i set this up as a test but it does not catch the field on going to the next record.. the only thing i can think of is should it be altered to be nz(fieldname)? it is called in the

Private Sub Form_BeforeUpdate(Cancel As Integer)
Validation
End Sub

Public Function Validation()
    Dim blnError As Boolean
    Dim strError As String
 
    If Forms!WindOptOutV12.IndStatmentHandwrittenComment = -1 And Len(Forms!WindOptOutV12.IndStatmentHandwrittenComment & "") = 0 Then
        blnError = True
        strError = "YourControlNameHere" & vbCrLf
    End If
 
    'If Len(Me.AnotherControlNameHere & "") = 0 Then
    '    blnError = True
    '    strError = strError & "YourOtherControlNameHere" & vbCrLf
    'End If
 
    ' and so on for how ever many controls you want to validate that
    'something has been entered.  You can also validate other stuff about the values, etc.
 
    If blnError Then
        Cancel = True
        If MsgBox("You need to fill out these fields before we can save the record: " & vbCrLf & _
                  strError & vbCrLf & _
                  "Do you wish to cancel this record?", vbQuestion + vbYesNo, "Validation Failure") = vbYes Then
            Me.Undo
        End If
    End If
End Function

 

[miken5678]

I attached the file and I know its far from perfect.

    If blnError Then
        Cancel = True
        If MsgBox("You need to fill out these fields before we can save the record: " & vbCrLf & _
                  strError & vbCrLf & _
                  "Do you wish to cancel this record?", vbQuestion + vbYesNo, "Validation Failure") = vbYes Then
            Me.Undo]
Else
    MSGBox "Do you want to save and lock the record")vbQuestion + vbYesNo) = vbYes Then
me.statusfield = Complete       
        End If
    End If
End Function

 

[miken5678]

Private Sub Other_BeforeUpdate(Cancel As Integer)
OtherFunction
    Dim blnError As Boolean
    Dim strError As String
 
    If Forms!WindOptOutV12.Other = -1 And Len(Forms!WindOptOutV12.IndStatmentHandwrittenComment & "") = 0 Then
        blnError = True
        strError = "Other Deviations Comment" & vbCrLf
    End If
 
    'If Len(Me.AnotherControlNameHere & "") = 0 Then
    '    blnError = True
    '    strError = strError & "YourOtherControlNameHere" & vbCrLf
    'End If
 
    ' and so on for how ever many controls you want to validate that
    'something has been entered.  You can also validate other stuff about the values, etc.
 
    If blnError Then
        Cancel = True
        If MsgBox("You need to fill out these fields before we can save the record: " & vbCrLf & _
                  strError & vbCrLf & _
                  "Do you wish to cancel this record?", vbQuestion + vbYesNo, "Validation Failure") = vbYes Then
            Me.Undo
        End If
    End If
End Sub

This is what I am using now however as soon as the first criteria is met the vba fires and it pops up. Is there a way to delay this by putting it within a save/complete button? I would prefer to have it in a button as well as only activate when the user selects a new policy in the split form and goes to a new record instead of firing each time one of the criteria is met. Any suggestions on this?

 

[boblarson]

Okay, how can I get you to understand this?

Put it in the FORM'S BEFORE UPDATE EVENT. NOT the control's before update, not into a function called Validate, but in the FORM'S BEFORE UPDATE EVENT.

Is that better? Sorry, I've a splitting headache at the moment.

I have already told you this. It goes in to the form's before update event so that when a record starts to save it then validates. It matters not if you have a SAVE button or just move to a new record. The FORM'S before update event will fire if something is changed, added, etc.

 

[miken5678]

nevermind trying something rather quickly.. i thought the function would work