By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Subform Recordsource Nightmare

P: 56
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command6_Click()
  3.  
  4. SetFilter
  5.  
  6. End Sub
  7.  
  8.  
  9.  
  10. Sub SetFilter()
  11.  
  12.     Dim LSQL  As String
  13.  
  14.     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"
  15.  
  16.     TopSalesByMonthsubform.RecordSource = LSQL
  17.  
  18. End Sub
  19.  
  20.  
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 TopSalesByMonthsubform.RecordSource = LSQL but I can't see what. Its copied direct from past examples too.

Can anyone help?
Aug 8 '07 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command6_Click()
  3.  
  4. SetFilter
  5.  
  6. End Sub
  7.  
  8.  
  9.  
  10. Sub SetFilter()
  11.  
  12.     Dim LSQL  As String
  13.  
  14.     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"
  15.  
  16.     TopSalesByMonthsubform.RecordSource = LSQL
  17.  
  18. End Sub
  19.  
  20.  
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 TopSalesByMonthsubform.RecordSource = 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?
Aug 8 '07 #2

P: 56
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.
Aug 9 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.RecordSource property
Here is your code.
Expand|Select|Wrap|Line Numbers
  1. TopSalesByMonthsubform.RecordSource = LSQL
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. Me!TopSalesByMonthsubform
  2. or
  3. Me.TopSalesByMonthsubform
  4.  
2. Above expressions return Control object which does not have RecordSource property. You need to get Form property of the Control.
Expand|Select|Wrap|Line Numbers
  1. Me.TopSalesByMonthsubform.Form.RecordSource=LSQL
  2.  
Aug 9 '07 #4

P: 56
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
Aug 9 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.RecordSource
Aug 9 '07 #6

P: 56
Brilliant, cheers!

I knew I wasn't referencing properly!
Aug 9 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.