Use subform to display search results

[Lol999]

Probably an easy one for somebody but I've hit the wall after 3.5 hours tonight.

I have a form, on it is a combo box. There are also two text boxes set to date format where the user can enter Start and End dates.

Dependent upon the value selected by the user from the combo box and the date range a search delivers results from the database.

I would like to either display the results in a subform which can then be printed out or, ideally, a report which can then be printed out.
I have tried passing OpenArgs for the report and got nowhere, and the subform is the final nail in the coffin tonight.
I have the following as a search string, it does work when run through the query builder, but I just can't figure using this to generate output in either subform or report.
(ignore the naming conventions, it works for me so that's all that matters at the moment).
I used the Debug.print to verify values being collected from the form controls.

Here's the code:

Private Sub Btn_PPE_Search_Click()
strCategory = Me.Cbo_PPE_Category.Value
strSearch = "SELECT Tbl_Issue.Issue_Employee_ID, 
Tbl_PPE.PPE_PPE_Category, Tbl_PPE.PPE_PPE_Description, Tbl_Issue.Issue_PPE_Size, Tbl_Issue.Qty_Issue, Tbl_Issue.Date_Issued " & _
"FROM Tbl_Employees INNER JOIN (Tbl_PPE INNER JOIN Tbl_Issue ON Tbl_PPE.[PPE_ID] = Tbl_Issue.[Issue_PPE_ID]) ON Tbl_Employees.Employee_Employee_ID = Tbl_Issue.Issue_Employee_ID " & _
"WHERE (((Tbl_PPE.PPE_PPE_Category)=[strCategory]) AND ((Tbl_Issue.Date_Issued) Between [Txt_Start_Date.Value]  And [Txt_End_Date.value]));"

Debug.Print strSearch
Debug.Print strCategory
Debug.Print Me.Txt_End_Date.Value
Debug.Print Me.Txt_Start_Date.Value
End Sub

Thanks, Lol

 

[The_Doc_Man]

The first thing to consider is that if the sub-form shows the correct results, whatever it is using as a .RecordSource is EXACTLY what you want to use for the report in question.

So... if the only significant things are the contents of the combo and the two date fields, you can pick them up, put them in text format, and open the report programmatically with a filter statement. Now, here is how you might do this.

Begin with setting up and opening the report from the form.

Dim ArgCombo As String, ArgStart As String, ArgEnd As String, ArgFilter As String
...

ArgCombo = CStr(cboSelector)
ArgStart = "#" & FormatDateTime( txtStartDate, vbGeneralDate ) & "#"
ArgEnd = "#" & FormatDateTime( txtEndDate, vbGeneralDate ) & "#"
ArgFilter = "[TestValue] = '" & ArgCombo & "' And [TestDate] Between " & ArgStart & " And " & ArgEnd
DoCmd.OpenReport "TheReportName", acReportNormal, , ArgFilter, acWindowNormal
...

The line that defines the filter is made up based on a simple assumption of how you might use it. Whatever you used for the sub-form to pick what it did, use the same kind of tests for the report. Using a report filter, you are supplying a WHERE clause (but you don't need to supply the word WHERE). If there are other criteria besides those three, they can all go in the filter unless it gets REALLY long.

Those "FormatDateTime" calls presumed a date and time. But if they are date only, you could also use vbShortDate and it would work equally well.

 

[CJ_London]

you need to assign your strSearch to the subform recordsource

after your debugs and before the end put

me.subformname.form.recordsource=strSearch

This assumes that the subform controls themselves are bound to the fields returned in your query

where subformname is the name of your subform control

 

[Lol999]

@DocMan - what I am trying to do is output the results to EITHER a report or a subform - sorry if that wasn't clear in my post.

@CJ_London - perhaps a daft one, but when I add the subform it takes me through the wizard to add the fields from tables etc. Do I do this mirroring the fields that will be generated by the sql string that I will be assigning to the recordsource?


Thanks, Lol

 

[Lol999]

I just noticed you said "where subform controls are bound to the fields returned in your query", so I guess the answer is yes!

 

[The_Doc_Man]

Nope, wasn't clear. I though you had a (sub)form and only needed to know how to get it to a report. Still, take a look at the method I used to set a filter on the report before I opened it. The same general concept would work for opening a sub-form.

 

[Lol999]

Okay it works after a little tweaking on the syntax for the sql string.
Code below for anyone interested:

Private Sub Btn_PPE_Search_Click()
strSearch = "SELECT Tbl_Issue.Issue_Employee_ID, Tbl_Employees.Employee_Employee_Name, Tbl_PPE.PPE_PPE_Category, Tbl_PPE.PPE_PPE_Description, Tbl_Issue.Issue_PPE_Size, Tbl_Issue.Qty_Issue, Tbl_Issue.Date_Issued " & _
"FROM Tbl_Employees INNER JOIN (Tbl_PPE INNER JOIN Tbl_Issue ON Tbl_PPE.[PPE_ID] = Tbl_Issue.[Issue_PPE_ID]) ON Tbl_Employees.Employee_Employee_ID = Tbl_Issue.Issue_Employee_ID " & _
"WHERE (((Tbl_PPE.PPE_PPE_Category)= Cbo_PPE_Category.Value) AND ((Tbl_Issue.Date_Issued) Between Txt_Start_Date.Value  And Txt_End_Date.Value));"

Me![Frm_Search subform].Form.RecordSource = strSearch

Me.Cbo_PPE_Category.SetFocus

End Sub

Cheers, Lol