How do I get a form action send its subform to last record

[shacket]

I have a subform on a main form. When the main form changes records (OnCurrent), I want the subform to move to the last record. How do I do that?

Dave

 

[BillNorton]

Try something like this in your OnCurrent event:

DoCmd.GoToRecord acDataForm, "Employees", acLast

Bill Norton
Austin, TX

 

[shacket]

Bill,

thanks for being my person Access assistant!

I tried what you suggested. For the name of the form, I tried the name of the control, the name of the subform placed in the control, and Forms!MainFormName!SubFormName and none of them worked. It kept telling me that the form (subform) was not open.

Any other ideas?

 

[BillNorton]

Yeah, I've had trouble getting this right myself. My understanding is that the ObjectName refers to the name of the form itself not the name of the control for which the form is the SourceObject (but I could be wrong).

If all else fails you might try putting the code inside the subform itself as a Public Sub:

Public Sub sGoToLast()
DoCmd.GoToRecord , , acLast
End Sub

Let me know if that works.

Bill Norton
Austin, TX

 

[charityg]

I don't if this will work, but it's worth a try.

dim rst as recordset

set rst=me!subformname.recordsetclone
rst.movelast

me!subformname.bookmark=rst.bookmark

I'm not certain how access will react to setting subform bookmarks. Let me know if it works.

 

[BillNorton]

OK, I did a little research and found this:
http://support.microsoft.com/support/kb/articles/Q92/6/85.ASP?LN=EN-US&SD=gn&FR=0&qry=GoToRecord%20subform&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

This is VERY misleading. What you need to do is something like this:

DoCmd.GoToControl "SubFormControl"
DoCmd.GoToRecord , , acLast

Bill Norton
Austin, TX

 

[shacket]

OK, I tried all of your suggestions and they didn't work. The only way I can get it to happen is to put the GoToRecord command in the OnOpen event of the subform, and reassign that subform to the SourceObject of the control on my main form in the OnCurrent event of the main form. It hiccups the application, but it works.

Dave

 

[BillNorton]

Hmmm... the

DoCmd.GoToControl "subfrmSecondaryVictims"
DoCmd.GoToRecord , , acNext

works for me. Make sure in your case to replace "subfrmSecondaryVictims" with whatever is the name of your subform control not the name of the control's SourceObject.

Bill Norton
Austin, TX

Bill Norton

 

[pcs]

this may be too "quick and dirty" depending on what your data subform needs to display.

went round & round w/this in Acc 2.0 some years ago. my subform displayed order details for the main entry form. i changed the the subform query to sort Descending on the Autonumber field, then requeryed the subform for each new record entered. it worked fine for the client as the last detail entered was always on top, but your mileage may vary

al

 

[JCarmic]

You can manipulate the sub-from dataset directly
ie

dim frm as form, rst as recordset

set frm = me!SubControl.form
with rst
if .recordcount > 0 then
.movelast
frm.bookmark = rst.bookmark
end if
end with

just another way of doing things

John