Subform Recordsource Nightmare

[Widge]

I'm having a nightmare. I've used this technique before and can't understand why I'm getting an error using it now. Basically I have a form, on that is a sub form datasheet. What I am eventually aiming to do is have a button set the Record Source of the subform.

Now the code is as follows:

 
Private Sub Command6_Click()
 
SetFilter
 
End Sub
 
 
 
Sub SetFilter()
 
    Dim LSQL  As String
 
    LSQL = "SELECT AllProducts.Supplier, AllProducts.Manufacturer, AllTransactionData.[Product Code], First(AllTransactionData.[Product Description]) AS [Product Description], Sum(AllTransactionData.[Qty Sent]) AS Quantity, Sum(AllTransactionData.[Selling Price]) AS [Amount £], AllPrices.[Core/Wider], AllTransactionData.[Transaction Period] FROM AllPrices INNER JOIN (AllProducts INNER JOIN AllTransactionData ON AllProducts.[Product Code] = AllTransactionData.[Product Code]) ON AllPrices.[Product Code] = AllTransactionData.[Product Code] GROUP BY AllProducts.Supplier, AllProducts.Manufacturer, AllTransactionData.[Product Code], AllPrices.[Core/Wider], AllTransactionData.[Transaction Period] HAVING (((AllProducts.Manufacturer) = 'Polypipe Building Product')) ORDER BY AllTransactionData.[Transaction Period] DESC , Sum(AllTransactionData.[Qty Sent]) DESC"
 
    TopSalesByMonthsubform.RecordSource = LSQL
 
End Sub
 
 

Yes the select is huge, but I've literally ripped if from a query.

Its perfectly happy having that pasted into the recordsource from properties, but I can't get the subform to update from VBA.

I'm obviously doing something wrong with TopSalesByMonth subform.RecordS ource = LSQL but I can't see what. Its copied direct from past examples too.

Can anyone help?

[FishVal]

I'm having a nightmare. I've used this technique before and can't understand why I'm getting an error using it now. Basically I have a form, on that is a sub form datasheet. What I am eventually aiming to do is have a button set the Record Source of the subform.

Now the code is as follows:

 
Private Sub Command6_Click()
 
SetFilter
 
End Sub
 
 
 
Sub SetFilter()
 
    Dim LSQL  As String
 
    LSQL = "SELECT AllProducts.Supplier, AllProducts.Manufacturer, AllTransactionData.[Product Code], First(AllTransactionData.[Product Description]) AS [Product Description], Sum(AllTransactionData.[Qty Sent]) AS Quantity, Sum(AllTransactionData.[Selling Price]) AS [Amount £], AllPrices.[Core/Wider], AllTransactionData.[Transaction Period] FROM AllPrices INNER JOIN (AllProducts INNER JOIN AllTransactionData ON AllProducts.[Product Code] = AllTransactionData.[Product Code]) ON AllPrices.[Product Code] = AllTransactionData.[Product Code] GROUP BY AllProducts.Supplier, AllProducts.Manufacturer, AllTransactionData.[Product Code], AllPrices.[Core/Wider], AllTransactionData.[Transaction Period] HAVING (((AllProducts.Manufacturer) = 'Polypipe Building Product')) ORDER BY AllTransactionData.[Transaction Period] DESC , Sum(AllTransactionData.[Qty Sent]) DESC"
 
    TopSalesByMonthsubform.RecordSource = LSQL
 
End Sub
 
 

Yes the select is huge, but I've literally ripped if from a query.

Its perfectly happy having that pasted into the recordsource from properties, but I can't get the subform to update from VBA.

I'm obviously doing something wrong with TopSalesByMonth subform.RecordS ource = LSQL but I can't see what. Its copied direct from past examples too.

Can anyone help?

Hi, Widge.
Some additional information would be really helpful.
What error you are getting and where the code (if code at all) fails?
Do subform controls ControlSource propeties match the query fields?
Did you try to run the SQL expression in Query Builder?

[Widge]

Hi there!

The query works fine (it just looks a mess).

The error I am getting is:

Compile error:

Method or data member not found

As for the control source? I'm not sure. The recordsource is initially that query pasted into it, which displays fine as a datasheet.

Then to test the button, I set the recordsource to "" temporarily and created the little statement in the code.

[FishVal]

Hi there!

The query works fine (it just looks a mess).

The error I am getting is:

Compile error:

Method or data member not found

As for the control source? I'm not sure. The recordsource is initially that query pasted into it, which displays fine as a datasheet.

Then to test the button, I set the recordsource to "" temporarily and created the little statement in the code.

Hi, Widge.

Sorry, I should notice it from the very beginning.
You have a bad reference to [subform].Form.RecordSou rce property
Here is your code.

TopSalesByMonthsubform.RecordSource = LSQL
 

1. Despite you may reference control without name of collection ("Form.Controls !" or simply "Form!" as Controls is a default property of Form), its better to explicitely refer to Controls collection.

Me!TopSalesByMonthsubform
or
Me.TopSalesByMonthsubform
 

2. Above expressions return Control object which does not have RecordSource property. You need to get Form property of the Control.

Me.TopSalesByMonthsubform.Form.RecordSource=LSQL
 

[Widge]

I think I see what you mean! I had an experiment with the me. before but it didn't help.

I will give this a go and come back with praise in a bit! :D

[FishVal]

I think I see what you mean! I had an experiment with the me. before but it didn't help.

I will give this a go and come back with praise in a bit! :D

Using Me. within a form module as object name of the form owner the code module is in your case more likely question of good programming style, it doesn't cause the error.
What you need to do is to enter parh to RecordSource property explicitely.
Me.[SubFormName].Form.RecordSou rce

[Widge]

Brilliant, cheers!

I knew I wasn't referencing properly!