Hello,

I have two combo boxes linked to two different tables (workers &
competencies). When the user selects the worker and the competency
then clicks a button I want the subform to populate from a third table
which contains the worker, the competency tested, the date tested, and
the result.

I have created a query that retrieves the information I want with
parameters setup to prompt for the worker and competency.

I want to feed the values of the combo boxes to the parameters in the
query and display the results in a subform.

The code I have does not work at all:

Private Sub Command8_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim qdfParmQry As QueryDef
        Set db = CurrentDb()
        Set qdfParmQry = db.QueryDefs("WorkerCompResults")
        qdfParmQry("which worker do you want results for?") =
comboPharm.Value
        qdfParmQry("which competency do want results for?") =
comboComp.Value
        Set rs = qdfParmQry.OpenRecordset()

        SubFormWorkerCompResults.Form.Recordset = rs

End Sub

SubFormWorkerCompResults is bound to the query WorkerCompResults and
the parameters prompting for worker and competency popup before the
main form opens. I've been working on this for several hours and am
close to pulling my hair out. Please help!

Thanks,
-Blake

Make certain that your parameter names

        qdfParmQry("which worker do you want results for?") =
comboPharm.Value
        qdfParmQry("which competency do want results for?") =
comboComp.Value

are exactly the same as in the query.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"GoCoogs" <blaketheturtle@yahoo.com> wrote in message 
news:238bc686-d308-4fd4-a0ac-83a6b9d95f3a@d4g2000prg.googlegroups.com...
> Hello,
>
> I have two combo boxes linked to two different tables (workers &
> competencies). When the user selects the worker and the competency
> then clicks a button I want the subform to populate from a third table
> which contains the worker, the competency tested, the date tested, and
> the result.
>
> I have created a query that retrieves the information I want with
> parameters setup to prompt for the worker and competency.
>
> I want to feed the values of the combo boxes to the parameters in the
> query and display the results in a subform.
>
> The code I have does not work at all:
>
> Private Sub Command8_Click()
>    Dim db As Database
>    Dim rs As Recordset
>    Dim qdfParmQry As QueryDef
>        Set db = CurrentDb()
>        Set qdfParmQry = db.QueryDefs("WorkerCompResults")
>        qdfParmQry("which worker do you want results for?") =
> comboPharm.Value
>        qdfParmQry("which competency do want results for?") =
> comboComp.Value
>        Set rs = qdfParmQry.OpenRecordset()
>
>        SubFormWorkerCompResults.Form.Recordset = rs
>
> End Sub
>
> SubFormWorkerCompResults is bound to the query WorkerCompResults and
> the parameters prompting for worker and competency popup before the
> main form opens. I've been working on this for several hours and am
> close to pulling my hair out. Please help!
>
> Thanks,
> -Blake 

I do something similar that maybe you can use. In your query that creates the 
subform, put the combo box (or list box) fields as criteria. For example I 
have a list box named lstAreaIV. I put this as criteria in a field in the 
query named Work_Order_ID. On the criteria line of the query it looks like 
this:

[Forms]![frmMenuIV]![lstAreaIV]

The list box contains a list of Work Areas. When the user chooses a value in 
the list box it becomes the criteria for the query. In the On Change property 
of the list box I put in me.refresh. This will populate the subform. From 
posting on this board I've learned that using the After Update property 
instead of on change may be more efficient, but they both work.

"GoCoogs" wrote:

> Hello,
> 
> I have two combo boxes linked to two different tables (workers &
> competencies). When the user selects the worker and the competency
> then clicks a button I want the subform to populate from a third table
> which contains the worker, the competency tested, the date tested, and
> the result.
> 
> I have created a query that retrieves the information I want with
> parameters setup to prompt for the worker and competency.
> 
> I want to feed the values of the combo boxes to the parameters in the
> query and display the results in a subform.
> 
> The code I have does not work at all:
> 
> Private Sub Command8_Click()
>     Dim db As Database
>     Dim rs As Recordset
>     Dim qdfParmQry As QueryDef
>         Set db = CurrentDb()
>         Set qdfParmQry = db.QueryDefs("WorkerCompResults")
>         qdfParmQry("which worker do you want results for?") =
> comboPharm.Value
>         qdfParmQry("which competency do want results for?") =
> comboComp.Value
>         Set rs = qdfParmQry.OpenRecordset()
> 
>         SubFormWorkerCompResults.Form.Recordset = rs
> 
> End Sub
> 
> SubFormWorkerCompResults is bound to the query WorkerCompResults and
> the parameters prompting for worker and competency popup before the
> main form opens. I've been working on this for several hours and am
> close to pulling my hair out. Please help!
> 
> Thanks,
> -Blake
>