Subform populated from combo boxes

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" wrote in message
...
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