Alternative to Screen.PreviousControl

[Robert Neville]

I utilized some record navigation code some time ago, which worked
until I place it on a nested sub-form (don't ask). Now, I need another
alternative to passing these controls to the global function. I
thought about hard-coding it as well, yet would like to get some
suggestions before proceeding with this workaround.

I need an alternative to Screen.PreviousControl and Screen.ActiveForm.
The challenge for me involves referencing Access objects specifically
controls on a sub-form. I have tried several approaches with "Me"
without success.

Call FrmFocusSave(Me!PreviousControl)
Call FrmNavGotoNext(Me!ActiveForm)

Call FrmFocusSave(Me.PreviousControl)
Call FrmNavGotoNext(Me.ActiveForm)


MS Help and a Google Group shed little insight on my scenario. MS Help
files are very elusive if you can even find the desired topic. Could
someone explain if I need to reference the form name, object, or
control? Please leave me suggestions or links on referencing all form
objects whether implicit or explicit. E.g. requery a list box on a
subform or parent form on a separate tab control. So I could add them
to my notes.

The code below gives you an idea of the current approach. Please
remember other navigation buttons use a variation of this code. This
point is reason behind not over simplifying by choosing the
workaround.

Private Sub btnNext_Click()

Const cstrProc As String = "btnNext_Click"
On Error GoTo btnNext_Click_Err

Call FrmFocusSave(Screen.PreviousControl)
Call FrmNavGotoNext(Screen.ActiveForm)
Call FrmFocusRestore

btnNext_Click_Exit:
Exit Sub

btnNext_Click_Err:
Call ErrMsgStd(Me.Name & "." & cstrProc, Err.Number,
Err.Description, True)
Resume btnNext_Click_Exit

End Sub

Public Sub FrmNavGotoNext(rfrm As Form)
' Purpose: Goto next record in form recordset
' Arguments: rfrm:=Calling form
' Calls: FrmNavIsLast

Const cstrProc As String = "FrmNavGotoNext"
On Error GoTo FrmNavGotoNext_Err

If rfrm.mtdValidate Then ' Record is valid
If Not FrmNavIsLast(rfrm) Then
'rfrm.SetFocus ' Just to be sure
Application.RunCommand acCmdRecordsGoToNext
End If
End If

FrmNavGotoNext_Exit:
Exit Sub

FrmNavGotoNext_Err:
Call ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number,
Err.Description, True)
Resume FrmNavGotoNext_Exit

End Sub

 

[Ken Snell]

The Me object does not have a "previous control" nor an "active form"
property. To do the previous control, you'd need to capture via code the
name of each control as it receives focus (likely by having 2 global
variables that holdsthe name of the previous control that had the focus and
the name of the control that now has the focus, and then running code on the
OnGotFocus event for each control moving the name of the control that is in
the "current focus" control into the "previous" global variable and then
saving the name of the current control into that global variable); this
could get tricky.

To do the "active form", you'd need to know if the form that is running the
code is the active form. If it is, just use Me.Name as the name of that
form, or else use Me alone if you want to pass the form object to the
subroutine).

 

[Dirk Goldgar]

I utilized some record navigation code some time ago, which worked
until I place it on a nested sub-form (don't ask). Now, I need another
alternative to passing these controls to the global function. I
thought about hard-coding it as well, yet would like to get some
suggestions before proceeding with this workaround.

I need an alternative to Screen.PreviousControl and Screen.ActiveForm.
The challenge for me involves referencing Access objects specifically
controls on a sub-form. I have tried several approaches with "Me"
without success.

Call FrmFocusSave(Me!PreviousControl)
Call FrmNavGotoNext(Me!ActiveForm)

Call FrmFocusSave(Me.PreviousControl)
Call FrmNavGotoNext(Me.ActiveForm)


MS Help and a Google Group shed little insight on my scenario. MS Help
files are very elusive if you can even find the desired topic. Could
someone explain if I need to reference the form name, object, or
control? Please leave me suggestions or links on referencing all form
objects whether implicit or explicit. E.g. requery a list box on a
subform or parent form on a separate tab control. So I could add them
to my notes.

The code below gives you an idea of the current approach. Please
remember other navigation buttons use a variation of this code. This
point is reason behind not over simplifying by choosing the
workaround.

Private Sub btnNext_Click()

Const cstrProc As String = "btnNext_Click"
On Error GoTo btnNext_Click_Err

Call FrmFocusSave(Screen.PreviousControl)
Call FrmNavGotoNext(Screen.ActiveForm)
Call FrmFocusRestore

btnNext_Click_Exit:
Exit Sub

btnNext_Click_Err:
Call ErrMsgStd(Me.Name & "." & cstrProc, Err.Number,
Err.Description, True)
Resume btnNext_Click_Exit

End Sub

Public Sub FrmNavGotoNext(rfrm As Form)
' Purpose: Goto next record in form recordset
' Arguments: rfrm:=Calling form
' Calls: FrmNavIsLast

Const cstrProc As String = "FrmNavGotoNext"
On Error GoTo FrmNavGotoNext_Err

If rfrm.mtdValidate Then ' Record is valid
If Not FrmNavIsLast(rfrm) Then
'rfrm.SetFocus ' Just to be sure
Application.RunCommand acCmdRecordsGoToNext
End If
End If

FrmNavGotoNext_Exit:
Exit Sub

FrmNavGotoNext_Err:
Call ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number,
Err.Description, True)
Resume FrmNavGotoNext_Exit

End Sub

Assuming your command button is on the form object (whether main form or
subform) to be "navigated", then you probably want to call
FrmNavGotoNext() passing just the "Me" reference:

Call FrmNavGotoNext(Me)

PreviousControl is trickier. While each form has its own ActiveControl
property, it doesn't have a PreviousControl property, so the only
"PreviousControl" available is Screen.PreviousControl. However, near as
I can make out, Screen.PreviousControl returns the previous control on
the active data object, so it should still work if used on the subform.
In other words, I think you should still be able to write:

Call FrmFocusSave(Screen.PreviousControl)

Are you sure that doesn't work, if you fix the problem with
FrmNavGoToNext and ActiveForm?