Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

assign parameter query to container source object

Posted on 2014-02-10
14
Medium Priority
?
533 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 52

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 52

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 52

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 52

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 52

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

772 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