Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

Hi Wei,

Again, as per my previous email:

I never open a filter form in the On Open event of a report. I typically gather all of my filtering information on a form and then click a button that opens the report. It's a personal opinion that I don't want code in a report that does anything that is outside of the report (like opening a form).

Are you still running code in the On Open event of your report to open a filter form? If that is the direction you choose to use then someone who uses that technique should help you. I just never do it that way.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Wei Qian via groups.io <wei_qian2001=yahoo.com@groups.io>
Sent: Monday, July 20, 2020 12:42 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

 

Thanks, Duane!  I tried to change the query in the click event of OK button on the filter form, using your UDF.  I can see the query changed like I wanted, but I got an error saying "run time error 2585.  This action can not be carried out while processing a form or report event" after clicking OK, and debugger stops at last line code (DoCmd.OpenReport), though sometimes the report did run after I stopped the debugger.  I'm pasting the code again.  Can you see what did I do wrong?
Thanks very much!

Private Sub OK_Click()

 

Dim strSQL As String

Dim strOldSQL As String

 

strSQL = "SELECT * FROM vWorkOrders " & _

    "WHERE MaxOfReleaseDate BETWEEN '" & Me.txtStart & "' AND '" & _

    Me.txtEnd & "' "

      

  If Not IsNull(Me.cbQRFType) Then

    strSQL = strSQL & " AND QRFTypeDescr = '" & Me.cbQRFType & "' "

  End If

  

  If Not IsNull(Me.txtClassCode) Then

    strSQL = strSQL & " AND Class = '" & Me.txtClassCode & "' "

  End If

      

strSQL = strSQL & "ORDER BY [SN/LotStart], WorkOrderNo;"

  

strOldSQL = fChangeSQL("Instruments Query", strSQL)

 

DoCmd.OpenReport ReportName:="Instruments Detail Report"

 

 

End Sub

 
On Mon, Jul 20, 2020 at 11:20 AM, Duane Hookom wrote:

If replying on the web in the topic, please click the speech bubble icon to the left of the Formats drop down. Chain icons are typically for including "links" to other web pages.

Regarding your initial question, I never open a filter form in the On Open event of a report. I typically gather all of my filtering information on a form and then click a button that opens the report. It's a personal opinion that I don't want code in a report that does anything that is outside of the report (like opening a form).

Duane

On Mon, Jul 20, 2020 at 07:12 AM, Wei Qian wrote:

I am very sorry.  I came to the site to post things this time instead of replying from email, hoping to include the previous messages.  But I made a mistake.  I clicked the 'chain' icon on the right of the screen in replying, it says something about all previous messages, but it seems that was only for viewing, not for including in replying messages.  I'll re-post my last message using the 'bubble' icon on the left. Thanks very much!

On Sun, Jul 19, 2020 at 12:42 PM, Wei Qian wrote:

Arnelito,

Thanks so much for trying to help me out.  Sorry I was out earlier and I couldn't see the whole messages from my phone so I didn't reply.  The problem I have is my back  end database is converted from Access to SQL Server and my front end is still using Access for data entry and reporting.  Some reports run very slow when Access tries to run through all records and filter later.  So I was trying to use the pass through query to only get only the intended records.  Duane's function works fine, I can change the query according to the parameters selected and it reports run much faster. But I got the error of 'can't process opening reports' though sometimes it did run the report after I stopped the debugger.  You are saying I can put the code in report open event.  But the report open event has the macro of opening the form for parameter selection which is necessary before running the report.  Should I still put the code in the OK click button on the form?  I guess I can clone the selection form for each report though it sounds stupid. I'm not dealing with sub report, I only try to run the main report.  Thanks very much!