Subform Navigation

[Rational Repairs]

I am trying to create global code for navigation use in all my forms --
including subforms. I searched, and searched, but can not find how to
globally reference a subform object. I need to have the navigation
buttons on the subform itself, but I would like to code to be global so
I can reuse it in my many subforms.

>From what I understand, a subform can't be referenced as an open form,

but only as a control in the partent form. So how can I change records
in a control?

I have tried to reference the subform every way I could think of, or
could find here or in help:

Forms![frmMyParentForm]![ctrlMySubFormOb ject].Form
Forms![frmMyParentForm]![ctrlMySubFormOb ject].SourceObject

I have also tried doing this via a string for the form name, and using
the form object. I think I would rather use the form object, as I would
also like to do some other things beside change records (enable/disable
buttons, ext)

Can someone help me here, or point me to some more info? Thanks in
advance.

Jeff Smeker

[Darryl Kerkeslager]

Pass the Me reference to the function/sub.

Public Sub MoveToNext(curr entRecordId As Long, frm As Form)
{Your code here}
Ends Sub

Call it from a form or subform:

MoveToNext Me.txtId, Me

Of course, writing the code to do what you want may still be tricky.

I used this as a global listbox refresh for forms and subforms
=============== ==========
Public Sub List_Refresh(id As String, frm As Form, _
Optional move_cursor = 0)
Dim rsc As Object
Dim lst As ListBox
Set rsc = frm.Recordset.C lone
Set lst = frm!lstRecords
lst.Requery
If rsc.RecordCount <= 0 Then
Set lst = Nothing
Exit Sub
End If

If move_cursor = NEW_ITEM Then
rsc.MoveLast
frm.Bookmark = rsc.Bookmark
lst.Value = frm!txtId
Else
If move_cursor = LAST_ITEM Then
lst.Value = lst.ItemData(ls t.ListCount - 1)
Else
If IsNull(lst.Valu e) Or move_cursor = TOP_ITEM Then
If lst.ColumnHeads Then
lst.Value = lst.ItemData(1)
Else
lst.Value = lst.ItemData(0)
End If
End If
End If
If Not IsNull(lst.Valu e) Then
rsc.Findfirst id & "=" & lst.Value
If Not rsc.EOF Then frm.Bookmark = rsc.Bookmark
End If
End If
Set lst = Nothing
End Sub
--
Darryl Kerkeslager

"Rational Repairs" <ra************ *@gmail.comwrot e

>I am trying to create global code for navigation use in all my forms --
including subforms. I searched, and searched, but can not find how to
globally reference a subform object. I need to have the navigation
buttons on the subform itself, but I would like to code to be global so
I can reuse it in my many subforms.

[Rational Repairs]

Thanks for the tip. I should have mentioned, I tried passing the Me
reference. The problem is that I receive errors saying the specific
subform isn't open. I think that when a form is being used as a
subform, is not *really* open (meaning it's not found in the forms
collection), so it can't be referenced that way.

Even in your code example below, passing Me from a subform wound't
work. Unless I'm completely missing something here. Wouldn't be the
first time!

If a subform isn't an open form, how the heck do you work with it
globally via VB?

Thanks again.

Darryl Kerkeslager wrote:

Pass the Me reference to the function/sub.

Public Sub MoveToNext(curr entRecordId As Long, frm As Form)
{Your code here}
Ends Sub

Call it from a form or subform:

MoveToNext Me.txtId, Me

Of course, writing the code to do what you want may still be tricky.

I used this as a global listbox refresh for forms and subforms
=============== ==========
Public Sub List_Refresh(id As String, frm As Form, _
Optional move_cursor = 0)
Dim rsc As Object
Dim lst As ListBox
Set rsc = frm.Recordset.C lone
Set lst = frm!lstRecords
lst.Requery
If rsc.RecordCount <= 0 Then
Set lst = Nothing
Exit Sub
End If

If move_cursor = NEW_ITEM Then
rsc.MoveLast
frm.Bookmark = rsc.Bookmark
lst.Value = frm!txtId
Else
If move_cursor = LAST_ITEM Then
lst.Value = lst.ItemData(ls t.ListCount - 1)
Else
If IsNull(lst.Valu e) Or move_cursor = TOP_ITEM Then
If lst.ColumnHeads Then
lst.Value = lst.ItemData(1)
Else
lst.Value = lst.ItemData(0)
End If
End If
End If
If Not IsNull(lst.Valu e) Then
rsc.Findfirst id & "=" & lst.Value
If Not rsc.EOF Then frm.Bookmark = rsc.Bookmark
End If
End If
Set lst = Nothing
End Sub
--
Darryl Kerkeslager

"Rational Repairs" <ra************ *@gmail.comwrot e

I am trying to create global code for navigation use in all my forms --
including subforms. I searched, and searched, but can not find how to
globally reference a subform object. I need to have the navigation
buttons on the subform itself, but I would like to code to be global so
I can reuse it in my many subforms.

[Darryl Kerkeslager]

The subform is really open (you can run code in the open event to test
this). While the subform is not added to the Forms collection, that doesn't
mean it's not open - it still can be referenced as a form by using Me. That
is why from the subform, you can use code such as

