Solved

assign parameter query to container source object

Posted on 2014-02-10
14
452 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
  • 9
  • 5
14 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
whao fantastic! but gustav, just for learning purposes, how can i do this via code? thanks!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:developingprogrammer
Comment Utility
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 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
You should remove the parameter and use LO_team_tblPK as ChildField.

/gustav
0
 

Author Comment

by:developingprogrammer
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:developingprogrammer
Comment Utility
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
Comment Utility
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
Comment Utility
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now