Re: [MSAccessProfessionals] I need help to create vba function

It would have helped if you included the image from your previous email since you forced us to glance back and forth. I would start with a table of all of the month periods.  Use this tblMthPeriods with your main table and set the criteria under the new MthPeriod field to Between cds_open_or_created_eom and cds_last_snapshot_eom. This will display a duplicate record for every month period from the start to the end.

Get this correct and we can proceed with other issues.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of anzus101 <anzus101@gmail.com>
Sent: Wednesday, March 8, 2023 8:55 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] I need help to create vba function

 

I forgot to give context to the function inputs and outputs;

1.- column 1 is a transaction 

2.- column 2 is the starting end of month period the transaction appeared

3.-The third column is the last end of month the transaction appeared

4.- The aim of the function is to return a sequence of all end of months in between, in addition to the starting end of month and the last end of month. and, also evaluate if the transaction is closed or opened. The transaction is closed if the sequenced end of month is not the current end of month, which as of today, the current end of month is 23-02, which means February of 2023.

Thanks for your attention!

On Wednesday, March 8, 2023, anzus101 via groups.io <anzus101=gmail.com@groups.io> wrote:

Hi folks, 

I am rusty on Vba, could you please guide me to create a function as show in the pic below? I invite you a round of coffee to you and your mates. Thanks.

Re: [MSAccessProfessionals] I need help to create vba function

I forgot to give context to the function inputs and outputs;

1.- column 1 is a transaction 

2.- column 2 is the starting end of month period the transaction appeared

3.-The third column is the last end of month the transaction appeared

4.- The aim of the function is to return a sequence of all end of months in between, in addition to the starting end of month and the last end of month. and, also evaluate if the transaction is closed or opened. The transaction is closed if the sequenced end of month is not the current end of month, which as of today, the current end of month is 23-02, which means February of 2023.

Thanks for your attention!

On Wednesday, March 8, 2023, anzus101 via groups.io <anzus101=gmail.com@groups.io> wrote:

Hi folks, 

I am rusty on Vba, could you please guide me to create a function as show in the pic below? I invite you a round of coffee to you and your mates. Thanks.

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116374) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

[MSAccessProfessionals] I need help to create vba function

Hi folks, 

I am rusty on Vba, could you please guide me to create a function as show in the pic below? I invite you a round of coffee to you and your mates. Thanks.

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116373) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Export to PDF

You haven't shown your code, but make sure that the user has write permissions to the output path and that the name being given the file is legal.

Paul

------ Original Message ------

From "Ryan S" <ryan.paschal@gmail.com>

To MSAccessProfessionals@groups.io

Date 3/6/2023 12:01:34 AM

Subject Re: [MSAccessProfessionals] Export to PDF

The codes above worked before. But, now it is returning error 2501 : "OutputTo method was cancelled".
There's nothing changed on the codes. I am confused why it is suddenly returning an error?
Does changing the references affect it?

Please enlighten me.

Ryan

On Wed, 14 Sept 2022 at 03:45, Graham Mandeno via groups.io <graham=mandeno.com@groups.io> wrote:

Hi Paco

strFilter should be used only in the SECOND position (WhereCondition).  The FIRST position (FilterName) should be blank.  It might work having it in both positions, but it is not correct.

According to the documentation here FilterName should be "A string expression that's the valid name of a query in the current database".  As Crystal has pointed out, it is rarely ever used.

Kind regards,
Graham [Access MVP 1996 – 2016]

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of paco@cellarinfo.com
Sent: Wednesday, 14 September 2022 02:55
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Export to PDF

 

Thanks, Paul and Crystal. I see my problem. I use strFilter for both the "name" of the filter AND for the Where Condition. I should have included it twice (in both positions). It's working fine now.

 

Paco

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of crystal (strive4peace) via groups.io
Sent: Sunday, September 11, 2022 3:57 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Export to PDF

 

hi Paco,

no, the extra comma needs to go BEFORE the WhereCondition. In reality, the FilterName parameter is barely used.

syntax:

DoCmd.OpenReport ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs

~ crystal ... more below*

On 9/8/2022 8:45 PM, paco@cellarinfo.com wrote:

Note the extra comma between acViewPreview and strFilter. I think that pushes strFilter to the right position?

