Generate reports for a series of specific groups (1 Viewer)

Metric · Sep 21, 2019

Hi,

I am still a real newbie to Access but I had so much luck with my last question here I am back for more!

I have created a database of members who are sorted into specific groups and I want to be able to create a report for each of these groups. Eg, for each person I have:
Name
DOB
Membership ID
Group

Where the groups start at 1 and go up numerically. I can see that if I want to generate a report for group 1, I just make a query and use 1 as a criteria under the column 'group'. But going forward, do I need to change the design every time to change the group number? Is there a way to get it to prompt the user to enter the group number, therefore creating a different report depending on which group you want to look at?

I hope this question is clear.

June7 · Sep 21, 2019

Apply filter criteria to report. The criteria can be a dynamic parameter in query (I don't do this) or use WHERE CONDITION argument of OpenReport command that references a control on form for user input of criteria. Common topic and many examples. Start with review of http://allenbrowne.com/ser-62.html

Pat Hartman · Sep 21, 2019

If you want the report to always print all groups but with breaks and counts, use the sorting and grouping options of the report.

If you want to print the report for a specific group then use the method suggested by June.

Or, you can do either method once you have formatted the report to group, you can run it with no criteria to print all or run it with a specific group to print just that group.

Metric · Sep 25, 2019

Thanks for the replies. It does really help to even know what this function is called (I was googling with no luck as I was using silly search terms).

Pat Hartman · Sep 25, 2019

Knowing what to search for is the hardest part of finding the solution

Metric · Sep 26, 2019

Pat Hartman said:
If you want the report to always print all groups but with breaks and counts, use the sorting and grouping options of the report.

If you want to print the report for a specific group then use the method suggested by June.

Or, you can do either method once you have formatted the report to group, you can run it with no criteria to print all or run it with a specific group to print just that group.

Okay, I got the report to format using the sorting and grouping options you mentioned, and I've even successfully created a macro to generate that report. However, despite reading the site June linked I'm still a little flummoxed. I'm assuming I should set up the macro with the where condition, but I just can't seem to figure out exactly what I should be entering there. Could anyone give me a concrete example of an expression that will work for this?

Thanks!

Pat Hartman · Sep 28, 2019

I don't use macros. Create a VBA procedure. In the click event of a button, use the OpenReport method. Intellisense should help you format the instruction and you can look up the syntax in help if you want to read about it first. The WHERE argument of the OpenREport method should probably point to a control on the form where the ID of the record you want to print is located.

Uncle Gizmo · Sep 28, 2019

I answered a similar question a few years back, and I posted the process on my website here:-

https://www.niftyaccess.com/generate-multiple-reports/

You have a form in which you create an SQL statement for each group in a loop. Once the SQL statement is created, the form opens the report, the reports on open event takes the SQL statement from the form, and the form is saved as PDF.

The loop repeats, creating the next report until you reach the end of the groups list, listed in your table.

I also provide a link to Gina Whipps website'-

https://www.access-diva.com/vba16.html

where she provides some code demonstrating how you could email each report as it is created.

[Edit] - All my code samples are free at the moment, to get them for free, use the coupon code:-

GetALL4Fr33_OrBuyMeA_Coffee

isladogs · Sep 28, 2019

You may find my examples showing how to do multiple filters and grouping useful. See https://access-programmers.co.uk/forums/showthread.php?t=302189. The method can be used for both forms and reports

June7 · Sep 28, 2019

The Allen Browne guide shows using VBA. Did you follow it and create VBA? I don't use macros either.

Metric · Sep 28, 2019

Thank you all, I read everything you so kindly suggested and in the end I ended up adding a condition to the report rather than trying to do it with a macro. So glad I finally figured it out, although I’m quite sure there are more exciting adventures to come! I’m finding the learning curve in this quite steep.

Pat Hartman · Sep 29, 2019

I ended up adding a condition to the report

Does that mean that you used the Where argument of the OpenReport method as I suggested or do you mean something else?

Metric · Sep 30, 2019

Pat Hartman said:
Does that mean that you used the Where argument of the OpenReport method as I suggested or do you mean something else?

Not quite. In the design view of the report itself I added a condition which then brought up a prompt for the user to enter the group number.

I’m learning slowly, there’s a lot more I’d like to do with this program. Hopefully I can overcome the hump of figuring it out.

Pat Hartman · Oct 1, 2019

Please notice that the method you selected will prompt a second time if you decide to print the report after it is opened. That is why I didn't suggest it.

The Where argument is simple, you are already using OpenReport so why not use the WHERE argument? Typically people add a text box or combo to the form so the user can choose a specific value. OR if the report is printed from a form where the data you want to see is already visible, then just reference the control with the unique ID.

Code:

docmd.OpenReport "reportname",acViewPreview,,"CustID = " & Me.cboCustID

Metric · Oct 2, 2019

I found that when I put it in the OpenReport it prompted twice to enter the info no matter what I tried. Putting it in the report it only prompts once. I understand that it's likely not ideal.

Pat Hartman · Oct 6, 2019

The method I suggested does not prompt twice. You must have done something different.

Generate reports for a series of specific groups (1 Viewer)

Metric

Registered User.

June7

AWF VIP

Pat Hartman

Super Moderator

Metric

Registered User.

Pat Hartman

Super Moderator

Metric

Registered User.

Pat Hartman

Super Moderator

Uncle Gizmo

Nifty Access Guy

isladogs

MVP / VIP

June7

AWF VIP

Metric

Registered User.

Pat Hartman

Super Moderator

Metric

Registered User.

Pat Hartman

Super Moderator

Metric

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread