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

Unsubscribed Me Here!

On Fri, Jul 24, 2020 at 4:28 AM Wei Qian via groups.io <wei_qian2001=yahoo.com@groups.io> wrote:

Hi Duane,

Just to let you know.  I haven't seen that issue again.  I think that might be from report not properly closed like you guessed.

Thanks very much for all your help!  Really appreciate it!

~ Wei
On Wed, Jul 22, 2020 at 12:01 PM, Duane Hookom wrote:

Hi Wei,

You need to make sure the report is closed when you run the OpenReport method. I almost always use acViewPreview. 

 

If your report is not filtered by the WHERE clause of your pass-through then something is not as expected. Maybe the report was already open or something changes the record source. 

 

Duane

 

 

---

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

 

Yes.  SQL view of [Instrument Query] is like 'SELECT * FROM vWorkOrders WHERE MaxOfReleaseDate BETWEEN '5/1/2020' AND '7/22/2020'  AND QRFTypeDescr = 'Production'  AND Class = 'EE' ORDER BY [SN/LotStart], WorkOrderNo;' This is as expected, those values in where clause are what I have selected in the filtering form.  [Instrument Query] is the data source.  There's no non-blank events except the 'key preview' has a 'no' in it, I guess that's just a default. It seems from the doc the default acViewNormal should be printer, though that was not what I see when first added code and got report back, even with that error. Maybe that macro I got rid of has it open in Preview mode? Though it seems to me the macro is only open the filtering form, but I really don't know much about macro and it seems not easy to read to me.  What do you usually use for open report?  Just the default, or acViewPreview,, or acViewReport?  Thanks very much!

On Wed, Jul 22, 2020 at 09:15 AM, Duane Hookom wrote:

Wei,

After you run your code, what is the SQL view of the query [Instruments Query]? Does this return exactly the expected records? Does it match your criteria from your form?

 

Open the [Instruments Detail Report] in design view and look at the Record Source. Is it [Instruments Query]? Do you see any non-blank events in the report properties?

 

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 7:03 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 didn't change the code, the same one I posted twice before.  I only got rid of the macro in report's open event. The record source is the pass through query which I used your UDF to change where clause each time, that doesn't change as well.
On Tue, Jul 21, 2020 at 04:26 PM, Duane Hookom wrote:

Wei,

What is your code or macro to open the report? Can you share your code? What is your report's record source? Is there anything that might change the record source of 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: Tuesday, July 21, 2020 1:58 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

 

Sorry I encountered a new issue now.  Somehow the report is now sending to a printer directly in opening.  I need it to show on the screen first.  I googled and it says with DoCmd.OpenReport, acViewNormal is for printing to printer, acViewReport and acViewPreview are for printing on screen.  But when I use acViewPreview, it somehow does not use the pass through query, goes back to pull all records one by one very slowly when I only need 2 records back.   ViewReport seems get back result quick, but I can't see how many pages, and there's no print button.  I also don't see any place in Access to set printer/screen as user preference. 

How do you resolve this print issue?  Thanks again!

~ Wei
On Tue, Jul 21, 2020 at 01:59 PM, Duane Hookom wrote:

Wei,

Thanks for including the previous messages! It makes it much easier to follow for some of us old mature members to recall the previous content and banter.

 

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

 

Thanks for listing the steps Duane.  I see the only difference is opening a form, so I got rid of the macro and put the open form in a button.  The error is gone.  I thought open from the macro and code would be the same, but I certainly do not know Access well enough.  

Thanks again for your help!  Sorry for the mistakes of not including previous messages in my earlier posts.

~ Wei
On Tue, Jul 21, 2020 at 10:20 AM, Duane Hookom wrote:

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!

 On Tue, Jul 21, 2020 at 04:26 PM, Duane Hookom wrote:

Wei,

What is your code or macro to open the report? Can you share your code? What is your report's record source? Is there anything that might change the record source of 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: Tuesday, July 21, 2020 1:58 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

 

Sorry I encountered a new issue now.  Somehow the report is now sending to a printer directly in opening.  I need it to show on the screen first.  I googled and it says with DoCmd.OpenReport, acViewNormal is for printing to printer, acViewReport and acViewPreview are for printing on screen.  But when I use acViewPreview, it somehow does not use the pass through query, goes back to pull all records one by one very slowly when I only need 2 records back.   ViewReport seems get back result quick, but I can't see how many pages, and there's no print button.  I also don't see any place in Access to set printer/screen as user preference. 

How do you resolve this print issue?  Thanks again!

~ Wei
On Tue, Jul 21, 2020 at 01:59 PM, Duane Hookom wrote:

Wei,

Thanks for including the previous messages! It makes it much easier to follow for some of us old mature members to recall the previous content and banter.

 

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

 

Thanks for listing the steps Duane.  I see the only difference is opening a form, so I got rid of the macro and put the open form in a button.  The error is gone.  I thought open from the macro and code would be the same, but I certainly do not know Access well enough.  

Thanks again for your help!  Sorry for the mistakes of not including previous messages in my earlier posts.

~ Wei
On Tue, Jul 21, 2020 at 10:20 AM, Duane Hookom wrote:

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!

--

Arnelito G. Puzon

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115738) | Reply To Group | Reply To Sender | Mute This Topic | New Topic

Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_