goto LAST record (on SUBform)

[piet123]

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.

 

[Dennisk]

place your code in the main forms On Current Event. That way it is triggered when you change records on the main form. As it is it is only run once when you open the form.

 

[unclejoe]

Hi,

Have you tried using this "DoCmd.RunCommand accmdRecordsGoToLAST" on each of the subform's current event?

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.

 

[piet123]

reply to Dennisk :
Thank you for the reply. That is what I thought I should do. I know I want to do what YOU are suggesting but do not know how to do the following:
On the MAINFORM's OnCurrent > DoCmd.RunCommand accmdRecordsGoToLast
but for EACH and every subform and still remain focus on the subform that the user is currently viewing.


reply to unclejoe:
Yes. But then the user can only view/edit the LAST record and cannot move to previous records for editing since the OnCurrent for the subform will always goto the Last record. So this is not an option. Thank you anyway.

Anyone else with the same thoughts as Dennisk ?

Thanks
Piet.

 

[Dennisk]

is there a particular reason to goto the last record on the subform. Is there a field that you can sort on so the last record is in the first row and the first record is on the last row.

 

[piet123]

is there a particular reason to goto the last record on the subform. Is there a field that you can sort on so the last record is in the first row and the first record is on the last row.

thanks dennis,
yes. the reason beiing: (fyi: each of the subforms contains ALOT of data)

the mainform holds the patient's name, address, etc.
subform1 is for (example) "observations"
subform2 is for (example) "visits"(appointments)
subform3 for xray examinations
etc. etc.

if the user looks at subform2(for example), he wants to see the last observation of the patient before he decides to add more records.

the important part is that he wants to be able to look at the LAST record on subform2(or 3 or whatever) and always see the last record. the problem is (i think) with the mainform because as soon as you move to another record(prev,next) then the SUBFORM still has the focus BUT GOES TO THE 1ST record (as it should, normally).

any ideas ?

thanks for the tip on sorting(maybe).
i'll play with that idea in a while.
i might be able to sort on some field for some of the subforms but not necessarily on all of them.

any ideas would be appreciated.

thanks for the replies anyway.
regards
piet.

 

[unclejoe]

Hi,

Did not considered this event, sorry.

Use the Tab OnClick Property. this should work.

reply to Dennisk :
snip
reply to unclejoe:
Yes. But then the user can only view/edit the LAST record and cannot move to previous records for editing since the OnCurrent for the subform will always goto the Last record. So this is not an option. Thank you anyway.
Piet.

 

[Dennisk]

I work in the Health service also and I either sort referrals so the latest is top of the list or if the subform is a single form then the latest shows. To do this either use a query or the sort property of the form.

 

[piet123]

let's try again

reply to unclejoe :
i already have docmd.runcommand accmdrecordsgotolast in the onclick event of the tab(of the tabcontrol). but it applies only for the first time you click the tab.
once the user is viewing SUBreport3 (ie. the sub is already open/hasfocus), and then moves to another record on the MAINform, the SUBfrm is STILL open/hasfocus and therefor the tab's onclick event no longer applies.

reply to dennis:
i have thought about it like that and my subfrms are sorted by date (descending) but the HCP(user) wants to see the following:

fyi: each subfrm has so much data that it MUST be displayed in singleform view.
on each subfrm i have an unbound txtbx (txtRecordNo). this txtbx displays the following: "Record 2 of 4" (for example) which it gets from the subfrm's oncurrent event having the following code :

Me.RecordsetClone.MoveLast
Me.txtRecordNo = " " & [CurrentRecord] & " of " & Me.RecordsetClone.RecordCount

now, when any subform is in view, and the doctor or sister (HCP) moves from patient to patient (mainform move record> prev,next) then the subfrm in view (opened/hasfocus) must show "Record 4 of 4" for each patient(mainfrm record)

because the hcp wants to see what was done to the patient on the previous occasion before starting a new record in the subform. and also needs to know how many records there are in the subfrm.

so, the sort order of the subfrm is not really applicable. it's the fact that they want to see record 4 of 4 in the subfrm when moving between records on the MAINfrm.

i hope you understand what i mean.

btw: please respect the fact that i cannot post the mdb here since it contains alot of medical-confidential info wrt HIV/AIDS & TB, and i work for a gevornment (military) health service where the whole HIV issue is very political on all...


thanks all.
piet.

 

[neileg]

I work in the Health service also and I either sort referrals so the latest is top of the list or if the subform is a single form then the latest shows. To do this either use a query or the sort property of the form.