MsgBox Me.txtString

but not

MsgBox Forms("TheSubfo rm").txtStrin g

Even in your code example below, passing Me from a subform wouldn't
work.

But it does, otherwise I wouldn't have posted it.

Perhaps, at the time you called the function passing in Me as a form
reference, the subform was in fact not open?
--
Darryl Kerkeslager

[Lyle Fairfield]

Rational Repairs wrote:

Thanks for the tip. I should have mentioned, I tried passing the Me
reference. The problem is that I receive errors saying the specific
subform isn't open. I think that when a form is being used as a
subform, is not *really* open (meaning it's not found in the forms
collection), so it can't be referenced that way.

If the form has a code module, or the HasModule property set to True it
can be referenced from anywhere with
Form_FormName
that is if the sub form is named OrderDetails then
Form_OrderDetai ls
points to the form, whether or not it's being used as a subform
and
Form_OrderDetai ls.Property
Form_OrderDetai ls.Function
Form_OrderDetai ls.Sub
Form_OrderDetai ls.Control
are all valid for Public procedures and controls.

[Rational Repairs]

I guess I should not have said your code example wound't work before
testing it.

But, it appears various methods work differently. For example:

Dim frmForm as Form
Set frmForm = 'form being passed to function
DoCmd.GoToRecor d acForm, frmForm, , acNext

Received the "form not open" error (The button used to initiate the
function is on the form in question. So it was open)...

....but

frmForm.Records et.RecordCount

Works like a champ.

I did find that this, instead, will work to change the record:
DoCmd.GoToRecor d acActiveDataObj ect, , acNext

This works but relies on the subform having the focus. Being that the
buttons are located on the subform *I guess* that will always be the
case. Doesn't seem right though.

Am I thinking correctly here?
Darryl Kerkeslager wrote:

The subform is really open (you can run code in the open event to test
this). While the subform is not added to the Forms collection, that doesn't
mean it's not open - it still can be referenced as a form by using Me. That
is why from the subform, you can use code such as

MsgBox Me.txtString

but not

MsgBox Forms("TheSubfo rm").txtStrin g

Even in your code example below, passing Me from a subform wouldn't
work.

But it does, otherwise I wouldn't have posted it.

Perhaps, at the time you called the function passing in Me as a form
reference, the subform was in fact not open?
--
Darryl Kerkeslager

[Darryl Kerkeslager]

Your original post said:

>I searched, and searched, but can not find
how to globally reference a subform object

You now have two methods: pass the Me reference to a function, or Lyle has
also posted a method that I was unaware of.

Re-reading you original post, I see:

>I think I would rather use the form object, as I would
also like to do some other things beside change records
(enable/disable buttons, ext)

IMHO, it is far easier to enable/disable controls in code used in the
form/subform's module, most importantly because you can use the Me reference

Me!txtID

without having to refer to the subform

Me!boxPeskySubF orm.Form.txtID

or using the forms collection

Forms("MainForm ").boxPeskySubF orm.Form.txtID

Me also tends to be a marginally quicker way to reference the control, and
probably more so if you have to call a function.

I would say the same for using global code to navigate the form. What you
gain in modular coding, you are losing in simplicity (and maybe speed).

--
Darryl Kerkeslager

[David W. Fenton]

"Darryl Kerkeslager" <ke*********@co mcast.netwrote in
news:Ic******** *************** *******@comcast .com:

Pass the Me reference to the function/sub.

Public Sub MoveToNext(curr entRecordId As Long, frm As Form)
{Your code here}
Ends Sub

I would define that ByVal so as not to create an implicit reference
to the control passed to the first argument.

Call it from a form or subform:

MoveToNext Me.txtId, Me

Of course, writing the code to do what you want may still be
tricky.

I used this as a global listbox refresh for forms and subforms
============== ===========
Public Sub List_Refresh(id As String, frm As Form, _
Optional move_cursor = 0)
Dim rsc As Object
Dim lst As ListBox
Set rsc = frm.Recordset.C lone

Er, why not frm.RecordsetCl one? That will work in all versions of
Access, where frm.Recordset.C lone will work only from A2K on.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Rational Repairs" <ra************ *@gmail.comwrot e in
news:11******** **************@ f16g2000cwb.goo glegroups.com:

But, it appears various methods work differently. For example:

Dim frmForm as Form
Set frmForm = 'form being passed to function
DoCmd.GoToRecor d acForm, frmForm, , acNext

Received the "form not open" error (The button used to initiate
the function is on the form in question. So it was open)...

DoCmd form operations are not needed. All you need to do to navigate
records is to use bookmark navigation, using the subform's
RecordsetClone, as outlined in the code Darryl posted.

Seems to me that nobody but novices uses DoCmd.GoToRecor d for plain
old record navigation, whether in parent forms or child forms.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Darryl Kerkeslager]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote

Er, why not frm.RecordsetCl one? That will work in all versions of
Access, where frm.Recordset.C lone will work only from A2K on.

I began programming in Access in 2001. Although I've had to use and open 97
databases, I've never had to write to one.

I've heard rumors that there were versions even before 97, but I've never
actually seen them, so I doubt that they really exist.
--
Darryl Kerkeslager