Reload subform SourceObject

[moishy]

I have a subform which shows a query (dragged the query and dropped it on the main form).

I can set the query sql so:

CurrentDb.QueryDefs("qryReports").SQL ="SELECT...."

But I can't get the subform to reflect the changes made to the querie's SQL unless the form is close and reopened.

How do I requery the subform/query without having to reopen the form?

p.s.
The reason for this setup is, that the subform is used to show data from diferent tables, with different filtering.

 

[CJ_London]

me.subformname.requery

or

me.subformname.controlsource="qryReports"

 

[moishy]

Tried:
Me.sfrmReportsControl.Requery
Doesn't make a difference.

Me.sfrmReportsControl.ControlSource = "qryReports"
Get a compile error: Method or data member not found.

Me.sfrmReportsControl.Form.ControlSource = "qryReports"
Get a runtime error 2465, Application-defined or object-defined error.

 

[CJ_London]

my mistake,

should be

Me.sfrmReportsControl.SourceObject = "qryReports"

 

[CJ_London]

also, I'm assuming that once you have modified the querys sql, you are saving it?

 

[moishy]

Get a compile error: Method or data member not found.
Setting the query sql using CurrentDb.QueryDefs, automatically saves the query. Does it not?

 

[CJ_London]

I make changes a different way, so don't know, but normally if you change something, you have to save it. Easy way to find out is to pause the code after the change then open the query.

and I corrected my post - it is the sourceobject

 

[moishy]

Ok, I checked the sql is saved automatically.

Me.sfrmReportsControl.SourceObject = "qryReports" gives a runtime error 2101.

 

[CJ_London]

What did you have as the sourceobject before? needs to be the same

 

[Galaxiom]

The problem is that you are using a query as the SourceObject. Create a form from the query and use it as the SourceObject.

Changing the RecordSource of a form automatically causes a requery. I am not sure if this applies when the query SQL is changed but commanding a Requery of the form or subformcontrol should.

 

[moishy]

What did you have as the sourceobject before?

In the designer, I set the SourceObject to qryReports. But even after the query is changed, those changes are not reflected until the form is closed and reopened.

needs to be the same

What do you mean, the SourceObject can't be changed?

 

[moishy]

The problem is that you are using a query as the SourceObject. Create a form from the query and use it as the SourceObject.

Changing the RecordSource of a form automatically causes a requery. I am not sure if this applies when the query SQL is changed but commanding a Requery of the form or subformcontrol should.

If I use a form as the SourceObject of the subform I'll have to create a separate one for each combination of fields/tables (there are quite a few...).

 

[Galaxiom]

If I use a form as the SourceObject of the subform I'll have to create a separate one for each combination of fields/tables (there are quite a few...).

Yes but there is no way around the fact that, unlike a form, a query has no Requery Method so it must be close and reopened.

You could use a generic form and customise it on load. Firstly, change the RecordSOurce property.

Then simultaneously loop through the form's Recordset.Fields and Controls Collections assigning the Field's Name property to the Control's ControlSource.

Also assign the Name to the label's Caption Property. In a loop, the displayed content of a label for a control is referred to as:

Me.Controls(loopcounter).Controls(0).Caption

If the number of field's varies, assign the Visible property to True and make the ones you don't need False.

Probably easier to use the Wizard to make individual forms.

 

[moishy]

Ok so here goes. I figured out the problem and a solution.

Problem:
1. If the ObjectSource = "" it can not be assinged an object (don't know why, but that is a fact).
2. Once the query is changed using CurrentDb.QueryDefs, the subform's ObjectSource evaluates to "".
And so, Me.sfrmReportsControl.SourceObject = "qryReports" doesn't work.

Solution:
1. Create 2 queries, qryReports1 and qryReports2.
2. Before changing the the query's SQL determine which query is the SourceObjec.
3. Change the SQL of the query that is not currently used as the SourceObject.
4. Assign the changed query to Me.sfrmReportsControl.SourceObject

Sample code (tested and working):

Private Sub cboReports_Change()

    Dim qdf As QueryDef
    Set qdf = IIf(Me.sfrmReports.SourceObject = "query.qryReports1", CurrentDb.QueryDefs("qryReports2"), CurrentDb.QueryDefs("qryReports1"))
    
    Select Case Me.cboReports
        Case Is = "Report 1"
            strReportName = "rptSample1"
            cboFilterBy.RowSource = "list of values seperated by semi colon"
            qdf.SQL = "SELECT ...."
        Case Is = "Report 2"
            strReportName = "rptSample2"
            cboFilterBy.RowSource = ""
            qdf.SQL = "TRANSFORM ...."
     End Select
    Me.cboFilterBy = ""
    Me.cboFilterCriteria = ""

    Me.sfrmReports.SourceObject = "query." & qdf.Name
End Sub

 

[CJ_London]

usually if you want to show a table or query in a subform you would use

query.qryReport

assuming qryReport is a query

oe for tables it is

table.mytable

which I now see in your code. Shame you didn't reveal this 14 posts ago

 

[Galaxiom]

Ok so here goes. I figured out the problem and a solution.

Problem:
1. If the ObjectSource = "" it can not be assinged an object (don't know why, but that is a fact).
2. Once the query is changed using CurrentDb.QueryDefs, the subform's ObjectSource evaluates to "".
And so, Me.sfrmReportsControl.SourceObject = "qryReports" doesn't work.

You have come to the wrong conclusions. The symptoms are from an error in the reference to the objects.

As CJ_London pointed out, the SourceObject must be qualified with Query or Table. Moreover the ChildLinkFields of the subformcontrol must be similarly qualified when using a table or query as a SourceObject.

 

[moishy]

usually if you want to show a table or query in a subform you would use

query.qryReport

assuming qryReport is a query

oe for tables it is

table.mytable

which I now see in your code. Shame you didn't reveal this 14 posts ago

Me.sfrmReportsControl.SourceObject = "Query.qryReports" also gives the same runtime error 2101.

 

[moishy]

You have come to the wrong conclusions. The symptoms are from an error in the reference to the objects.

Perhaps, but the solution does work.

As CJ_London pointed out, the SourceObject must be qualified with Query or Table. Moreover the ChildLinkFields of the subformcontrol must be similarly qualified when using a table or query as a SourceObject.

As I mentioned in regards to CJ_London's last post, Me.sfrmReportsControl.SourceObject = "Query.qryReports" also gives the same runtime error 2101. Also, the form is unbound and so the ChildLinkFields is not set.

 

[Galaxiom]

Me.sfrmReportsControl.SourceObject = "Query.qryReports" also gives the same runtime error 2101.

What version of Access are you using?

 

[moishy]

What version of Access are you using?

The 2016 version.