Solved

assign parameter query to container source object

Posted on 2014-02-10
14
458 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
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 49

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 49

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 49

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 49

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

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