Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Collapse Subform Using Cmd Button (A97)

    I have a command button in a form that opens a blank subform for users to enter details about a record when clicked. I would like for the subform to:
    A) Collapse when the cmd button is clicked again
    [img]/forums/images/smilies/cool.gif[/img] Become visible initially if there is data in the subform for that record
    (ex. If a user enters text in the subform initially and goes back to that record later, the subform would be expanded to show the data rather than having to click the cmd button to see if there is any there for that particular record)

    Thanks as always,

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Collapse Subform Using Cmd Button (A97)

    Can't this code in your form's current event do the job?
    (Or am I too fast...)

    Private Sub Form_Current()
    If Me.Subformcontrol.Form.RecordsetClone.RecordCount = 0 And Not Me.NewRecord Then
    Me.Subformcontrol.Visible = False
    Else
    Me.Subformcontrol.Visible = True
    End If
    End Sub

    Then, you can still use your button to show the empty subform again in records with no sub-datasets when desired.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapse Subform Using Cmd Button (A97)

    Should I be putting this code into my form, my subform, or my command button?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collapse Subform Using Cmd Button (A97)

    Hasse has gone offline again, so I'll answer for him. The code he posted is meant for the On Current event of the main form. You will also need code for the command button on the main form.

    For the On Current event of the main form:

    Open the main form in design view.
    Activate the Events tab of the Properties window.
    Click in the first event (On Current) and select Event Procedure from the dropdown list.
    Click the builder button (the three dots ... on the right hand side.)
    Copy the code between Sub Form_Current() and End Sub from Hasse's post into your form module.
    Replace Subformcontrol by the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window.

    For the command button:

    Select the command button (with the form in design view).
    If necessary, activate the Events tab of the Properties window again.
    Click in the On Click event and select Event Procedure from the dropdown list.
    Click the builder button (the three dots ... on the right hand side.)
    Copy the following code into the form module:

    Me.Subformcontrol.Visible = Not Me.Subformcontrol.Visible

    (Again, replace Subformcontrol by the name of the subform etc.)

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Collapse Subform Using Cmd Button (A97)

    HansV
    you completed it better than I should have been able to (just one reason why it's always so good to have some <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33> moderators in the house <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    thanks!
    Hasse

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapse Subform Using Cmd Button (A97)

    "Replace Subformcontrol by the name of the subform as a control on the main form"

    Not sure exactly what you mean here....(i'm being ridiculously dense about this). if my subform is called frmProductsSubform, then i replace the text in the code that reads Subformcontrol with frmProductsSubform?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collapse Subform Using Cmd Button (A97)

    Your subform is named frmProductsSubform. That is most probably the name you see in the database window, and it is quite possible, but not sure, that it is also the name it has on the main form. To test this,
    open the main form in design view. Click on the subform to select it. The name of the subform will appear both in the Object dropdown list in the toolbar, and in the Properties window if that is open*. It is this name that you need, even if it is different from frmProductsSubform. Replace all occurrences of Subformcontrol in the code from my/Hasse's replies by this name.

    * As you will see, you can change the name of the subform on the main form without changing its name in the database window. That is because the subform has two roles: it is a form, but it is also a control on the main form, just like a text box or a label. The name of the subform as a form is its Source Object property, the name of the subform as a control is its Name property. Confusing, eh?

Posting Permissions

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