Cancel SetFocus on Text Field When Hitting Exit Command Button

[state90]

I have a form with required fields. In order to force these requirements on the user I use a message box to alert the user of the requirement followed by a SetFocus back to the required text field. However, I do have a command button that allows the user to basically cancel the data entry session for the form and exit to the Main Menu. How can I override or cancel the SetFocus on the various required fields when the user simply opts to cancel the data entry and exit to the main menu? Because of the SetFocus on these text fields I keep getting the message box alert that the field is required and placed back into the required field as a result fo the SetFocus.

I have copied the VBA for one of the required fields (They are all basically the same) and the VBA behind the "Main Menu" command button below. Any help is much appreciated.

Text Field VBA:

Private Sub Txt_Cust_Name_Exit(Cancel As Integer)
 
If IsNull(Me.Txt_Cust_Name) Then
 
    MsgBox "This is a Required Field." & vbCrLf & "Please Enter the Customer Name Before Proceeding"
    Cancel = True
    Me.Txt_Cust_Name.SetFocus
 
End If
 
End Sub

Main Menu Command Button:

Private Sub Cmd_Open_Main_Menu_06_Click()
 
MsgBox "This New Customer Has NOT Been Added"
DoCmd.Close acForm, "Frm_New_Customer", acSaveNo
 
DoCmd.OpenForm "Frm_Main_Menu", acNormal
 
End Sub

[PhilOfWalton]

Several suggestions.
Firstly, I keep the Main Menu open the whole time, because, if you want to look up some information on form 2, and minimise form 1, the menu is there to open form2.

Secondly, I suggest you do all your checking that fields are entered correctly on the Before_Update of the form. At the moment you appear to be checking only that the required fields are filled in. You may also want to check one piece of data against another. As an example, suppose you have a field with a value man, woman child, you may want to check that if it is a child their birthday is entered.

To cancel your entry using a command button try

Private Sub Btn1_Click
    SendKeys "{ESC}{ESC}"
    ' Optionally close the form here with any message
    ' Optionally open your main menu here
End Sub
 

Phil

[zmbd]

I personally would AVOID the SENDKEYS command

At one time SENDKEYS was very useful and often the only way to accomplish some tasks; however, it can be very flaky as one cannot always be sure what object has the focus due to the user interaction! Say the user has your application and Word open. I know, 99.9% of the time in this case, most likely not an issue... it's the 0.1% that will cause you the most heartburn.

One option I use is a form level variable that I set for check points such as this:

Open your form in design view.
<alt><f11> to open the VBA Editor Window
Show the code for your form (easy enough, on the left should be the navigation pane, your form should be listed, simply right-click and show code

You should have the:

Option Compare Database
Option Explicit

lines in this module (and all others IMHO read the trouble shooting section here> > Before Posting (VBA or SQL) Code as for why this is so importaint

Just below these two lines insert

Private zFailSafe As Boolean

or any other name you'd like to give it

I tend to set this value to False in the form's on_load event just to make sure of the value.

Now in your code

Private Sub Txt_Cust_Name_Exit(Cancel As Integer)
   If not zFailSafe then 
      If IsNull(Me.Txt_Cust_Name) Then
 
        MsgBox "This is a Required Field." & vbCrLf & "Please Enter the Customer Name Before Proceeding"
        Cancel = True
        Me.Txt_Cust_Name.SetFocus
 
       End If
   end if
 
 End Sub

In your close and open command button set the zFailSafe to True:

Private Sub Cmd_Open_Main_Menu_06_Click()
 
    zFailSafe = True
    MsgBox "This New Customer Has NOT Been Added"
    DoCmd.Close acForm, "Frm_New_Customer", acSaveNo
 
    DoCmd.OpenForm "Frm_Main_Menu", acNormal
 
End Sub

Now this way, your control's exit event my fire; however, nothing happens if the "Open_Main_Menu" is used to close the form.

>> As for checking the data during entry (using the before_update event)... a very good point and if the form is bound something that one really should consider - I usually do this at the table level for most data validation which helps to keep the form's code cleaner; however, there are some situations that one simply must code for to ensure proper data entry.