Store and send reports by customer number Access 2016

[TrevorJ]

I've looked through the site for this but nothing quite matches. I have an Access report for monthly accounts called rptAccount.
Each report is grouped by Customer code number, a 3 character number string (012) etc..
Is there a quick and easy (ish) way to send these accounts each month. They print separately but send as one big report. That is fine for archive. At the moment I have to go to Print Preview, save each report page as a pdf which requires about 6 clicks, then I have to go back to the archived pdfs and 'send' to email recipient. It's a bit long winded because there are usually 30+. Thanks for any help, macro if possible, not very conversant with VBA.

[TrevorJ]

Yes, it is on the cmdButton OnClick.
The gstrReportFilte r is in the correct code pasted into the form's Test_Click() procedure. It was the Report_rptAccou nt code that I placed the Dim in. I just commented it out and got the Variable not defined error so what should I do, I did copy and paste the code in?
I am sure that the code in the report is what you sent, it is the only code in the Report part. It's not easy to run through the code in the code window without the F keys. When I click on the menu to run I am confronted with a Create Macro dialogue which I don't understand. However, when I open the report the code stops at the IfLen line with the 'Variable not declared' error and the gstrReportFilte r is highlighted.
Yes, I will leave the 'DoCmd.SendObje ct' line as it is for now.
No I didn't mean I've given up with her, I meantthat sometimes it's prudent to give up debating something. I'm doing o.k. so far I think, 48 years at the moment.

[twinnyfo]

OK - so, very much my bad on this one. Sometimes I can't see the forest for the trees, and don't realize something obvious.

From your DB, go to the Create Menu, under Macros & Code, click Module. Put this in:

Option Compare Database
Option Explicit
 
Public gstrReportFilter As String

Save it as "modTwinnyfoIsA nIdiotSometimes "

Delete the Public declaration statement in the Form.

All should now work.

Apparently--unbeknownst to me until I researched it and then actually tried it myself, declaring a variable as a public variable on a form does not behave the same way as declaring that same variable in a blank module. This was the assumption I was operating under, and I apologize for the length it has taken for this to be identified as an issue. Since I have always used global variables in stand alone modules, this has never been an issue, and I assumed declaring the same in a Form's module would function the same way. This is not the case.

[NeoPa]

TrevorJ:
I'm doing o.k. so far I think, 48 years at the moment.

Well of course then! At that stage you'd be expected to have learnt enough wisdom to know not to argue with her any more than absolutely necessary. I'm nearly at that stage and only just over thrirty years in :-D Seriously, many congratulations to you both approaching your Golden Anniversary in a couple of years or so.

TwinnyFo:
Since I have always used global variables in stand alone modules, this has never been an issue, and I assumed declaring the same in a Form's module would function the same way. This is not the case.

This is a complicated one. Declaring a variable as Public within an Object's (Form or Report) Class Module does make it Public. What is different about such variables though is that they cannot be referenced simply from the Application object. They are members of different Objects so they need to be referenced as such. So, it would be referenced as Form_frmYourFor mName.gstrRepor tFilter. Not so straightforward .

So, unless you have a logical reason for storing such an item within an Object's Class Module rather than in a Standard Module, don't.

[twinnyfo]

NeoPa:

Thanks for the additional clarification. Although I had not tried, it, I was wondering if Form_frmYourFor mName.gstrRepor tFilter would work, and so apparently it does.

I use a series of global variables in my main DB, all decalred in a separate module that houses all my global constants, variables and some miscellaneous functions.

And so, I learn yet one more thing--this time, the hard way. However, now I am better able to advise others concerning this same issue.

About the time I figure all this stuff out it will be time to hangup my hat.

TrevorJ:

Congrats! I am only coming up on 14, but unfortunately this is my second go-round. But I know this one will last until Providence is pleased separate our immortal souls from our mortal bodies.

[TrevorJ]

Good morning, answers to your questions in post #50.
Yes, I have called the form 'Form_Test form to send rptAccount' and the only button in the form is named 'Test' (Caption Test click) and it's OnClick() event is all the code copied from your post #46 in the code block named Form code: I've just checked it and it is character for character o.k..
The 'Report Code:' block was the one that gave the Variable not defined error when I compiled the code so I put the Dim statement in for gstrReportFilte r and the error went away, I have now commented out the Dim.

Understood re. the report doesn't need to be open in any way.

I am certain that the code from post #46 named Report Code: has been copied and pasted to the rptAccount's VBA as Private Sub Report_Open(Can cel As Integer) Remember that I said that Microsoft had placed this in the Project browser and it has a notebook icon, not a module or class module icon. I have looked at Microsoft's explanation of icons at https://msdn.microsoft.com/en-us/vba...d-code-windows and there is no notebook icon.

Even I can see that the problem is related to the gstrReportFilte r now. It is difficult to step through the code with no useable function keys but I have managed to get a break point in various palces on the code, including the first line (after Option explicit line) and then clicked the report's command button. The code stops at the If Len(gstrReportF ilter) <> 0 Then line every time with the variable not defined error message.
The Dim statement that I put in is now commented out.

Gladly I didn't quite phrase my 'give up' status properly to NeoPa, what I meant was that I thought it was better to give up sometimes, than argue with my wife, I haven't given up on her though, nor her me. Were currently scoring 48 years.

[twinnyfo]

Now the only thing left in order for this to work (fingers crossed) is to create the standalone module from Post #52.....

[TrevorJ]

Hi Both,
Wait for it............. ..... it works! All I have to do now is some polishing and tidying, and if my daughter wishes, change the true for false on the DoCmd.SendObjec t line.
I had to change the name of the standalone module name though! Seriously, thank you, it has been an excellent learning opportunity and all's well that ends well.
Thank you both for your anniversary comments, I wish you both well with your journeys.
One mystery remains, in that apparently even Microsoft don't know what the notebook icon means in the VBA Project browser, I guess we may never find out.
And finally ... (Question moved to new thread -NeoPa).
Regards,
TrevorJ.

[NeoPa]

TrevorJ:
I didn't quite phrase my 'give up' status properly to NeoPa, what I meant was that I thought it was better to give up sometimes, than argue with my wife, I haven't given up on her though, nor her me. We're currently scoring 48 years.

I suspect you're mistaken in thinking I didn't understand your comment :-) That explains my understanding quite perfectly ;-)

My comment suggests that your longevity together quite rightly has taught you that arguing with your wife is rarely a sensible thing to do. I expect she'd agree with me on that one Trevor - wouldn't you say ;-)

BTW I created a new thread (Difference Between Open and Load of Forms) with your latest question. This wasn't a criticism. Simply a way to allow that question to be searchable in its own right (and to award TwinnyFo the Best Answer which he deserves).

This current thread is too complicated for me to select a Best Answer. If either of you feels one post is where most of the value is shown then please feel free to select it.

[TrevorJ]

Thanks for your additional comments. I too just read the whole thread and found it impossible to separate out one that was the best. Suffice to say that success prevailed in the end - for all.