moving through subforms while moving through main form

[Troopa]

Hi all,

I have three tables/forms one main form and two subforms.

When I open the main form, the two subforms go to the last record but when I navigate through the records in the main form, the two subforms default to the first record for each record on the main form.

Do you know how I can navigate through the records in the main form and when doing so, the two subforms automatically move to the last records?

Thnaks in advance

 

[ProgramError]

Use this in one of the subform events

DoCmd.GoToRecord , , aclast

don't forget to refresh

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[Troopa]

thanks Ian, do you know what even it will work on? I have tried adding a macro to do the same thing but it doesn't seem to work when navigating the main form/records only when the main form is loaded/opened?

 

[ProgramError]

Hi Troopa,

Is the subform based on a query? If so, does the query have a particular field sort order?
What do you use for navigation (programmed buttons or the navigation bar)?

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[TheAceMan1]

How are ya Troopa . . .

In the [blue]OnCurrent[/blue] event of the MainForm, copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim sfrm1 As Form, sfrm2 As Form
   
   Set sfrm1 = [[purple][b]subFormname1[/b][/purple]].Form
   Set sfrm2 = [[purple][b]subFormname2[/b][/purple]].Form
   
   [green]'SubForm1[/green]
   [[purple][b]subFormname1[/b][/purple]].SetFocus
   
   If Not sfrm1.NewRecord Then
      DoCmd.RunCommand acCmdRecordsGoToLast
   End If

   [green]'subForm2[/green]
   [[purple][b]subFormname2[/b][/purple]].SetFocus
   
   If Not sfrm2.NewRecord Then
      DoCmd.RunCommand acCmdRecordsGoToLast
   End If
   
   Forms![purple][b]MainFormName[/b][/purple].SetFocus [green]'set focus back to MainForm[/green]
   
   Set sfrm1 = Nothing
   Set sfrm2 = Nothing[/blue]

Don't forget to disable/delete the other code to prevent interaction! . . .

See Ya! . . . . . .

 

[Troopa]

nice one AceMan, I'll give that a go in the morning
Thanks

 

[TheAceMan1]

Troopa . . .

I noticed an error in the code (focus has to be set back to the mainform before setting focus to subform2). The modified could would be:

[blue]   Dim frm As Form, sfrm1 As Form, sfrm2 As Form
   
   Set frm = Forms![[purple][b]MainFormName[/b][/purple]]
   Set sfrm1 = [[purple][b]subFormName1[/b][/purple]].Form
   Set sfrm2 = [[purple][b]subFormName2[/b][/purple]].Form
   
   [green]'SubForm1[/green]
   [[purple][b]subFormName1[/b][/purple]].SetFocus
   
   If Not sfrm1.NewRecord Then
      DoCmd.RunCommand acCmdRecordsGoToLast
   End If
   
   frm.SetFocus [green]'set focus back to MainForm[/green]

   [green]'subForm2[/green]
   [[purple][b]subFormName2[/b][/purple]].SetFocus
   
   If Not sfrm2.NewRecord Then
      DoCmd.RunCommand acCmdRecordsGoToLast
   End If
   
   frm.SetFocus [green]'set focus back to MainForm[/green]
   
   Set sfrm1 = Nothing
   Set sfrm2 = Nothing
   Set frm = Nothing[/blue]

See Ya! . . . . . .

 

[Troopa]

Thanks AceMan, not sure if I've got it right though because I get a debug error. This is the code in full:

Private Sub Form_Current()
   Dim frm As Form, sfrm1 As Form, sfrm2 As Form
   
   Set frm = Forms![frmProcMaster]
   Set sfrm1 = [frmReview].Form
   Set sfrm2 = [frmVersionControl].Form
   
   'SubForm1
   [frmReview].SetFocus
   
   If Not sfrm1.NewRecord Then
      DoCmd.RunCommand acCmdRecordsGoToLast
   End If
   
   frm.SetFocus 'set focus back to MainForm

   'subForm2
   [frmVersionControl].SetFocus
   
   If Not sfrm2.NewRecord Then
      DoCmd.RunCommand acCmdRecordsGoToLast
   End If
   
   frm.SetFocus 'set focus back to MainForm
   
   Set sfrm1 = Nothing
   Set sfrm2 = Nothing
   Set frm = Nothing
End Sub

The error relates to:

Set sfrm2 = [frmVersionControl].Form

error:

Run-time error '-2146500594 (800f00e)':

Method 'Form' of object '_Subform' failed

 

[TheAceMan1]

Troopa . . .

Check the [blue]Name[/blue] property of frmVersionControl . . .

See Ya! . . . . . .

 

[Troopa]

AceMan,

I've checked the name and copy&pasted it from the actual form name (frmVersionControl) into the VB code you provided but still the same prolem?

Is there anything else I can check/do?

nice1

 

[TheAceMan1]

Troopa . . .

Many apologies! This thread got burried in my e-mail. I've moved and things piled up quite a bit. Just recently on line at home again.

To pick up where you left off, delete the previous code I gave you and try the following in the OnCurrent event of the mainform (no worry about focus). . .

[blue]   Dim frm As Form, n As Integer
   
   For n = 1 To 2
      Set frm = Choose(n, [[purple][b]subFormName1[/b][/purple]], [[purple][b]subFormName2[/b][/purple]]).Form
   
      With frm.Recordset
         If .RecordCount > 0 Then .MoveLast
      End With
      
      Set frm = Nothing
   Next[/blue]

[blue]When I open the main form, [purple]the two subforms go to the last record[/purple] . . . [/blue]

Apparently you have some other code that sets the subforms to the last record on open. This code can be removed as the just given code will suffice when the form is opened as well.

Cheers! . . .

See Ya! . . . . . .