Nested Subform Not Showing First Record

[Vaulcul]

Hello,

I have form in datasheet view that has multiple nested subforms in it and I'm having the odd issue that they work and show the data they need to show... except not on the first record... and only when not viewing last subform in its immediate parent.

My layout:

Main Parent Form is called Emp which has a child subform called EmpPos
EmpPos has a child subform called EmpPosInf
and EmpPosInf has a child subform called EmpPosInfFall

EmpPosInf is set to programatically select a form to bind to the control (named PPLoader) on loading depending on what is selected from a list provided on a main form.

Please note: I currently have the code working... it binds the correct form. I also have the correct fields bound as the information is updateable/writeable.

When opening the form EmpPosInf, all data is shown as needed. But, as soon as I open EmpPosInf as a child form the first record seems to disappear in all cases.

Below is the code that I have executing on load of EmpPosInf:

    
Private Sub Form_Load()
    On Error Resume Next

    Dim strSubformName As String

    If Forms![Test Opener]!PPList = "Fall" Then
        strSubformName = "Emp Pos Inf Fall"
    ElseIf Forms![Test Opener]!PPList = "Fall/Spring" Then
        strSubformName = ""
    ElseIf Forms![Test Opener]!PPList = "Spring" Then
        strSubformName = "[Emp Pos Inf Spring]"
    ElseIf Forms![Test Opener]!PPList = "Summer" Then
        strSubformName = "[Emp Pos Inf Summer]"
    ElseIf Forms![Test Opener]!PPList = "All" Then
        strSubformName = ""
    End If

    Me.PP_Loader.SourceObject = strSubformName
    Me.PP_Loader.LinkMasterFields = "Emp_Inf_ID"
    Me.PP_Loader.LinkChildFields = "Emp_Inf_ID"
    Me.PP_Loader.Visible = True
End Sub

Thanks in advance for your help.

[meratigoerr]

Try adding this at the end of you code.

Me.(strsubformname).form.setfocus
Docmd.runcommand accmdrecordsgotofirst

I don't remember the exact syntax for using a variable subform name so you might want to double check that.

[Vaulcul]

Thanks for the reply meratigoerr

I had to change your code a bit so that it didn't error out... by replacing the brackets. So the code looks as follows"

Me.[strSubformName].form.setfocus
Docmd.runcommand accmdrecordsgotofirst

However, it still skips the first record

I'm wondering if the level of subform I've put the code at might have anything to do with the first record missing... as I stated in my OP that I don't have the problem when the subform is only a single level deep... only when I start adding more forms on top of it does it have an issue showing the first record.

I just don't know what the path to point to something that deep would be from the main form.

[meratigoerr]

Am I correct in assuming that you've already tried to set the order by property of the subform and that didn't work?

If you are running the new code on the open of EmpPosInf than that should work.
Are [Emp Pos Inf Fall], [Emp Pos Inf Spring], [Emp Pos Inf Summer] all queries? Do you have an ascending or Descending order set on your quieries?

Does the actual Name of the subform change, or are you just changing the record source of your subform? Try

me.[PP Loader].form.setfocus

$.02 - Avoid using spaces in your field, form, and object names. It overly complicates things and creates the potential for silly errors due to bracketing.

[Vaulcul]

I'm not sure how the "order by" property will help with the subform... most of the ordering of data is done by the subforms on your way up (or down however you look at it) through the subforms. Maybe an explination of why this would help would be helpful to me.

I switched the area of where the code executes from the "on load" to the "on open" area... nothing changed.

Are [Emp Pos Inf Fall], [Emp Pos Inf Spring], [Emp Pos Inf Summer] all queries?

No, these are all subforms fed by their individual queries (so the name of the subform is changing in the control) [Emp Pos Inf Fall] indicates pay periods 1-10, [Emp Pos Inf Spring], indicates pay periods 11-20 and [Emp Pos Inf Summer] indicates pay periods 21-26. That's pretty much the only information in the forms (other than the linking fields).

If it helps, in a nut shell heres the data that's in each form: [Emp] has only the Employee ID and their name (though it does contain the position ID to link it to the subform, [Emp Pos] Has the position ID and Title, and linking field for the next subform, [Emp Pos Inf] Has how many hours, start and end date, supervisor and linking information...

I tried the "me.[PP Loader].form.setfocus" and this didn't seem to work either...

Your $.02 is greatly appreciated and I must admit I had a feeling I should be doing that... I'm going through and making sure this is the case from now on.

[meratigoerr]

Can you post a stripped down version of your database to a file sharing site, (Such as Box.com) Remove any confidential or sensitive infomation and compact and repair it before upload.

I think getting at look at what you're working on will help.

[Vaulcul]

It'll take me a bit... things have gotten really busy and I've had to put the DB on the back burner...
Thanks for the willingness to help.