Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Hiding a subform if no records - Access 2007

    Hi - I have a main form ACCOUNTS with a subform REGISTRATION that is a 1-t-many relationship on ACCOUNTNAME.

    Both m/f and s/f are locked - used to view information, then close.

    M/f is single record, and s/f is datasheet view. s/f is required information, so every record in the m/f has at least one record in the s/f.

    I would like to add another subform to show some optional information, which will only apply to about an 1/8 - 1/4 of the m/f records. this 2nd s/f is related to the m/f (also) by ACCOUNTNAME.

    I have created and added this 2s/f in datasheet view with locked fields, no additions - and it displays fine....some records have data there but most don't.

    To attempt the m/f to hide 2s/f - I tried alot of variations in m/f on current - If, With, True, Recordcount=0 and have been riddled with errors, 2645 424....Access 07 just dosn't understand what I am trying to achieve. Mostly not finding my subform no matter how it is bracketed or shown with Me., Me!, [Forms]!

    I am wondering if there is a simple, clean way to code the 2s/f to be hidden if there are no records?? (Am also thinking of cancelling form and creating a report preview instead, which would change the syntax all together )
    Last edited by Foskbou; 05-10-11 at 15:10.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From Dev Ashish & Arvin Meyer site The Access Web (http://access.mvps.org/access/forms/frm0029.htm):
    Code:
    Private Sub Form_Current()
        With Me![SubformName].Form
            .Visible = (.RecordsetClone.RecordCount > 0)
        End With
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Yeah - that one doen't work.......I tried some variations of it, including calling to it through the mainform bracket exclaimation bracket...... I get different errors.
    Where Access "can't find" my subform, I'm wondering if it's something in the subform causing the problem.

    It's not set to data entry or add new record, which were 2 of the possible causes I've found.

    Do I need to embed subform somewhere else? s/f and 2s/f are both many and can have multiple records showing, so we like datasheet.

    Is there a form property that needs to be set that I am overlooking?
    (when I made this subform, I copied the 1st s/f - - - I think today I'll try to create from blank form and suffer through the field formatting!)

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you pay attention that [SubformName] is the name of the control of type SubForm, not the name of the SubForm object? Both names can be different.
    Have a nice day!

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    Thank you! No I hadn't! They are different...so I went in and rewrote the code pulling the name from the subform properties
    Got a new error: 2146500594(800f000c) Method 'Form' of object '_subform" failed

    I read somewhere that is has to do with references out of order - and I can't figure it out

  6. #6
    Join Date
    Jun 2010
    Posts
    186
    I GOT IT! Ya-hoo!!
    The whole problem was that those names were different.....can see why that woul dbe confusing! I deleted the form and made a new one, it saved with both names the same and was able to run the code you gave me yesterday.
    Awesome!

    Thank you

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Jun 2010
    Posts
    186
    I have more data for these ACCOUNTS now, and would like to show another set of information in another s/f that hides if no data.

    I'm not sure how add that to this code?

    Private Sub Form_Current()
    With Me![sfViewSlmnNotes].Form
    .Visible = (.RecordsetClone.RecordCount > 0)
    End With
    End Sub


    Also, in the form design mode, I would like these forms one on top of the other, and if both have data, 1 will be on top and the 2nd will be below it.
    Is there a way I need to lay them out in design view to get them to do that?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •