create a report based on a search result

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

create a report based on a search result

Post by siamandm »

Hello all,

if i have a for with a sub form showing the result of search text box using vba code, and if i want to show the result of the search on report , what should i do

Code: Select all

Dim sql As String

sql = "SELECT qrySearch.* FROM qrySearch " _
& " Where FirstName Like '*" & Me.txtSearch & "*'" _
& " OR SecondName Like '*" & Me.txtSearch & "*'" _
& " OR ThirdName Like '*" & Me.txtSearch & "*'" _
& " OR FirstName_Mother Like '*" & Me.txtSearch & "*'" _

Me.sbf_SearchData.Form.RecordSource = sql
Me.sbf_SearchData.Form.Requery
Regards

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: create a report based on a search result

Post by HansV »

Create a report with qrySearch as record source.
Save the report as (for example) rptSearch.
On your main form, create a command button named cmdReport.
The On Click event procedure for the command button would look like this:

Code: Select all

Private Sub cmdReport_Click()
    Dim strWhere As String
    strWhere = "FirstName Like '*" & Me.txtSearch & "*'" _
        & " OR SecondName Like '*" & Me.txtSearch & "*'" _
        & " OR ThirdName Like '*" & Me.txtSearch & "*'" _
        & " OR FirstName_Mother Like '*" & Me.txtSearch & "*'"
    DoCmd.OpenReport ReportName:="rptSearch", View:=acViewPreview, WhereCondition:=strWhere
End Sub
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: create a report based on a search result

Post by siamandm »

Thanks a lot working as expected.