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]

Re: Subform Navigation

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" <rationalrepair s@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]

Re: Subform Navigation

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" <rationalrepair s@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]

Re: Subform Navigation

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]

Re: Subform Navigation

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]

Re: Subform Navigation

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]

Re: Subform Navigation

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]

Re: Subform Navigation

"Darryl Kerkeslager" <kerkeslager@co mcast.netwrote in
news:Ic6dnZ-9dNqvyMPYnZ2dnU VZ_rOdnZ2d@comc ast.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]

Re: Subform Navigation

"Rational Repairs" <rationalrepair s@gmail.comwrot e in
news:1163825389 .631250.131760@ 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]

Re: Subform Navigation

"David W. Fenton" <XXXusenet@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

[Rational Repairs]

Re: Subform Navigation

Seems to me that nobody but novices uses DoCmd.GoToRecor d for plain

old record navigation, whether in parent forms or child forms.

I guess that would explain why I was trying to use it.

Thanks for the tips fellas. I ended up recreating my code without using
DoCmd.GoToRecor d. Seems to work much better this way. One other
question: Recordset versus RecordsetClone with Bookmarks? I used
Recordset here so that my forms update without a requery when I add or
delete a record. Is this the correct method?

Thanks again.

I've posted my global navigation code below:

Function fcnNavigation(f rmForm As Form, Optional strFunction As String,
_
Optional strFindString As String)

Dim rs As Object
Set rs = frmForm.Records et

Select Case strFunction
Case "Open"
rs.MoveLast
rs.MoveFirst
frmForm.cmbQuic kFind.RowSource = frmForm.RecordS ource
Case "First"
rs.MoveFirst
Case "Next"
rs.MoveNext
Case "Prev"
rs.MovePrevious
Case "Last"
rs.MoveLast
Case "New"
rs.AddNew
rs.Update
frmForm.cmbQuic kFind.RowSource = frmForm.RecordS ource
Case "Delete"
rs.Delete
rs.MoveFirst
frmForm.cmbQuic kFind.RowSource = frmForm.RecordS ource
Case "QuickFind"
rs.FindFirst strFindString
Case Else
frmForm.cmbQuic kFind.RowSource = frmForm.RecordS ource
End Select


Select Case rs.RecordCount
Case Is < 2 'either a new record, or one loaded
frmForm.cmdFirs tRecord.Enabled = False
frmForm.cmdPrev iousRecord.Enab led = False
frmForm.cmdNext Record.Enabled = False
frmForm.cmdLast Record.Enabled = False
frmForm.cmbQuic kFind.Enabled = False
Case Else 'more than 1 loaded
If frmForm.cmbQuic kFind.Enabled = False Then
frmForm.cmbQuic kFind.Enabled = True
End If
If frmForm.Current Record = rs.RecordCount Then 'sittin on the last
record
frmForm.cmdFirs tRecord.Enabled = True
frmForm.cmdPrev iousRecord.Enab led = True
frmForm.cmdPrev iousRecord.SetF ocus
frmForm.cmdNext Record.Enabled = False
frmForm.cmdLast Record.Enabled = False
End If
If frmForm.Current Record < rs.RecordCount Then 'not on last record
frmForm.cmdNext Record.Enabled = True
frmForm.cmdLast Record.Enabled = True
If frmForm.Current Record <1 Then 'not on first record
frmForm.cmdFirs tRecord.Enabled = True
frmForm.cmdPrev iousRecord.Enab led = True
Else 'sittin on the first record
frmForm.cmdNext Record.SetFocus
frmForm.cmdFirs tRecord.Enabled = False
frmForm.cmdPrev iousRecord.Enab led = False
End If
End If
End Select

frmForm.lblNavI nfo.Caption = frmForm.Current Record & " of " &
rs.RecordCount

Set rs = Nothing
End Function

[Lyle Fairfield]

Re: Subform Navigation

Darryl Kerkeslager wrote:

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.

David had a whole pile of stone tablets with Access 97 code examples in
his back yard.

[David W. Fenton]

Re: Subform Navigation

"Darryl Kerkeslager" <kerkeslager@co mcast.netwrote in
news:KMmdnfsB8b MH4_3YnZ2dnUVZ_ qCdnZ2d@comcast .com:

"David W. Fenton" <XXXusenet@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.

Actually, your code makes no sense. You've created a copy of a
recordset when there is already a copy existing that could do all
the same things.

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.

You're not even using the best methods for versions after 2000.

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

[David W. Fenton]

Re: Subform Navigation

"Rational Repairs" <rationalrepair s@gmail.comwrot e in
news:1163956973 .529015.285780@ k70g2000cwa.goo glegroups.com:

One other
question: Recordset versus RecordsetClone with Bookmarks?

I would use the RecordsetClone and bookmarks, as that's the
preferred method for navigation. Secondly, you can do things in the
RecordsetClone that won't have an effect on the form (i.e., you can
try moving somewhere and then not set the form's bookmark).

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

[Darryl Kerkeslager]

Re: Subform Navigation

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

You're not even using the best methods for versions after 2000.

While your point is well-taken about the need to create another recordset
(and this is old code and I can't recall right now why I might have done it
one way or another), there really is no difference that is going to have any
effect on an Access 2000 mdb. I will accept a millisecond performance
degradation or extra temporary use of 64 bytes of memory without much worry.
As to backwards compatibility with Access 97, this app was written for a
department who upgrades all PCs at the same time; all were using Access 2000
at the time, and now use XP. Backwards compatibility is a non-issue.

The first rule of writing, be it code or prose, is to write for your
audience. And, no, I didn't re-write it for the ng post, I just
cut-and-posted.



--
Darryl Kerkeslager