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]![ctrlMySubFormObject].Form
Forms![frmMyParentForm]![ctrlMySubFormObject].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(currentRecordId 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.Clone
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(lst.ListCount - 1)
Else
If IsNull(lst.Value) 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.Value) 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.comwrote

>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(currentRecordId 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.Clone
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(lst.ListCount - 1)
Else
If IsNull(lst.Value) 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.Value) 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.comwrote

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("TheSubform").txtString

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_OrderDetails
points to the form, whether or not it's being used as a subform
and
Form_OrderDetails.Property
Form_OrderDetails.Function
Form_OrderDetails.Sub
Form_OrderDetails.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.GoToRecord 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.Recordset.RecordCount

Works like a champ.

I did find that this, instead, will work to change the record:
DoCmd.GoToRecord acActiveDataObject, , 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("TheSubform").txtString

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!boxPeskySubForm.Form.txtID

or using the forms collection

Forms("MainForm").boxPeskySubForm.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*********@comcast.netwrote in
news:Ic******************************@comcast.com:

Pass the Me reference to the function/sub.

Public Sub MoveToNext(currentRecordId 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.Clone

Er, why not frm.RecordsetClone? That will work in all versions of
Access, where frm.Recordset.Clone 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.comwrote in
news:11**********************@f16g2000cwb.googlegr oups.com:

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

Dim frmForm as Form
Set frmForm = 'form being passed to function
DoCmd.GoToRecord 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.GoToRecord 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*******@dfenton.com.invalidwrote

Er, why not frm.RecordsetClone? That will work in all versions of
Access, where frm.Recordset.Clone 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]

Seems to me that nobody but novices uses DoCmd.GoToRecord 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.GoToRecord. 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(frmForm As Form, Optional strFunction As String,
_
Optional strFindString As String)

Dim rs As Object
Set rs = frmForm.Recordset

Select Case strFunction
Case "Open"
rs.MoveLast
rs.MoveFirst
frmForm.cmbQuickFind.RowSource = frmForm.RecordSource
Case "First"
rs.MoveFirst
Case "Next"
rs.MoveNext
Case "Prev"
rs.MovePrevious
Case "Last"
rs.MoveLast
Case "New"
rs.AddNew
rs.Update
frmForm.cmbQuickFind.RowSource = frmForm.RecordSource
Case "Delete"
rs.Delete
rs.MoveFirst
frmForm.cmbQuickFind.RowSource = frmForm.RecordSource
Case "QuickFind"
rs.FindFirst strFindString
Case Else
frmForm.cmbQuickFind.RowSource = frmForm.RecordSource
End Select
Select Case rs.RecordCount
Case Is < 2 'either a new record, or one loaded
frmForm.cmdFirstRecord.Enabled = False
frmForm.cmdPreviousRecord.Enabled = False
frmForm.cmdNextRecord.Enabled = False
frmForm.cmdLastRecord.Enabled = False
frmForm.cmbQuickFind.Enabled = False
Case Else 'more than 1 loaded
If frmForm.cmbQuickFind.Enabled = False Then
frmForm.cmbQuickFind.Enabled = True
End If
If frmForm.CurrentRecord = rs.RecordCount Then 'sittin on the last
record
frmForm.cmdFirstRecord.Enabled = True
frmForm.cmdPreviousRecord.Enabled = True
frmForm.cmdPreviousRecord.SetFocus
frmForm.cmdNextRecord.Enabled = False
frmForm.cmdLastRecord.Enabled = False
End If
If frmForm.CurrentRecord < rs.RecordCount Then 'not on last record
frmForm.cmdNextRecord.Enabled = True
frmForm.cmdLastRecord.Enabled = True
If frmForm.CurrentRecord <1 Then 'not on first record
frmForm.cmdFirstRecord.Enabled = True
frmForm.cmdPreviousRecord.Enabled = True
Else 'sittin on the first record
frmForm.cmdNextRecord.SetFocus
frmForm.cmdFirstRecord.Enabled = False
frmForm.cmdPreviousRecord.Enabled = False
End If
End If
End Select

frmForm.lblNavInfo.Caption = frmForm.CurrentRecord & " of " &
rs.RecordCount

Set rs = Nothing
End Function

[Lyle Fairfield]

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]

"Darryl Kerkeslager" <ke*********@comcast.netwrote in
news:KM******************************@comcast.com:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote

>Er, why not frm.RecordsetClone? That will work in all versions of
Access, where frm.Recordset.Clone 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]

"Rational Repairs" <ra*************@gmail.comwrote in
news:11**********************@k70g2000cwa.googlegr oups.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]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote

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