wipnav
asked on
Set recordsource of unbound subform
I have an unbound subform on a bound form. I am trying to set the recordsource of the subform in vba from the click event of a command button to display the records related to the value of a field on the parent form. I am getting a run time error 2467 - "The expression you entered refers to an object that is closed or doesn't exist." This error is ocurring on the line of code listed below. This seems like it should be very simple but is causing me a great deal of trouble.
Addtionaly, I cannot simply create a bound subform and link the fields as I want to group the data by multiple fields.
Addtionaly, I cannot simply create a bound subform and link the fields as I want to group the data by multiple fields.
Private Sub Command73_Click()
Dim strSQL As String
strSQL = "SELECT FIRST([Group Code]), [Month], FIRST([Quarter]), FIRST([Year]), " & _
"SUM([Gross Sales]), SUM([CM-PY Sales]), SUM([E-Sales]), SUM([Rebateable Sales]), " & _
"SUM([Standard Calc Percent]), SUM([Growth Calc Percent]), SUM([Electronic Calc Percent]) " & _
"FROM tblHistoricSales WHERE [Group Code] = '" & Me.GROUP_CODE.Value & "' " & _
"GROUP BY [Month]"
'***The error occurs on the following line:
Me.tblHistoricalSales_subform.Form.RecordSource = strSQL
Me.Requery
End Sub
That is why LinkMasterFields and LinkChildFields is plural - you can enter multiple fields.
What is the error?
Take the query put it in the query designer to see if it works. Might be something wrong with the query.
Take the query put it in the query designer to see if it works. Might be something wrong with the query.
ASKER
capricorn - Yes, that was the name of the form, just to be safe, I renamed it to "sfrm" and tried again and I still received the same error.
GRayL - How would you do a group by with LinkMasterFields and LinkChildFields, how would I specify if I wanted a sum, min, max, etc.?
GRayL - How would you do a group by with LinkMasterFields and LinkChildFields, how would I specify if I wanted a sum, min, max, etc.?
You would enter the fields, separated by commas and qualified with their tablenames on the Link Master and Child Fields lines. Then enter the aggregate fields in the subform.
You would enter the Group By fields,...
<I renamed it to "sfrm" >
where did you do this?
where did you do this?
Me.tblHistoricalSales_subf
Me.Requery
Should be:
Me.tblHistoricalSales_subf
Me.tblHistoricalSales_subf
Me.Requery
Should be:
Me.tblHistoricalSales_subf
Me.tblHistoricalSales_subf
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
seen my comment here http:#a33599946
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you add a textbox control, wizard asks you about the control source.
The textbox name can by any.
With subform control, it asks for the form to use, if no form, it asks to use a table/query to create a form for you to be used.
The subform control can be any, but the form is the one you select. The form is the source object of the subform control. (compare with controol source for the textbox).
Check back previous comments and see if they are helpful.
The textbox name can by any.
With subform control, it asks for the form to use, if no form, it asks to use a table/query to create a form for you to be used.
The subform control can be any, but the form is the one you select. The form is the source object of the subform control. (compare with controol source for the textbox).
Check back previous comments and see if they are helpful.
Give your objects and controls the appropriate naming convention prefixes, to avoid confusion (and reference errors). In these cases I have found it useful to save the SQL as a query (so it can be examined in the query designer if there are any problems), and set it as the subform's record source. Then, you can just refresh the subform's source object as follows:
Assign a Subform variable to the name of the subform, and set its source object to "", then run queries or otherwise recreate the SQL record source used by the subform, then reassign the source object:
Assign a Subform variable to the name of the subform, and set its source object to "", then run queries or otherwise recreate the SQL record source used by the subform, then reassign the source object:
strForm = "frmDataEntryr"
Set subFrm = Forms(strForm)![subDetail]
subFrm.SourceObject = ""
strTable = "tblCMNumbersDetailEntry"
strSQL = "DELETE * FROM " & strTable
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
strQuery = "qappNumbersDetailEntry"
DoCmd.OpenQuery strQuery
strQuery = "qupdNumbersDetailEntry"
DoCmd.OpenQuery strQuery
subFrm.SourceObject = "fsubCurrentDetails"
This method avoids swapping the record source of an open object, which can be problematic.
ASKER
Thanks for your help guys.
GRayL, your solution turned out to be the most appropriate for my problem, I was able to link the master/child fields and place my aggregate functions in the fields on the subform which was quick and easy.
sb9, you got points as well because you indeed did identify the issue that I was having of not referencing the correct object which should help in a situation where linking master/child fields may not be practical.
GRayL, your solution turned out to be the most appropriate for my problem, I was able to link the master/child fields and place my aggregate functions in the fields on the subform which was quick and easy.
sb9, you got points as well because you indeed did identify the issue that I was having of not referencing the correct object which should help in a situation where linking master/child fields may not be practical.
are you sure the name of the subform is tblHistoricalSales_subform
open the form in design view
select the outer edge of the subform control and hit F4, select the All tab
see what is on the Name property
copy that and use it in your code