Go to last record when selecting different pages on tab control subform

[sandi]

Hello group mates:
I've searched the groups but have only come up with half the answer.
Please forgive the stupid question.
I have a form with 2 pages in a tab control.
What I want to do is simple, when I hit page one, I would like to go to
the last record, same for page 2. Page 1 is in form format and 2 is in
datasheet format.

I have this code in my main form:

Private Sub Form_Current()
Me.cboLastName = CaseNumber 'synchronize txtboxes
On Error Resume Next
Dim frm As Form
Set frm = Me.SubVisits.Form 'this is Page 1
With frm
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
End Sub

This works great for the first page...
Should I use a select statement or dim another form for my second
subform?

Many thanks ahead of time!
Sandi

 

[Sandra Daigle]

I would move this code to a procedure that is called from the Current Event
of the form and the Change event of the Tab Control. In this procedure, you
can test the value of the Tab Control to determine which page is visible and
then execute the appropriate navigation code from there.

You don't need the object variable for the subform - just reference it
directly in the With Clause.

Note that on each of the cases below instead of using absolute page numbers
I get the page number by getting the pageindex of a named page. I do this
because the page index of a page changes if you reorder the pages but the
names don't change unless you change them. This just reduces maintenance and
prevents weird errors when you reorder the pages but forget to adjust the
code.

Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
With Me.SubVisits.Form
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
With Me.SubOtherSubform.Form
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
End Select

Select Case me.MyTabCtl

 

[sandi]

I would move this code to a procedure that is called from the Current Event
of the form and the Change event of the Tab Control. In this procedure, you
can test the value of the Tab Control to determine which page is visible and
then execute the appropriate navigation code from there.

You don't need the object variable for the subform - just reference it
directly in the With Clause.

Note that on each of the cases below instead of using absolute page numbers
I get the page number by getting the pageindex of a named page. I do this
because the page index of a page changes if you reorder the pages but the
names don't change unless you change them. This just reduces maintenance and
prevents weird errors when you reorder the pages but forget to adjust the
code.

Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
With Me.SubVisits.Form
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
With Me.SubOtherSubform.Form
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
End Select

Select Case me.MyTabCtl

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hello group mates:
I've searched the groups but have only come up with half the answer.
Please forgive the stupid question.
I have a form with 2 pages in a tab control.
What I want to do is simple, when I hit page one, I would like to go
to the last record, same for page 2. Page 1 is in form format and 2
is in datasheet format.

I have this code in my main form:

Private Sub Form_Current()
Me.cboLastName = CaseNumber 'synchronize txtboxes
On Error Resume Next
Dim frm As Form
Set frm = Me.SubVisits.Form 'this is Page 1
With frm
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
End Sub

This works great for the first page...
Should I use a select statement or dim another form for my second
subform?

Many thanks ahead of time!
Sandi

I will try that straight away! Thanks!

 

[sandi]

Hi Sandra Daigle:

I tried your suggestion, substituting in the names of my tab and
subforms but I am getting an 2467 run time error stating that my
expression refers to an object that is closed or does not exist.

I've tried the code on both the Current event of the main form and the
Change event of my tab control.

This is what my code looks like:

Private Sub TabCtl3_Change()
Select Case mIntCurTabPage
Case Me.TabCtl3.Pages("Medical_Records").PageIndex
With Me.SubVisits.Form
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
Case Me.TabCtl3.Pages("Bills_Compilation").PageIndex
With Me.subBillsComp.Form
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
End With
End Select
End Sub

Have I typed it wrong?
Many thanks for your help!
Sandi

 

[Sandra Daigle]

Errrr . . . no, I typed it wrong - or rather didn't finish cleaning up the
code that I copied to help answer your question. The error is referring to
"mIntCurTabPage" which doesn't exist in your code. Change the first line of
the Select to this:

Select Case me.TabCtl3\

My apologies for the mistake!

 

[Sandra Daigle]

Oh heck - it should be this:

Select Case me.TabCtl3

Don't know how the extra character got tacked on to the end!

 

[sandi]

As you can tell, I don't know nothing about coding, so your help is
greatly appreciated, even with typos.

I will try these changes.
Do I need to do any kind of dim statments?

(I'm not currently at the computer that has the database on it right
now so I can't test the code out yet.)

 

[Sandra Daigle]

No dims required. Let me know if you need any additional help. Good luck!