You can do this and place a series of unbound controls at the top of the subform so that the user appears to be entering data at the top of the list. Use a 'Save' button to append this data to the subform table where it will appear at the top of the subform or as the first record in a single form. Keeps it nice and consistent without having to do loads of jiggery pokery!

 

[gemma-the-husky]

i would have thought displaying data in reverse order is the way to go.

if you need to add a new record i often find a continuous form can't display enough data (columns - particluarly long text info fields), so I would probably disallow additions in the continuous form, and instead popup up an entry form to get the new entry, then requery the continuous form when the popup closes

I tend to do this with command buttons, but a shortcut menu could look pretty elegant

 

[piet123]

keep focus on LAST sub record

thanks again for the replies.

sort order (of any field) is not really applicable here.
adding new records is not the problem as such.
continous forms- and datasheet views is not an option here.

the problem i'm having is to KEEP focus on the LAST record of the subforms(singlefrm-view) in focus while moving between records on the MAINFORM.

i quote myself below:

[snip]

fyi: each subfrm has so much data that it MUST be displayed in singleform view.
on each subfrm i have an unbound txtbx (txtRecordNo). this txtbx displays the following: "Record 2 of 4" (for example) which it gets from the subfrm's oncurrent event having the following code :

Me.RecordsetClone.MoveLast
Me.txtRecordNo = " " & [CurrentRecord] & " of " & Me.RecordsetClone.RecordCount

now, when any subform is in view, and the doctor or sister (HCP) moves from patient to patient (mainform move record> prev,next) then the subfrm in view (opened/hasfocus) must show "Record 4 of 4" for each patient(mainfrm record)

because the hcp wants to see what was done to the patient on the previous occasion before starting a new record in the subform. and also needs to know how many records there are in the subfrm.

so, the sort order of the subfrm is not really applicable. it's the fact that they want to see record 4 of 4 in the subfrm when moving between records on the MAINfrm.

[snip]

any more advice? anyone ? please.

piet.

 

[unclejoe]

Hi,

reply to unclejoe :
i already have docmd.runcommand accmdrecordsgotolast in the onclick event of the tab(of the tabcontrol). but it applies only for the first time you click the tab.
once the user is viewing SUBreport3 (ie. the sub is already open/hasfocus), and then moves to another record on the MAINform, the SUBfrm is STILL open/hasfocus and therefor the tab's onclick event no longer applies.
piet.

The Tab OnClick Event may or may not work. In my version of access the event didn’t even fire up.

You might want to try to use the Tab’s OnChange event. This one works for me. Here my code.

Private Sub TabCtl0_Change()

Select Case Me.TabCt10
Case 0
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 1
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 2
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 3
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 4
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
End Select

End Sub

Your Tab Control are in numbers, 0 is the first Tab and 1 is the second Tab…so on..

You need to set the focus first before using the “Docmd”. (Or use the Recordset to move last.)

You can use “Forms!YourFormNameHere.Setfocus” or “Me.YourFormNameHere.Setfocus”

Note: I did not suggest changing the Form’s RecordSource SQL syntax because your tables were unknown. In fact, it is possible to use the “Last()” in the SQL syntax. Example..

Select Col1,Col2, Last(DateOfLastVisit) as LastVisit,Co4…….From Table1 Order By LastVisit

However, it not possible for your cursor to move to the last record. I don't recall any SQL syntax that might help.(cont form?)

HTH

 

[unclejoe]

Hi,

[QOUTE=piet123;593361]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[/QOUTE]

reply to unclejoe :
Snip
piet.

I realize you also use the Next & Previous buttons without changing the Tabs.
You might also want to try out the same code i gave as in the Tab's OnChange Event. Or better, create a Subroutine or Function to move the cursor to the last record.

PS. Is the Next & Previous a custom buttons?

 

[piet123]

thank you

Hi,

[QOUTE=piet123;593361]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[/QOUTE]

I realize you also use the Next & Previous buttons without changing the Tabs.
You might also want to try out the same code i gave as in the Tab's OnChange Event. Or better, create a Subroutine or Function to move the cursor to the last record.

PS. Is the Next & Previous a custom buttons?

reply to unclejoe:
thank you for the reply.
sorry for the delay, had meetings all day !

yes, the MAINfrm's Nxt,Prev buttons are my own (docmd....gotonext, etc)

thanks for the code, i'll give it a go tonight.
will keep you posted.
piet.

 

[piet123]

still having issues with this

Hi,

The Tab OnClick Event may or may not work. In my version of access the event didn’t even fire up.

You might want to try to use the Tab’s OnChange event. This one works for me. Here my code.

Private Sub TabCtl0_Change()

Select Case Me.TabCt10
Case 0
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 1
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 2
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 3
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 4
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
End Select

