VBA - Access Form and ActiveControl on Date/Time datatype issue

[ac_jr]

Hello Experts,
Windy but hope I covered all infoyou need.

New to vba and coding, startedjust a few weeks ago so I know I have much to learn but this one is stumpingme. I've about tried everything I know up to now to get this code to work butits not recognizing the elseif statement when the Me. ActiveControl is on afield of the data type Date/Time, the weird thing is that the MsgBox returnsthe 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 thecode, it bypasses and executes the else parts just fine but causes issues whenthe focus is on the date_of_failure field.

What I am trying to do is when auser cancels the data input on a form (hits the ESC key before a record issaved) then to set focus back to my first field (Well_Type - part of acomposite Primary Key) except if the focus is on any of my Composite PrimaryKey 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 stayon any of these 3 fields if ESC is hit while if it is not on either of these 3fields then move the focus back to the first field, Well_Type when a new recordis being created).

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

This is a bound field, 99 percentof my fields are bound to tables and all my relationships are just fine. The 3Composite Primary Key Fields come from the same table so I do not see this asan Issue. I can create and delete records just fine so I think my databaseunderlying structure is just fine.
This is with MS Access 2013, noplugins, On Windows 7, 64 bit, computer has no issues and patched with thelatest set of SPs, hotfixes, etc...

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

Private Sub Form_Undo(Cancel AsInteger)
'Test Focus
'MsgBox "Codeis at beginning of Form_Undo"

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

ElseIfMe.ActiveControl = Me.Well Then
'MsgBox "Codeis at Form_Undo and Well"
Me.Well.SetFocus
'MsgBox "Codeis after Form_Undo and Well"

ElseIfMe.ActiveControl = Date_of_Failure Then
'MsgBox "Codeis at Form_Undo and Date_of_Failure"
Me.Date_of_Failure.SetFocus
'MsgBox "Codeis after Form_Undo and Date_of_Failure"

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

End If
''ElseIfMe.ActiveControl <> Me.Date_of_Failure Then
''MsgBox "Codeis getting here Date of Failure"
''Me.Date_of_Failure.SetFocus
''MsgBox "Codeis getting all the way here Date of Failure"
''
'ElseIfMe.ActiveControl = Me.Well Then
'MsgBox "Codeis here 5.0"
'Me.Well.SetFocus
'MsgBox "Codeis here 5.1"
''
'ElseIfMe.ActiveControl <> Me.Date_of_Failure Then
'MsgBox "Codeis here 6.0"
'Me.Well_Type.SetFocus
'MsgBox "Elsecode is here 6.1"
'End If
'MsgBox "Codeis here end of Undo"
'MsgBox "Codeis here 4.3"
Me.SetFocus
'MsgBox "Codeis 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 youare clearing a new record then go back to (click on) Well Type to startcreating the new record again ---"
Exit Sub

'MsgBox "Codeis here 4.6"
ElseIfScreen.ActiveControl = Me.Date_of_Failure Then
MsgBox "Code isseeing Date of failure field)"
Exit Sub

'MsgBox "Codeis here 4.7"
ElseIfMe.ActiveControl <> Me.Date_of_Failure Then
Me.Well_Type.SetFocus
'MsgBox "Codeis here 4.8"
Else:Me.Well_Type.SetFocus
End If
MsgBox "If youare clearing a new record then go back to (click on) Well Type to startcreating the new record again"
MsgBoxMe.ActiveControl.Name
MsgBoxScreen.ActiveControl.Name
End Sub

[/FONT]
All help is appreciated.
Thanks,
AC_jr

[jonh]

edit

"if user cancels input, set focus back to Well_Type control except if focus is on Well_Type, Well or Date_of_Failure"



Private Sub Form_Undo(Cancel As Integer) 'to debug your code, print to the immediate window...
Debug.Print "ActiveControl is " & ActiveControl.Name


If ActiveControl.Name = "Date_Rig_on_Well" Then
Debug.Print Date_Rig_on_Well, ActiveControl, Date_Rig_on_Well = ActiveControl
End If


Select Case ActiveControl.Name
Case "well_type", "well" ', "Date_Rig_on_Well"
MsgBox "If you are clearing a new record then ..."
Case "Date_of_Failure"
MsgBox "Code is seeing Date of failure field)"
Case Else 'not a pk
well_type.SetFocus
End Select
End Sub


In VBA controls have a default property - the value. So comparing activecontrol to another control with = is just asking if the values are the same, not whether they are the same object. You need to use IS instead.


if activecontrol is othercontrol then msgbox "same object"


An easy way of comparing several items is to use 'select case' but then IS has a different meaning.
Since all control names in VBA are unique in each form, it's safe to just check the name.


Also, focus is what makes a control active. If it is active it already has the focus so you don't need to set it.