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

It's a matter of:

• opening a form that has controls for the user to enter the filtering criteria. 
• the user enters or selects the appropriate values to filter the report and clicks a button
• the button runs VBA that creates the SQL statement and updates the SQL property of a saved pass-through query
• the report based on the pass-through is then opened
• the report typically has no code or macros

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Wei Qian via groups.io <wei_qian2001=yahoo.com@groups.io>
Sent: Tuesday, July 21, 2020 9:01 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

 

Thanks Duane.  That's OK.  Could you share one example how you do it?  

On Tue, Jul 21, 2020 at 09:29 AM, Duane Hookom wrote:

Wei,

I don't ever open a "form to collect filtering info" with a macro or code in a report. I always collect the filtering info first and then open the report.

 

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 1:43 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

 

No.  Open event on of the report already has a macro that opens the form to collect filtering info, that is certainly needed.  This code is on the click event of the OK button on that form after a user puts in filtering info.  Do you have a sample code for how you click the button that opens the report? Thanks very much!

On Mon, Jul 20, 2020 at 02:20 PM, Duane Hookom wrote:

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!