Forcing data entry in Form for a Required Field, and leaving form ignoring the Code

I have this situation.  I am Currently using this code to force data entry in a required field in a form.  This works fine, but now the problem I am running into is when I click on the Exit Button.  This is the Code:

Private Sub CODREN_LostFocus()
If CODREN.Text = "" Then
MsgBox "Please provide the Contract Number to continue", vbOKOnly, "Warning"
Flag = True
Else
Flag = False
End If
End Sub

Then in the Next field I use this:

Private Sub RENREN_GotFocus()
If Flag = True Then
CODREN.SetFocus
End If
End Sub

Now, when I click in the Button to exit the form, the Message "Please provide the Contract Number to continue" shows up, and then after I click Ok the form closes.  I would like to prevent this from happening.  I was trying to introduce another If statement in the code where if the Exit button was click it would ignore the message, but I wasn't able to do it.  Any help would be highly appreciated.
Thanks in advance.
Juan
camilo_gsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
GotFocus and LostFocus are not the best events to handle data validation in a form.  

Better to do this in the Form BeforeInsert event:  If the value is good, allow the update to happen, and if the value is not good, then Cancel = True to cancel the update.

Hope this helps.
-Jim
0
 
jjafferrCommented:
Hi camilo_gs,

place Exit Sub at the end of your if statement, before docmd.close
this will not allow the code to continue, it will break it before it reaches docmd.close

Hope this helps

Jaffer
0
 
stevbeConnect With a Mentor Commented:
I like using the BeforeUpdate event ...

Private Sub Form_BeforeUpdate(Cancel As INteger)
    If CODREN.Text = "" Then
        MsgBox "Please provide the Contract Number to continue", vbOKOnly, "Warning"
        Me.CODREN.SetFocus
        Cancel = True
    End If
End Sub
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks like you have some solid advice to your question.  Please award points and close the question.  Thanks in advance.  -Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
A couple of testing iterations would be nice here to see if our comments (all of which make sense to me) would solve her issue(s).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.