UtterAccess Discussion Forums > goto LAST record on SUBform

jamie007

May 24 2007, 01:31 PM

Hello, I have searched the forums for help and got several references to what i think i need but i still just can't make it work. I think it's to do with recordset clone :

On the MAINFORM is a Tabcontrol with 5 tabs.
Each tab(page) has a subform.
>Each subform's property is set to SINGLE FORM view with cmdButtons to scroll between records on the subform

All subforms are linked to MAINFORM with "pat_id" (PK, Autono.)

On the MAINFORM there are 2 cmdButtons for "Next Record" and "Previous Record"

NEED HELP with the following :

When moving between records (ie. currentrecord) on the MAINFORM, then ALL the subforms must always default to the LAST record for that subform. For example:

If the user is looking at "Subform 3" and clicks the "next or previous record" button on the MAINFORM , then "Subform 3" must always display the LAST related record.

What i have at the moment is > On each SUBform's OnOpen event :

DoCmd.RunCommand accmdRecordsGoToLAST

This is okay for the first opened record of the MAINFORM but ofcourse no longer applies when I move between records on the MAINFORM

Any advice please.

Piet.

fkegley

May 24 2007, 01:35 PM

I THINK this will do it:

In the click events of the Next and Previous buttons:

Dim rst As DAO.Recordset

Set rst = Me.NameOfSubformControl.Form.RecordsetClone
rst.MoveLast
Set rst = Nothing

etc. until all subform controls have been accounted for

Edited by: fkegley on Thu May 24 14:35:51 EDT 2007.

rsindle

May 24 2007, 01:56 PM

Assuming you have the Open event working properly from each subform you could also try:

Declare Each subform's Open event procedure to be "Public"

Call each from the main form's Current event.

Call Form_sfrmOne.Form_Open(False)
Call Form_sfrmTwo.Form_Open(False)

Etc.

datAdrenaline

May 24 2007, 02:15 PM

Couple of thoughts ...

- First ... ditch the OnOpen code in all your sub-forms. Sub-Forms load before the Main form, which can sometimes cause confusion when dealing with events.

Next ... consider sorting your sub-form data in the Opposite order that it is at now, so the "First" record is the "Last" one. For example, if you currently sort by SomeDate DESCending, change your sort to SomeDate ASCending.

... If that is not an option ...

In the MAIN FORMS OnCurrent event use the following code ...

CODE

Private Sub Form_Current()



    Dim aStrSubForm(1 To 3) As String  'Array to hold the sub form control names

    Dim x As Byte 'simple counter



    'Identify the sub form control names you wish to have the cursor go to the last record

    aStrSubForm(1) = "Sub Form 1"

    aStrSubForm(2) = "Sub Form 2"

    aStrSubForm(3) = "Sub Form 3"



    'Loop through all the identified sub form controls and go to the last record

    For x = LBound(aStrSubForm) To UBound(aStrSubForm)

        With Me.Controls(aStrSubForm(x)).Form.Recordset

            If Not (.EOF And .BOF) Then

                .MoveLast

            End If

        End With

    Next x



End Sub

Frank was close with the recordset clone and you can use that method, but then you have to set the bookmark of the form equal to the bookmark of the recordsetclone to actually perform the navigation.

(NOTE: 100% AIR CODE!!!!)

jamie007

May 25 2007, 12:33 PM

QUOTE

(snip)

In the MAIN FORMS OnCurrent event use the following code ...

CODE

Private Sub Form_Current()

    Dim aStrSubForm(1 To 3) As String  'Array to hold the sub form control names
    Dim x As Byte 'simple counter

    'Identify the sub form control names you wish to have the cursor go to the last record
    aStrSubForm(1) = "Sub Form 1"
    aStrSubForm(2) = "Sub Form 2"
    aStrSubForm(3) = "Sub Form 3"

    'Loop through all the identified sub form controls and go to the last record
    For x = LBound(aStrSubForm) To UBound(aStrSubForm)
        With Me.Controls(aStrSubForm(x)).Form.Recordset
            If Not (.EOF And .BOF) Then
                .MoveLast
            End If
        End With
    Next x

End Sub

(snip)

(NOTE: 100% AIR CODE!!!!)

Thank you very much ! That is exactly what i wanted, and it works perfect.

Forgive me for being 'greedy' but : Which part of the code(above) can i change, (or add to it) if :
only 1 of the SUBforms should "goto a NEW record", instead of the LAST,
but all the other subforms should still ".movelast" as in the code above.

Thanks again.
Regards,
Piet.

jamie007

May 27 2007, 12:19 PM

hi,

your code workd perfect as i said, but i still can't figure out how to adjust it so that only one of the SUBfrms must goto a NEW record instead of LAST as with the others.

other than that the code works perfect. thanks.

any help greatly appreciated.

piet.

jamie007

May 28 2007, 01:12 PM

i'm still having issues with this...

ther code works perfect for subforms to go to the LAST record, but i need something else in the code where i can specify that only one specific subform must goto a NEW record instead of LAST.

anyone ? please.

datAdrenaline

May 30 2007, 12:38 AM

Sorry for not responding ... I did not get e-mail notifications of post additions ... anyway if you have sub forms that you wish to go to a NEW RECORD, then set up code similar to the .MoveLast code above ... but instead of .MoveLast, use .AddNew ... here is how it would look for your ONE sub form ...

CODE

Private Sub Form_Current()



    Dim aStrSubForm(1 To 3) As String  'Array to hold the sub form control names

    Dim x As Byte 'simple counter



    'Identify the sub form control names you wish to have the cursor go to the last record

    aStrSubForm(1) = "Sub Form 1"

    aStrSubForm(2) = "Sub Form 2"

    aStrSubForm(3) = "Sub Form 3"



    'Loop through all the identified sub form controls and go to the last record

    For x = LBound(aStrSubForm) To UBound(aStrSubForm)

        With Me.Controls(aStrSubForm(x)).Form.Recordset

            If Not (.EOF And .BOF) Then

                .MoveLast

            End If

        End With

    Next x



    'Go to a new record for one sub form ..

    Me.Controls("SubFormName").Form.Recordset.AddNew



End Sub