Cancel SetFocus on Text Field When Hitting Exit Command Button (1 Viewer)

[state90]

By no means am I a VBA expert. I am getting better and probably to the extent where I am most dangerous! 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

 

[Ranman256]

don't test for validation in the exit..
test it on the button click, if valid, THEN exit.

 sub btnSaveExit_click
    if IsValidForm() then
        docmd.close
    endif
 end sub
  
 function IsValidForm() as Boolean
 dim vMsg
     select case true
         case isNull(txtName)
                 vMsg = "Name is reqd"
                 txtName.setfocus 
         case...
     end select
 
     IsValidForm = vMsg = ""
    msgbox vMsg
   end function

 

[state90]

Thank you for your response. Just to make sure I am understanding your recommendations correctly you are recommending

(1) That instead of having the following coding in each On Exit event of the required text fields I should remove those and do it all in the On Click event of the Main Menu command button?

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

(2) In your coding for the On Click event of the Main Menu (or exit) command button you specified one validation check on a required text field. If I have multiple required fields would your coding now look something like this below?

sub MainMenu_click
if IsValidForm() then
docmd.close
endif
end sub

function IsValidForm() as Boolean
dim vMsg
select case true
case isNull(txtName)
vMsg = "Name is reqd"
txtName.setfocus
case isNull(txtCustomerNo)
vMsg = "Name is reqd"
txtCustomerNo.setfocus
case isNull(txtStartDate)
vMsg = "Name is reqd"
txtStartDate.setfocus
(etc. for all the required fields)
end select

IsValidForm = vMsg = ""
msgbox vMsg
end function

 

[ddcessex]

From one Newbie to another....

Your topic is about Data Entry Validation and the method that you are using is fraught with danger of the user being stuck within an infinite loop such as the one that you highlight. Other Gotchas await you when the user is kept being put into one textbox when another is trying to do the same thing!

An approach that you might like to consider is to leave data entry validation until the user decides to save the record and not when exiting each textbox or control.

After all the user may decide to abandon the entry and cancel and it would be unfriendly to have to force data entry of the fields before allowing them to do so. Typically what happens is that the users work through the form until they discover that they are missing the data for one of the required fields so they cannot complete the form anyway.

So, assuming that you have a Command Button called cmdSave then you might consider this construct:

(please excuse any inaccuracies, I typed the code as part of this message to give you an idea so it hasn't been error checked!)

Private Sub cmdSave_Click()
If DataEntryIsValid() = False Then Goto cmdSave_Click_Exit

DoCmd.RunCommand acCmdSaveRecord
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
Else
RefreshTheDisplay
End If


cmdSave_Click_Exit:
Exit Sub

cmdSave_Click_Err:
MsgBox Error$, vbCritical
Resume cmdSave_Click_Exit
End Sub



Private Function DataEntryIsValid() as Boolean
Dim strMessage As string

DataEntryIsValid = False

If Trim$(Company.Value & "") = "" Then
strMessage = "Naf Company data...."
Company.SetFocus
Goto Failed
End if
If Trim$(PostCode.Value & "") = "" Then
strMessage = "Naf Post Code data...."
PostCode.SetFocus
Goto Failed
End if
If Trim$(JujuCode.Value & "") = "" Then
strMessage = "Naf Juju data...."
JujuCode.SetFocus
Goto Failed
End if
etc
etc.
etc.

DataEntryIsValid = True

Exit Function


Failed:
MsgBox strMessage, vbInfo
'Jumping to Failed leaves DataEntryIsValid = False
End Function


I hope this gives you some ideas