previous messages:

~~~

What does strFilter contain?  If it's really a wherecondition, it should be in the next position, like:

 

DoCmd.OpenReport ctllstReport.Column(0, varItem), acViewPreview, , strFilter, acHidden, strOpenArgs 

 

Paul

~~~

 

paco:

I have a VBA function that exports an Access report as a PDF rather than print or preview based on selections on a form. The record source (strFileName), report name (strReportName) and filter (strFilter) are passed to the function from the form. The Preview and Print functionality work fine, but the filter is not applied when outputting to a PDF (even though strFilter contains the proper filer syntax. Any ideas? Thanks in advance.

Private Function Export2PDF (strFileName, strReportName, varItem, strFilter, strOpenArgs) 

    DoCmd.OpenReport ctllstReport.Column(0, varItem), acViewPreview, strFilter, , acHidden, strOpenArgs      'Open the report in preview mode, hidden

    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName, False

    DoCmd.Close acReport, strReportName

End Function

 

~~~

* Paco, when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

 

kind regards,
crystal

Re: [MSAccessProfessionals] Export to PDF

The codes above worked before. But, now it is returning error 2501 : "OutputTo method was cancelled".
There's nothing changed on the codes. I am confused why it is suddenly returning an error?
Does changing the references affect it?

Please enlighten me.

Ryan

On Wed, 14 Sept 2022 at 03:45, Graham Mandeno via groups.io <graham=mandeno.com@groups.io> wrote:

Hi Paco

strFilter should be used only in the SECOND position (WhereCondition).  The FIRST position (FilterName) should be blank.  It might work having it in both positions, but it is not correct.

According to the documentation here FilterName should be "A string expression that's the valid name of a query in the current database".  As Crystal has pointed out, it is rarely ever used.

Kind regards,
Graham [Access MVP 1996 – 2016]

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of paco@cellarinfo.com
Sent: Wednesday, 14 September 2022 02:55
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Export to PDF

 

Thanks, Paul and Crystal. I see my problem. I use strFilter for both the "name" of the filter AND for the Where Condition. I should have included it twice (in both positions). It's working fine now.

 

Paco

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of crystal (strive4peace) via groups.io
Sent: Sunday, September 11, 2022 3:57 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Export to PDF

 

hi Paco,

no, the extra comma needs to go BEFORE the WhereCondition. In reality, the FilterName parameter is barely used.

syntax:

DoCmd.OpenReport ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs

~ crystal ... more below*

On 9/8/2022 8:45 PM, paco@cellarinfo.com wrote:

Note the extra comma between acViewPreview and strFilter. I think that pushes strFilter to the right position?

previous messages:

~~~

What does strFilter contain?  If it's really a wherecondition, it should be in the next position, like:

 

DoCmd.OpenReport ctllstReport.Column(0, varItem), acViewPreview, , strFilter, acHidden, strOpenArgs 

 

Paul

~~~

 

paco:

I have a VBA function that exports an Access report as a PDF rather than print or preview based on selections on a form. The record source (strFileName), report name (strReportName) and filter (strFilter) are passed to the function from the form. The Preview and Print functionality work fine, but the filter is not applied when outputting to a PDF (even though strFilter contains the proper filer syntax. Any ideas? Thanks in advance.

Private Function Export2PDF (strFileName, strReportName, varItem, strFilter, strOpenArgs) 

    DoCmd.OpenReport ctllstReport.Column(0, varItem), acViewPreview, strFilter, , acHidden, strOpenArgs      'Open the report in preview mode, hidden

    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName, False

    DoCmd.Close acReport, strReportName

End Function

 

~~~

* Paco, when you reply, please include previous messages in the thread so those following can keep up, thanks.

how to Quote Post .. Duane uploaded an image on how to quote post in a reply to show previous messages using the web interface here:

https://groups.io/g/MSAccessProfessionals/photo/249167/2977001

 

kind regards,
crystal

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116371) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Save report with First_Name & Last_Name in PDF format

Ryan,

Create a new, blank report in design view with no record source. Drag the first report from the navigation pane into the report header section. Drag the second report into the detail section and the third into the report footer.

Some things to keep in mind:

1. each section can only grow to about 21 inches
2. any page sections from your reports will not display and must be replaced by report or group sections

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Ryan S <ryan.paschal@gmail.com>
Sent: Saturday, March 4, 2023 6:37 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Save report with First_Name & Last_Name in PDF format

 

How to do that?

On Sun, 5 Mar 2023 at 03.30 Bill Mosca <wrmosca@comcast.net> wrote:

Ryan

One way that I've used in the past is to make one "shell" report and put all the reports you want in as sub reports. Be sure to use the "Can Shrink/Grow" properties so you don't end up with a lot of white space.

 

Regards,
Bill Mosca,
Founder, MS_Access_Professionals

MS Access MVP 2006-2016
My Nothing-to-do-with-Access blog

https://wrmosca.wordpress.com

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Ryan S
Sent: Sunday, February 26, 2023 11:31 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Save report with First_Name & Last_Name in PDF format

 

Dear Members,

 

How to combine multiple reports into a single PDF file?
I use MS Access 2013.

 

Thank you in advance.

 

Best regards,

Ryan

 

 

 

On Sat, 7 Jan 2023 at 11:29, LOU(at home) <lou@ix.net.au> wrote:

Hello Crystal,
thank you for your reply. Unfortunately my 'coding' capabilities are nowhere near yours and I have to confess I don't have a clue to as to what part of your code I need to use to replace my faulty code. :(
I'm sorry if I have wasted your time but I do appreciate your intentions, it's just that my old brain is not as sharp as it once was. :(
If you should get some spare time and guide me where and how to use your code it would be appreciated.

Kind regards,
Lou

--

Re: [MSAccessProfessionals] Save report with First_Name & Last_Name in PDF format

How to do that?

On Sun, 5 Mar 2023 at 03.30 Bill Mosca <wrmosca@comcast.net> wrote:

Ryan

One way that I've used in the past is to make one "shell" report and put all the reports you want in as sub reports. Be sure to use the "Can Shrink/Grow" properties so you don't end up with a lot of white space.

 

Regards,
Bill Mosca,
Founder, MS_Access_Professionals

MS Access MVP 2006-2016
My Nothing-to-do-with-Access blog

https://wrmosca.wordpress.com

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Ryan S
Sent: Sunday, February 26, 2023 11:31 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Save report with First_Name & Last_Name in PDF format

 

Dear Members,

 

How to combine multiple reports into a single PDF file?
I use MS Access 2013.

 

Thank you in advance.

 

Best regards,

Ryan

 

 

 

On Sat, 7 Jan 2023 at 11:29, LOU(at home) <lou@ix.net.au> wrote:

Hello Crystal,
thank you for your reply. Unfortunately my 'coding' capabilities are nowhere near yours and I have to confess I don't have a clue to as to what part of your code I need to use to replace my faulty code. :(
I'm sorry if I have wasted your time but I do appreciate your intentions, it's just that my old brain is not as sharp as it once was. :(
If you should get some spare time and guide me where and how to use your code it would be appreciated.

Kind regards,
Lou

--

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116369) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Save report with First_Name & Last_Name in PDF format

Ryan

One way that I've used in the past is to make one "shell" report and put all the reports you want in as sub reports. Be sure to use the "Can Shrink/Grow" properties so you don't end up with a lot of white space.

 

Regards,
Bill Mosca,
Founder, MS_Access_Professionals

MS Access MVP 2006-2016
My Nothing-to-do-with-Access blog

https://wrmosca.wordpress.com

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Ryan S
Sent: Sunday, February 26, 2023 11:31 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Save report with First_Name & Last_Name in PDF format

 

Dear Members,

 

How to combine multiple reports into a single PDF file?
I use MS Access 2013.

 

Thank you in advance.

 

Best regards,

Ryan

 

 

 

On Sat, 7 Jan 2023 at 11:29, LOU(at home) <lou@ix.net.au> wrote:

Hello Crystal,
thank you for your reply. Unfortunately my 'coding' capabilities are nowhere near yours and I have to confess I don't have a clue to as to what part of your code I need to use to replace my faulty code. :(
I'm sorry if I have wasted your time but I do appreciate your intentions, it's just that my old brain is not as sharp as it once was. :(
If you should get some spare time and guide me where and how to use your code it would be appreciated.

Kind regards,
Lou