Solved

assign parameter query to container source object

Posted on 2014-02-10
14
469 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 50

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 50

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 50

Accepted Solution

by:
Gustav Brock earned 500 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 50

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 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

749 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