How to set query as recordsource of form in vb

[mrbboy]

I have a form, frm_CARReview, that I want the data filtered using a selection in combo box, cbo_Choices in another form frm_Choices. The choices are All, Open and Close. The first filter is to give me the last record in tbl_CARGeneral.

SELECT Last(tbl_CARGeneral.CARID) AS LastOfCARID, tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status
FROM tbl_CARGeneral INNER JOIN tbl_Employee ON tbl_CARGeneral.[Initiated By] = tbl_Employee.EmployeeID
GROUP BY tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status
HAVING (((tbl_CARGeneral.Status)=[Forms]![frm_Choices]![cbo_Choices]));


The second filter gives me the first record in tbl_CARGeneral.

SELECT First(tbl_CARGeneral.CARID) AS FirstOfCARID, tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status
FROM tbl_CARGeneral INNER JOIN tbl_Employee ON tbl_CARGeneral.[Initiated By] = tbl_Employee.EmployeeID
GROUP BY tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status
HAVING (((tbl_CARGeneral.Status)=[Forms]![frm_Choices]![cbo_Choices]));

The third filter gives me everything in tbl_CARgeneral.

SELECT tbl_CARGeneral.CARID, tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status
FROM tbl_CARGeneral INNER JOIN tbl_Employee ON tbl_CARGeneral.[Initiated By] = tbl_Employee.EmployeeID
GROUP BY tbl_CARGeneral.CARID, tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status
HAVING (((tbl_CARGeneral.Status)=[Forms]![frm_Choices]![cbo_Choices]));

Thanks for the help.

 

[GingerR]

Ok...so what's your question?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mrbboy]

How do I assign these three different queries as the recordsource of the form?

 

[GingerR]

In the form's OnLoad event, you can put

Select Case Form![frm_Choices]!cbo_Choices
Case "ALL"
   Me.Recordsource = "BLAH1"

Case "OPEN"
   Me.recordsource = "BLAH2"

Case "Closed"
   Me.Recordsource = "BLAH3"

End Select

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html