?
Solved

Set recordsource of unbound subform

Posted on 2010-09-03
15
Medium Priority
?
908 Views
Last Modified: 2013-11-28
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.
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

Open in new window

0
Comment
Question by:wipnav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +4
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599436
Me.[tblHistoricalSales subform].Form.RecordSource = strSQL

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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33599811
That is why LinkMasterFields and LinkChildFields is plural - you can enter multiple fields.
0
 
LVL 13

Expert Comment

by:lucas911
ID: 33599885
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.
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 1

Author Comment

by:wipnav
ID: 33599890
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.?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33599930
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33599938
You would enter the Group By fields,...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599946
<I renamed it to "sfrm" >
where did you do this?
0
 
LVL 13

Expert Comment

by:lucas911
ID: 33600010
Me.tblHistoricalSales_subform.Form.RecordSource = strSQL
Me.Requery

Should be:

Me.tblHistoricalSales_subform.Form.RecordSource = strSQL
Me.tblHistoricalSales_subform.Form.Requery

0
 
LVL 16

Assisted Solution

by:Sheils
Sheils earned 800 total points
ID: 33600467
Looks like the confusion it between the subform and the source object which is a common rookie error.

When you create a subform you click on the subform icon on the toolbar and this allows you to create a box. (This box is the subform)

Then you are asked to select a form,table or query insert in the subform (These are the source objects)

By default access gives subform the same name as the source object which adds to the confusion. After falling in this trap for a number of times I have resolved to renaming the subform with the prefix chd. To do this right click on the box around the source object to open the subform property.

Check the data tab. The first item on this tab should be source object

select the other tab. Change name to chdHistoricalSales

Now your subform and subform object have different name it will be easier for you to know what you are referring to.

To change the record source of the source object use the following code

Me.chdHistoricalSales.Form.RecordSource = sql
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33600561

seen my comment here http:#a33599946
0
 
LVL 44

Accepted Solution

by:
GRayL earned 1200 total points
ID: 33600617
Note that in my proposed solution you do not need a query.  The main form is bound to the main table, the sub form is bound to the many table, the linkage is controlled by the settings in LinkMasterFields, and LinkChildFields.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33601190
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.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 33611886
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:
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"

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 33611895
This method avoids swapping the record source of an open object, which can be problematic.
0
 
LVL 1

Author Closing Comment

by:wipnav
ID: 33617372
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.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question