Collapse Subform Using Cmd Button (A97)

Topic

New Reply

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
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,

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.)

Reply | Quote

HansV
you completed it better than I should have been able to (just one reason why it’s always so good to have some moderators in the house )
thanks!
Hasse

Reply | Quote

“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?

Reply | Quote

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?

Reply | Quote