Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

assign parameter query to container source object

Posted on 2014-02-10
14
Medium Priority
?
518 Views
Last Modified: 2014-02-20
hi guys,

Question: how do i display a parameter query in my  subform/subreport container without creating a form wrapper?

i understand this is deviant from best practices but i just want to learn how to do it = ) thanks guys!

Homework:

i'm thinking something like this code

Private Sub cmbTeam_AfterUpdate()
Stop
    Dim qdfPeriodSelection  As DAO.QueryDef
    Set qdfPeriodSelection = CurrentDb.QueryDefs("LR_PeriodSelection_qry")
        qdfPeriodSelection.Parameters("lngLO_Team_tblPK") = Me.cmbTeam
        
    Set Me.ctnPeriodSelection.Form.Recordset = qdfPeriodSelection.OpenRecordset(dbOpenDynaset)

End Sub

Open in new window

0
Comment
Question by:developingprogrammer
[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
  • 9
  • 5
14 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39849455
You just drag the query and drop it on the (sub)form.

The parameter you can control via Master and Link fields.

/gustav
0
 

Author Comment

by:developingprogrammer
ID: 39849462
whao fantastic! but gustav, just for learning purposes, how can i do this via code? thanks!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39849473
I don't know. It may be possible, but I never create forms on the fly.

You would open the form in design view, add subform control, set coordinates, set source element. Something like that.

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:developingprogrammer
ID: 39849484
thanks gustav, i tried doing this like what you said but i think i'm doing something wrong and it's not working.

here's my query
PARAMETERS [lngLO_Team_tblPK] Long;
SELECT MTK.WeekEndingSunday, MTK.PeriodEarlier, MTK.PeriodLater, DatePart("ww",[MTK].[WeekEndingSunday]) & " - " & DatePart("yyyy",[MTK].[WeekEndingSunday]) AS WeekNumber
FROM LO_Data_MTK_tbl AS MTK
WHERE (((MTK.LO_Team_tblPK)=[lngLO_Team_tblPK]));

Open in new window


and this is how it looks like in design view parameter query
i'm trying to put it on a form and fill in the parameter using cmbTeam. here is how the form looks. the form is unbound!

masterchildform.png
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39849499
You should remove the parameter and use LO_team_tblPK as ChildField.

/gustav
0
 

Author Comment

by:developingprogrammer
ID: 39849503
ok but gustav i have a few other queries as you can see on my form and they have sub / sub-sub queries that contain parameters. so it would almost be an entire redesign of my queries if i have to remove my parameters.

is there any way i can do it without removing my parameters?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39849518
You might be able to set the recordsource of the subforms dynamically but I think it will take more than adjusting the queries.

/gustav
0
 

Author Comment

by:developingprogrammer
ID: 39850929
thanks gustav, in the end what i did was

1) i created sub forms,
2) at first i bound them to their queries
3) i added the fields, set it to datasheet view, no additions etc (just properties settings)
4) i unbound them from their queries
5) i added this code
Private Sub cmdCalculateComparison_Click()

    Dim qdfPeriodDetails    As DAO.QueryDef
    Dim qdfBenefitsOutput   As DAO.QueryDef
    Dim qdfBenefitsTeam     As DAO.QueryDef
    
    'set the query def objects
    Set qdfPeriodDetails = CurrentDb.QueryDefs("LR_Details_qry")
        qdfPeriodDetails.Parameters("lngLO_Team_tblPK") = Me.cmbTeam
    
    Set qdfBenefitsOutput = CurrentDb.QueryDefs("LR_Benefits_Output_qry")
        qdfBenefitsOutput.Parameters("lngLO_Team_tblPK") = Me.cmbTeam
        
    Set qdfBenefitsTeam = CurrentDb.QueryDefs("LR_Benefits_Team_qry")
        qdfBenefitsTeam.Parameters("lngLO_Team_tblPK") = Me.cmbTeam
            
    'assign the subforms
    Me.ctnPeriodDetails.SourceObject = "Period_Details_subfrm"
    Me.ctnBenefitsOutput.SourceObject = "Benefits_Output_subfrm"
    Me.ctnBenefitsTeam.SourceObject = "Benefits_Team_subfrm"
    
    'fill the query defs into the sub forms
    Set Me.ctnPeriodDetails.Form.Recordset = qdfPeriodDetails.OpenRecordset(dbOpenDynaset)
    Set Me.ctnBenefitsOutput.Form.Recordset = qdfBenefitsOutput.OpenRecordset(dbOpenDynaset)
    Set Me.ctnBenefitsTeam.Form.Recordset = qdfBenefitsTeam.OpenRecordset(dbOpenDynaset)
    
End Sub

Open in new window


works fine now and thanks so much for your help gustav!
0
 

Author Comment

by:developingprogrammer
ID: 39850931
will leave this question open for awhile to see if anyone has a better way of doing it than how i did it = )
0
 

Author Comment

by:developingprogrammer
ID: 39852332
dear all this is another poster on stackoverflow who faced the same situation as me, but we resolved it in a different way. i did not go down his track because i try to keep clear of creating database objects dynamically. if we create, we have to delete moving forward.

http://stackoverflow.com/questions/4684909/ms-access-how-to-supply-parameters-to-a-query-based-subform-control
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 2000 total points
ID: 39852598
I don't get it. So much trouble in place of just modifying the queries to fit the purpose.

/gustav
0
 

Assisted Solution

by:developingprogrammer
developingprogrammer earned 0 total points
ID: 39862135
hi gustav, in the end i modified my query to read directly from the form but i changed the query name to add a sufix of "frmprm" which stands for "form parameter" so i will know that this query is dependent on a form and in the event i change my form or control, i know what "went wrong".

thanks for your help gustav! = )
0
 

Author Comment

by:developingprogrammer
ID: 39862142
oh btw my form was unbound so i couldn't do the child master way = )

hrmm but i think i should use bound forms more as that is more rapid development
0
 

Author Closing Comment

by:developingprogrammer
ID: 39872898
oh btw my form was unbound so i couldn't do the child master way = )

hrmm but i think i should use bound forms more as that is more rapid development
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

670 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