Code Help?

[gbanks]

I use the code below on a search form. I would like for the results of the list box search to populate a report instead of the list box. Is it possible to take the the sql and move it to a report? Thanks..

Dim strsql As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strsql = "SELECT SiteIssues_tbl.IssueID, SiteIssues_tbl.SITE_ID, SiteIssues_tbl.IssueID, SiteIssues_tbl.AdminDate, SiteIssues_tbl.Occurring, SiteIssues_tbl.Issue, SiteIssues_tbl.Administrative, SiteIssues_tbl.Technical, SiteIssues_tbl.IssueComments, SiteIssues_tbl.CandidatesAffected, SiteIssues_tbl.RecordCreated, SiteIssues_tbl.User, SiteIssues_tbl.DateModified " & _
"FROM SiteIssues_tbl"


strWhere = "WHERE"

strOrder = "ORDER BY SiteIssues_tbl.AdminDate;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtIssue) Then '<--If the textbox txtCenterName contains no data THEN do nothing
strWhere = strWhere & " (SiteIssues_tbl.Issue) Like '*" & Me.txtIssue & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtAdminDate) Then
strWhere = strWhere & " (SiteIssues_tbl.AdminDate) Like '*" & Me.txtAdminDate & "*' AND"
End If

If Not IsNull(Me.txtSite) Then
strWhere = strWhere & " (SiteIssues_tbl.SITE_ID) Like '*" & Me.txtSite & "*' AND"
End If

'Pass the SQL to the RowSource of the listbox

Me.lstSearchResult.RowSource = strsql & " " & strWhere & "" & strOrder

With Me.lstSearchResult
If .ListCount > 0 Then
GetListCount = .ListCount - 1 'Headings count
Else
GetListCount = 0 'Headings don't count if no items listed!!
End If
Me.Text31 = .ListCount - 1

Dim ctl As Control
End With
If Me.Text31 = -1 Then
Me.Text31.Value = 0
Me.lstSearchResult.RowSource = ""
DoCmd.RepaintObject acForm, "SitesIssues_qry"
Me.Requery


MsgBox "No Records Found."

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Then
ctl.Value = Null
DoCmd.RepaintObject acForm, "SitesIssues_qry"
Me.lstSearchResult.RowSource = ""
Me.Requery
End If
Next ctl
End If

 

[namliam]

You can do something

Currentdb.querydef("queryReport").SQL = strsql & " " & strWhere & "" & strOrder

Then run the report of the query... No real other option I guess....
Possibly you could do in the On Open event of the report do:
Me.rowsource = [where ever your rowsource now is]

Sorry for the pseudo code, but I really dont know where that is... some form or combo/list most likely....