#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    40
    Rep Power
    2

    Question Nested Subform Not Showing First Record


    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:

    Code:
        
    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.
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Try adding this at the end of you code.
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    40
    Rep Power
    2
    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"

    Code:
    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.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    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.
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    40
    Rep Power
    2
    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.
  10. #6
  11. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    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.
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    40
    Rep Power
    2
    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.

Similar Threads

  1. Subform not showing for particular user
    By slh in forum Microsoft Access Help
    Replies: 1
    Last Post: November 28th, 2011, 07:19 PM
  2. Showing a button next to each record in a subform
    By iwyll in forum Microsoft Access Help
    Replies: 2
    Last Post: September 28th, 2010, 02:36 PM
  3. Subform Info Not Showing in Form
    By Siena in forum Microsoft Access Help
    Replies: 2
    Last Post: June 19th, 2009, 09:16 AM
  4. Subform Showing information from Current Form
    By Solitaire in forum Microsoft Access Help
    Replies: 4
    Last Post: October 29th, 2006, 08:04 AM
  5. Subform not showing all records
    By Gisel in forum Microsoft Access Help
    Replies: 3
    Last Post: May 7th, 2004, 12:00 AM

IMN logo majestic logo threadwatch logo seochat tools logo