End Sub

Your Tab Control are in numbers, 0 is the first Tab and 1 is the second Tab…so on..

You need to set the focus first before using the “Docmd”. (Or use the Recordset to move last.)

You can use “Forms!YourFormNameHere.Setfocus” or “Me.YourFormNameHere.Setfocus”

Note: I did not suggest changing the Form’s RecordSource SQL syntax because your tables were unknown. In fact, it is possible to use the “Last()” in the SQL syntax. Example..

Select Col1,Col2, Last(DateOfLastVisit) as LastVisit,Co4…….From Table1 Order By LastVisit

However, it not possible for your cursor to move to the last record. I don't recall any SQL syntax that might help.(cont form?)

HTH

reply to unclejoe:

thank you for the code(above).
i tried that but still nothing.
no error messages, and my forms still works perfectly, but
the subforms still goes to 'record 1 of 4' (for example).
(access2000 btw)

and i want (no, the USER wants) it to default to 'record 4 of 4' before he decides to add a new record or not.

so we're back to square 1 ?

anyone with thoughts on this ?

in the meantime i also decided to make it even more complicated (sorry) :

let's recap:

1. the MAINform has custom "next/previous' record btns. (docmd.r.....gotonext, etc)

2. in a tabctl i have 5 SUBfrms. (1 subfrm on every page of tabctl)
2a. each subfrm is in singleform view (datasheet and cont.frms is NOT an option), and sort order of whatever fields does not apply here.

3. the user is looking at (example) "SUBfrm3", so sub3 is open/hasfocus.
3a. now he moves between records (nxt/prev) on the MAINfrm, then "SUBfrm3" must also move to it's own LAST related record. (must! be LAST record)

now i want also the following:

the whole procedure above must work the same for all subfrms, except for (example) 'SUBfrm4" must default to NEW record, instead of LAST.

anyone with advice, code, help, anything ? please.

Thank you all.
Have a good week.
Piet.

 

[unclejoe]

Hi,

reply to unclejoe:

1. the MAINform has custom "next/previous' record btns. (docmd.r.....gotonext, etc)

Piet.

Have tried using the code on your Next & Previous buttons?

 

[Nmex Ron]

Move to the last (or next) record in a subform.

This one worked for me with only one subform:

Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast

Actually I modified it to go to the next record:

Private Sub Form_Current()
Me.YourFormNameHere.SetFocus
DoCmd.GoToRecord , , acNewRec
End Sub

I put the command in the "On Current" event code(...) of the Main form (not the subform).
Works like a charm -- (finally)!
Ron

 

[cmborges]

Re: still having issues with this

reply to unclejoe:

thank you for the code(above).
i tried that but still nothing.
no error messages, and my forms still works perfectly, but
the subforms still goes to 'record 1 of 4' (for example).
(access2000 btw)

and i want (no, the USER wants) it to default to 'record 4 of 4' before he decides to add a new record or not.

so we're back to square 1 ?

anyone with thoughts on this ?

in the meantime i also decided to make it even more complicated (sorry) :

let's recap:

1. the MAINform has custom "next/previous' record btns. (docmd.r.....gotonext, etc)

2. in a tabctl i have 5 SUBfrms. (1 subfrm on every page of tabctl)
2a. each subfrm is in singleform view (datasheet and cont.frms is NOT an option), and sort order of whatever fields does not apply here.

3. the user is looking at (example) "SUBfrm3", so sub3 is open/hasfocus.
3a. now he moves between records (nxt/prev) on the MAINfrm, then "SUBfrm3" must also move to it's own LAST related record. (must! be LAST record)

now i want also the following:

the whole procedure above must work the same for all subfrms, except for (example) 'SUBfrm4" must default to NEW record, instead of LAST.

anyone with advice, code, help, anything ? please.

Thank you all.
Have a good week.
Piet.

I have a similar problem to Piet. I tried UncleJoe's code, but the code only works when you click on a different tab. Otherwise the focus is on the first record. Anybody have any tips?

 

[cmborges]

The way I got it to work is to use:

Private Sub TabCtl0_Change()

Select Case Me.TabCt10
Case 0
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 1
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 2
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 3
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
Case 4
Me.YourFormNameHere.Setfocus
DoCmd.RunCommand accmdRecordsGoToLast
End Select

End Sub

But then repeat it again replacing
Private Sub TabCtl0_Change()
with
Private Sub Form_Current()

Now it will go to the last entry on the current tab (Private Sub Form_Current()) and go to the last entry whenever you change tabs (Private Sub TabCtl0_Change()). I hope that makes sense.