ms-accessms-access-forms

Make access subform only populate with button click

I am annoyed because I have done this previously but can't remember. I have an Access database with linked tables to SQL server database and a form which has a sub-form that is populated by a query. The query works as does the sub-form and indeed the sub-form is populated with correct data when the main form is invoked. But I want to stop the sub-form populating on load and only populating with a button click.

I have given the button's "On Click" event an embedded macro to Requery the sub-form and it does seem to refresh the sub-form but how do I stop the sub-form populating on main form load and only populating on the button click

Solution

  • You have several options.

    One command way is to remove the "source object" setting of the sub form control. Since the control (a subform control) does not have "pointer" to the sub form, the sub form will not load nor display.

    the code to thus set this (behind your button would be this):

       Me.MySubFormControl.SourceObject = "tblMain2"

    so, just set the source object setting to the sub form you want to display/load.

    Be CAREFULL during the form edit WHEN you blank out (remove) the source object setting of the sub form control, often messing during editing will also BLOW OUT the link master and child field settings. They usually stick, but can go away.

    The next possible?

    In place of setting the source object, you modify the sub form, and REMOVE its data source.

    Now in code behind that button, you can go:

    me.MySubFormControl.Form.Source = "SELECT * from child table"

    Once again, when you do the above, often the link master/child settings can go away. In some cases, you thus can go:

    me.MySubFormControl.Form.Source = "SELECT * from childTable where parent_id = " & me!id

    Now of course you replace parent_id with the column name used to relate to the parent form.

    I think the source object is a better choice, since then not even the sub form is rendered or even loaded when the main form loads.

    The only issue is that OFTEN access will attempt to set/change the link master/child settings, so you might consider setting the link master and link child field settings in code also (right before the source object line of code).