locked
Catching A Missing Subform Field When Moving to Main Form RRS feed

  • Question

  • Hello:

    I have an Access form with a primary form and one sub-form.  There is a date field in the sub-form that must be entered. What is happening is that when the user is working in the sub-form, they click on the main form, which transfers control that that form.  How can I catch the missing date in the sub-form when the user click in the MAIN form?

    I tried the "On Deactivate" sub-form event but it didn't even go to that portion of the code.

    The events that I have available are in the image shown in this post.

    Thanks,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, December 3, 2020 7:40 PM

Answers

  • Hi Rich

    To manage this you need to add code to the On_Exit event of the subform control in the main form as the focus will then have left the subform itself

    For example, your code might be:

    Private Sub subformcontrolname_Exit(Cancel As Integer)   

    If Len(Me.subformcontrolname.Form.datefield & "") = 0 Then        

    MsgBox "You MUST enter a date"    End If

    End Sub





    • Edited by isladogs52 Thursday, December 3, 2020 8:17 PM
    • Marked as answer by RichLocus Thursday, December 3, 2020 9:24 PM
    Thursday, December 3, 2020 8:15 PM
  • For example, your code might be:

    Private Sub subformcontrolname_Exit(Cancel As Integer)   

    If Len(Me.subformcontrolname.Form.datefield & "") = 0 Then        

    MsgBox "You MUST enter a date"    End If

    End Sub

    Hi Rich,

    In addition to isladogs' code, you can add the line:

            Cancel = TRUE

    in order to stay in the subform.

    It is also advisable to add code in the Exit event of the Control itself, to check whether it has a value, and if that value is a meaningful value.
    Why the Exit event? The Exit event has a Cancel parameter, as also has the BeforeUpdate event. But the BeforeUpdate event is not triggered when the date is filled through code, e.g. a DatePicker. And you always have to Exit the control.

    Imb.

    • Marked as answer by RichLocus Thursday, December 3, 2020 9:24 PM
    Thursday, December 3, 2020 9:00 PM

