Set subform to last record

[Big Pat]

Hi,

I have a fairly simple form and subform arrangement and the subform could contain hundreds of records for each main record.

When I open the form I want the subform, which is in continuous view, to automatically scroll to the bottom record which will be the most recently entered. How do I code that?

I think it's something like DoCmd.GotoRecord,, acMoveLast,

Does this go on the OnOpen event of the main form? Or maybe the OnActivate event of the subform (if that's activated automatically?) What's the right syntax and where does it go? I don't want to move records on the main form, just scroll to the last one on the subform.

Thanks

 

[vbaInet]

Me.SubformName.Recordset.MoveLast

... placed in the Load event of the Main form

Or

Forms!FormName!SubformName.Recordset.MoveLast
... placed just below the line of code that opens the form.

 

[Big Pat]

I tried the first option but got a compile error. Looks like it doesn't like ".Recordset". See attached jpeg.

I don't think it's worth trying the second approach, as it uses the same thing?

Thanks

 

[vbaInet]

Put .Form after SubformName. or before .Recordset

 

[Big Pat]

Ok...no error message this time....but now it simply doesn't work. The subform stays on the first record rather than going to the last one.

The foillowing events also fire from this main form. Could any of these be interfering?

Private Sub Form_Current()
'Display a big warning to show that archived trials
' are not counted in main reports
If Me.Archive.Value = True Then
    Me.Lbl_Archive.Visible = True
Else
    Me.Lbl_Archive.Visible = False
End If
'End Sub

Private Sub Form_Activate()
'Values may have changed on the investigators screens, so requery the following
Me.TotalTargetLookup.Requery
Me.TotalProRataTargetsLookup.Requery
Me.TotalRecruitedLookup.Requery
Me.RAG_ProRata.Requery
Me.RAG_Total.Requery
End Sub

Private Sub Form_GotFocus()
'Values may have changed on the investigators screens, so requery the following
Me.TotalTargetLookup.Requery
Me.TotalProRataTargetsLookup.Requery
Me.TotalRecruitedLookup.Requery
Me.RAG_ProRata.Requery
Me.RAG_Total.Requery
End Sub

Not sure whether I really need that last one in two places. It was a bit of trial and error!

Thanks,

 

[vbaInet]

Oh my, all that requerying? What for?

It doesn't work meaning? Did you try both solutions?

 

[Big Pat]

I didn't know that requerying was looked on as bad practice, if that's what you're driving at, but Access is something I've just picked up as I've gone along, with loads of help from the good people on this forum and I'm sure I have some awful habits. In this case, there's another subform which shows a filtered view of records and the user can click to go to another screen. That other screen has various calculated controls and when data is updated I want an updated summary, across all subrecords, back on the screen I came from. No doubt there's a far more elegant way, but requerying did the trick. Like I said it was trial and error and I can't remember now whether it was the Activate or the GotFocus event that worked. I will get round to commenting one out to see if the other works and vice versa.

Right...back to this. I tried your second suggestion by adding the line of code to the button on the menu form that opens the main form in the first place. It compiled Ok, but when I click it now it says "Object doesn't support this property or method".

Anyway, it's late on a Friday so I'm knocking off. Thanks for your help so far and I'll pick up any other suggestions on Monday. Or over the weekend if I get really bored

 

[missinglinq]

Interesting problem. I suppose you could simply set the Subform to Sort in Descending Order, which would put the Last Record at the top of the Subform, but using this code in the Code Module of the Main Form works:

Private Sub Form_Current()
  SubFormControlName.SetFocus
End Sub

Private Sub SubFormControlName _Enter()
  DoCmd.GoToRecord , , acLast
End Sub

Linq ;0)>

 

[Big Pat]

Thanks Linq

I wasn't sure whether your suggestion was to put two events on one form or if the second one was for the subform, but it gave me enough to go on.

This is what I came up with..It's on the OnOpen event of the main form and frm06_Reminders is the subform. I put a transparent button on the main form and set the focus back to it afterwards

Private Sub Form_Open(Cancel As Integer)
Me.frm06_Reminders.SetFocus
DoCmd.GoToRecord , , acLast
Me.focusdummy.SetFocus
End Sub

This works perfectly and it's pretty simple when you finally get it right.

Thanks so much for the help!

Pat

 

[vbaInet]

Now I'm pretty sure the code I gave you before would have worked too and you wouldn't need the other bits of code.

Can you upload a sample database?

 

[Big Pat]

Hi,

I'm not doubting your code! I did try both versions and your subsequent suggestions but they resulted in error messages as reported above. I fully expect these are due to other problems in my database and not your code.

On another form I had an error message about DAO-something (which gives you an idea of my level of expertise!!) and when I googled it there was a suggestion about Tools > References. But that option is greyed out on my PC which is in a large hospital. I've logged a call with our IT team about this and I suspect that *may* have somethnig to do with the Recordset message I was getting.

No idea about the "Object doesn't support this property or method" message. I've never really figured out why I get that sometimes.

I'm happy with the way this database is working now. I know it must make you proper IT types shudder when you see guys like me bodging things!

And I really do appreciate the help.

Pat.

 

[vbaInet]

Alright, so when you get an error message tell us what the error message is and we'll sort it out.

Good luck with the rest of your project.

 

[missinglinq]

A lot of corporate IT departments don't allow anyone but themselves to manipulate Reference Libraries. Sadly, they frequently think they understand Access and very seldom do! If they get snarly about loading the Libraries you need, tell them the next time JCAHO comes around you'll tell the inspectors that their network security is so pitiful that a 12 year old could crack it! The letters JCAHO will probably scare them into complying!

Linq ;0)>

 

[MStef]

In subform, Sub Form_Load
DoCmd.GoToRecord,,acLast

 

[missinglinq]

In subform, Sub Form_Load
DoCmd.GoToRecord,,acLast

Won't do! Any code placed in the Form_Load event will only execute when the Form is first Loaded, which is when (actually before) the Main Form loads.

When the Main Form is loaded, the Subform will move to the last Record, but not when moving to subsequent Records on the Main Form.

Linq ;0)>

 

[MStef]

Hello missinglinq !!
In the first theread Big Pat said.
"When I open the form...".
If he want to move to subsequent records on the MainForm,
then he have to enjoy herself with VBA. (he have to know what he want to do).

 

[missinglinq]

What could possibly be the point of only doing this for the first Record in the Main Form, but not in subsequent Records in the Main Form?