none
Please help with ActiveControl and Date/Time field not recognizing focus? RRS feed

  • Question

  • Hello Experts,

    Windy but hope I covered all info you need.

    New to vba and coding, started just a few weeks ago so I know I have much to learn but this one is stumping me. I've about tried everything I know up to now to get this code to work but its not recognizing the elseif statement when the Me. ActiveControl is on a field of the data type Date/Time, the weird thing is that the MsgBox returns the name just fine (weather I request the name off the form or screen object) so Access itself does know it but it just will not execute that part of the code, it bypasses and executes the else parts just fine but causes issues when the focus is on the date_of_failure field.

    What I am trying to do is when a user cancels the data input on a form (hits the ESC key before a record is saved) then to set focus back to my first field (Well_Type - part of a composite Primary Key) except if the focus is on any of my Composite Primary Key Fields (Well_Type itself, Well, and Date_of_Failure (in question) fields) since a lot other validation hinges on these fields (i.e. I want focus to stay on any of these 3 fields if ESC is hit while if it is not on either of these 3 fields then move the focus back to the first field, Well_Type when a new record is being created).

    I've tried with the Undo(Cancel) event on the form as well as the KeyPress(Keycode) event and both seem to ignore the if statement when the active field is on a date/time type of field... I've also tried the cancel event on the individual fields and this is not desired since I want this to work at the form level, I really want to cancel the input for the whole form and reset the focus to the first field. 

    This is a bound field, 99 percent of my fields are bound to tables and all my relationships are just fine. The 3 Composite Primary Key Fields come from the same table so I do not see this as an Issue. I can create and delete records just fine so I think my database underlying structure is just fine.

    This is with MS Access 2013, no plugins, On Windows 7, 64 bit, computer has no issues and patched with the latest set of SPs, hotfixes, etc...

    Part of the code below, I've debugged with message boxes (most as comments now) to tell me where the code is at and it never sees the Date_of_failure code when the focus is on it and hit the ESC key, weather I leave it null and hit ESC or I populate it and hit ESC or highlight the date value after I populate it and then hit ESC... Setting the code to = or <> both ignores the focus on that field, focus on all the rest is detected and code properly excecuted.

    Private Sub Form_Undo(Cancel As Integer)

    'Test Focus
    'MsgBox "Code is at beginning of Form_Undo"

    If Me.ActiveControl = Me.Well_Type Then
    'MsgBox "Code is at Form_Undo and Well_Type"
    Me.Well_Type.SetFocus
    'MsgBox "Code is after Form_Undo and Well_Type"

    ElseIf Me.ActiveControl = Me.Well Then
    'MsgBox "Code is at Form_Undo and Well"
    Me.Well.SetFocus
    'MsgBox "Code is after Form_Undo and Well"

    ElseIf Me.ActiveControl = Date_of_Failure Then
    'MsgBox "Code is at Form_Undo and Date_of_Failure"
    Me.Date_of_Failure.SetFocus
    'MsgBox "Code is after Form_Undo and Date_of_Failure"

    'Do not use, Date of Failure does not get focus and will go to this code and will error.
    'Else
    'MsgBox "this is clearing the form"
    'Me.Well_Type.SetFocus

    End If
    ''ElseIf Me.ActiveControl <> Me.Date_of_Failure Then
    ''MsgBox "Code is getting here Date of Failure"
    ''Me.Date_of_Failure.SetFocus
    ''MsgBox "Code is getting all the way here Date of Failure"
    ''
    'ElseIf Me.ActiveControl = Me.Well Then
    'MsgBox "Code is here 5.0"
    'Me.Well.SetFocus
    'MsgBox "Code is here 5.1"
    ''
    'ElseIf Me.ActiveControl <> Me.Date_of_Failure Then
    'MsgBox "Code is here 6.0"
    'Me.Well_Type.SetFocus
    'MsgBox "Else code is here 6.1"
    'End If
    'MsgBox "Code is here end of Undo"
    'MsgBox "Code is here 4.3"
    Me.SetFocus
    'MsgBox "Code is here 4.4"
    If (Me.ActiveControl = Me.Well_Type) Or (Me.ActiveControl = Me.Well) Or (Me.ActiveControl = Me.Date_Rig_on_Well) Then
    MsgBox "If you are clearing a new record then go back to (click on) Well Type to start creating the new record again ---"
    Exit Sub

    'MsgBox "Code is here 4.6"
    ElseIf Screen.ActiveControl = Me.Date_of_Failure Then
    MsgBox "Code is seeing Date of failure field)"
    Exit Sub

    'MsgBox "Code is here 4.7"
    ElseIf Me.ActiveControl <> Me.Date_of_Failure Then
    Me.Well_Type.SetFocus
    'MsgBox "Code is here 4.8"
    Else: Me.Well_Type.SetFocus
    End If
    MsgBox "If you are clearing a new record then go back to (click on) Well Type to start creating the new record again"
    MsgBox Me.ActiveControl.Name
    MsgBox Screen.ActiveControl.Name
    End Sub

    All help is appreciated.

    Thanks,

    AC_jr

     
    Thursday, October 22, 2015 2:51 PM

Answers

  • A line such as

    If Me.ActiveControl = Me.Well_Type Then

    won't do what you want since it compares the values of the controls. You have to check the name of the active control:

    Private Sub Form_Undo(Cancel As Integer)
        Select Case Me.ActiveControl.Name
            Case "Well_Type", "Well", "Date_of_Failure"
                ' Don't do anything
            Case Else
                ' Set focus to Well_Type
                Me.Well_Type.SetFocus
        End Select
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ac_jr Thursday, October 22, 2015 3:24 PM
    Thursday, October 22, 2015 3:05 PM

All replies

  • A line such as

    If Me.ActiveControl = Me.Well_Type Then

    won't do what you want since it compares the values of the controls. You have to check the name of the active control:

    Private Sub Form_Undo(Cancel As Integer)
        Select Case Me.ActiveControl.Name
            Case "Well_Type", "Well", "Date_of_Failure"
                ' Don't do anything
            Case Else
                ' Set focus to Well_Type
                Me.Well_Type.SetFocus
        End Select
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ac_jr Thursday, October 22, 2015 3:24 PM
    Thursday, October 22, 2015 3:05 PM
  • Thank's Hans, adding the name to the control did the trick, wonder why the other two fields were working/checking just fine (and a few more non date/time field) without checking the name in the property, anyhow you've saved me a great deal, things are working as expected!

    AC_jr

    Thursday, October 22, 2015 3:25 PM