All replies

  • Hi Rich

    To manage this you need to add code to the On_Exit event of the subform control in the main form as the focus will then have left the subform itself

    For example, your code might be:

    Private Sub subformcontrolname_Exit(Cancel As Integer)   

    If Len(Me.subformcontrolname.Form.datefield & "") = 0 Then        

    MsgBox "You MUST enter a date"    End If

    End Sub





    • Edited by isladogs52 Thursday, December 3, 2020 8:17 PM
    • Marked as answer by RichLocus Thursday, December 3, 2020 9:24 PM
    Thursday, December 3, 2020 8:15 PM
  • Good question. According to the instructions for the Forms Lost Focus Event:

    "If you move the focus from a control on a subform to a control on the main form, the Exit and LostFocus events for the control on the subform don't occur, just the Exit event for the subform control and the Enter and GotFocus events for the control on the main form."

    However, this is not true. The Exit Event for the sub-form control does NOT fire if you move to the main form. It only fires if you move to another control on the same sub-form. Here is the reference:

    Form.LostFocus event (Access) | Microsoft Docs

    Thursday, December 3, 2020 8:51 PM
  • For example, your code might be:

    Private Sub subformcontrolname_Exit(Cancel As Integer)   

    If Len(Me.subformcontrolname.Form.datefield & "") = 0 Then        

    MsgBox "You MUST enter a date"    End If

    End Sub

    Hi Rich,

    In addition to isladogs' code, you can add the line:

            Cancel = TRUE

    in order to stay in the subform.

    It is also advisable to add code in the Exit event of the Control itself, to check whether it has a value, and if that value is a meaningful value.
    Why the Exit event? The Exit event has a Cancel parameter, as also has the BeforeUpdate event. But the BeforeUpdate event is not triggered when the date is filled through code, e.g. a DatePicker. And you always have to Exit the control.

    Imb.

    • Marked as answer by RichLocus Thursday, December 3, 2020 9:24 PM
    Thursday, December 3, 2020 9:00 PM
  • "If you move the focus from a control on a subform to a control on the main form, the Exit and LostFocus events for the control on the subform don't occur, just the Exit event for the subform control and the Enter and GotFocus events for the control on the main form."

    Hi Lawrence,

    You must distinguish between the Control on the Subform, and the Subform-control itself on the Main form.

    As far as my practical experience goes, the Exit event of the Control on the subform is not fired, but the Exit event of the Subform-control on the Main form is indeed fired.

            Add: when going from (a control on) the Subform to a control on the Main form

    Imb.


    • Edited by Imb-hb Thursday, December 3, 2020 9:11 PM Add
    Thursday, December 3, 2020 9:09 PM
  • Hello isladogs52:

    That was a good start!  However, it landed me on the next main form record and didn't allow the user to correct the issue.  I'm still recording this as helpful!!

    Thanks!

    Rich Locus


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, December 3, 2020 9:14 PM
  • imb:

    Thanks for the proposed answer.  I'm working on it now with your suggestions and will post it as the answer if I don't have any more questions.

    Thanks!


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, December 3, 2020 9:20 PM
  • imb:

    Thanks!  worked like a charm.


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, December 3, 2020 9:25 PM
  • isladogs52:

    Your answer was only missing one line, so I'm also giving you the answer confirmation :)

    All it needed was "Cancel = Yes"

    Private Sub frmBeerProductionBatchesB_Detail_Exit(Cancel As Integer)
    If IsNull(frmBeerProductionBatchesB_Detail.Form!PackageRackEventDate) Or _
        frmBeerProductionBatchesB_Detail.Form!PackageRackEventDate = "" Then
        frmBeerProductionBatchesB_Detail.Form!PackageRackEventDate.SetFocus
        MsgBox ("Package/Rack Event Date is Required")
        Cancel = True
        Exit Sub
    End If
    End Sub


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, December 3, 2020 9:27 PM
  • Hi Rich

    That's odd as I tested it before posting. It stayed on the same record.

    I agree with imb's suggestion of adding the line Cancel=True to ensure that the control remains on the subform.

    Even better, also add code to restore code to the date field control itself:

    Private Sub subformcontrolname_Exit(Cancel As Integer)
        If Len(Me.subformcontrolname.Form.datefield & "") = 0 Then 
           MsgBox "You MUST enter a date"
           Cancel=True
           Me.subformcontrolname.Form.datefield.SetFocus
        End If
    End Sub


    Thursday, December 3, 2020 9:28 PM
  • Even better, also add code to restore code to the date field control itself:

    Private Sub subformcontrolname_Exit(Cancel As Integer)
        If Len(Me.subformcontrolname.Form.datefield & "") = 0 Then 
           MsgBox "You MUST enter a date"
           Cancel=True
           Me.subformcontrolname.Form.datefield.SetFocus
        End If
    End Sub

    Hi Rich and isladogs,

    In the above case it is Rich's task to ensure that the datefield control gets the focus when opening the form.

    If it does not get the focus, and the user does not touch the control, the Exit event of that control never fires.

    Then, after Cancel is set to TRUE, the control keeps the focus, so it is not necessary to re-focus the control.

    Imb.

    Edit:

    What also can be added to the above Exit procedure, is a check on a meaningful date. Probably it will (mostly?) be Date (or Today), but some date in the next year or in the previous year - as an example - should not be accepted.

    • Edited by Imb-hb Thursday, December 3, 2020 9:48 PM edit
    Thursday, December 3, 2020 9:44 PM
  • Thanks!

    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, December 3, 2020 9:56 PM
  • To clarify...

    I'm in the subform and haven't entered a date....

    Whilst in a different control, I attempt to leave the subform by clicking on the main form...

    The code in my last response ensures that the user is immediately returned to the date field control

    Thursday, December 3, 2020 10:02 PM
  • The code in my last response ensures that the user is immediately returned to the date field control

    Hi isladogs,

    Yes, you are right. I overlooked that it is the Exit event of the Subform-control on the Main form.

    Some additional remark about Date-controls that my users appreciate very much.

    On any Date-control, that is editable, I can doubleclick to open a "Home-made" calender form, but that is not special.
    But clicking on "." or "," will increase viz. decrease the date with 1. Holding the Shift-key at the same time (thus using ">" or "<") will increase viz. decrease the date with 1 month.
    If the Date-control is Null, then the first click (with ".", ",", ">'" or "<") will initialize on Date (today).

       

    Imb.

    Thursday, December 3, 2020 10:27 PM