Open Report from Search SubForm (1 Viewer)

King21

New member
Local time
Today, 19:00
Joined
Jun 14, 2021
Messages
18
I have a form "FrmSearch" that searches table "Customers" and lists the results in a subform, I would like to open a report, then to export these results to a PDF file.

"FrmSearch" has an unbound control "txtcriteria" a "Search Button", an "Export Button" and an imbedded subform "FrmSubSearch".

The search function works fine and populates the subform correctly, my objective is to open a report "SearchRpt" populated with the filtered results as displayed in the subform, I have tried many variations of code but cannot get it to work.

"Search Button" code is
Code:
Private Sub btnSearch_Click()
 
  Dim strSearch As String
    Dim strText As String
    
    strSearch = "SELECT Customers.ID, Customers.OrderDate, Customers.OrderNo, Customers.Customer, Customers.Address, Payment.Payment " _
    & "FROM Customers LEFT JOIN Payment ON Customers.OrderNo = Payment.PayNo " _
    & "Where [OrderDate] Like '*" & Me.txtcriteria & "*' " _
    & " OR [OrderNo] Like '*" & Me.txtcriteria & "*' " _
    & " OR [Address] Like '*" & Me.txtcriteria & "*' " _
    & " OR [Customer] Like '*" & Me.txtcriteria & "*' " _
    & "ORDER BY Customers.[OrderDate] Desc "
    
    Me.Filter = strSearch
    Me.FilterOn = True

Me.FrmSubSearch.Form.RecordSource = strSearch
Me.FrmSubSearch.Form.Requery

End Sub

"Export Button" code I am trying is:
Code:
Private Sub Export_Click()

DoCmd.OpenReport "[SeachRpt]", acViewPreview

End Sub

When I click "Export Button" I get Error 2103, The report name '[SeachRpt]' you entered in the property sheet or The macro is misspelled or refers to a report that doesn't exist.

Could someone please advise how to overcome this problem.
 

Attachments

  • Search-1.zip
    55.4 KB · Views: 335

MarkK

bit cruncher
Local time
Today, 01:30
Joined
Mar 17, 2004
Messages
8,178
It doesn't make sense to me that you construct a complete SQL statement, and then assign it to the filter property of the form. Also, your code will break if a user enters an apostrophe. Something like this would be more robust...
Code:
Private Const SQL As String = _
    "SELECT ID, OrderDate, OrderNo, Customer, Address, Payment.Payment " & _
    "FROM Customers LEFT JOIN Payment ON OrderNo = Payment.PayNo " & _
    "WHERE OrderDate LIKE p0 " & _
        "OR OrderNo LIKE p0 " & _
        "OR Address LIKE p0 " & _
        "OR Customer LIKE p0 " & _
    "ORDER BY OrderDate Desc "

Private Sub btnSearch_Click()
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = "*" & Me.txtCriteria & "*"
        Set Me.FrmSubSearch.Form.Recordset = .OpenRecordset
    End With
End Sub
 

King21

New member
Local time
Today, 19:00
Joined
Jun 14, 2021
Messages
18
It doesn't make sense to me that you construct a complete SQL statement, and then assign it to the filter property of the form. Also, your code will break if a user enters an apostrophe. Something like this would be more robust...
Code:
Private Const SQL As String = _
    "SELECT ID, OrderDate, OrderNo, Customer, Address, Payment.Payment " & _
    "FROM Customers LEFT JOIN Payment ON OrderNo = Payment.PayNo " & _
    "WHERE OrderDate LIKE p0 " & _
        "OR OrderNo LIKE p0 " & _
        "OR Address LIKE p0 " & _
        "OR Customer LIKE p0 " & _
    "ORDER BY OrderDate Desc "

Private Sub btnSearch_Click()
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = "*" & Me.txtCriteria & "*"
        Set Me.FrmSubSearch.Form.Recordset = .OpenRecordset
    End With
End Sub

MarkK

Still trying to get my head around VBA, could you please explain where is the "Private Const SQL As String = _ code should be placed.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:30
Joined
May 7, 2009
Messages
19,169
you have mis spelling on your report name.
 

Attachments

  • Search-1.zip
    66.7 KB · Views: 359

King21

New member
Local time
Today, 19:00
Joined
Jun 14, 2021
Messages
18
you have mis spelling on your report name.
Thanks for the reply arnelgp, silly mistakes gets us every time.
Thanks also for the Report "On Open" code that has solved my problem.
 

Users who are viewing this thread

Top Bottom