Repeat Count (1 Viewer)

Hello,

I will try to make this as simple as possible. We have a department with multiple units. Each unit has a unique ID. Within each unit, each employee in those units also have unique ID's. As employees interact with customers they make notes on the customers accounts and assign the notes an expiration date. We have an database that pulls all the notes that are expiring that day and we want each employee to have all their notes print out on a report based on their employee ID. So the main table will contain all notes expiring that day with the unit and employee ID attached to each note. Whether or not a person has 1 note or 10 notes, we want each employee to have their own report of all their notes for that day. We have a macro to print the reports and we are using RunMacro and hard coding a number in the Repeat Count field that is high enough to make sure all notes are printed. A Unit may have 5 employees but only 2 may have notes that day, however we still need to hard code the number to be equal or higher than the total employees in that unit. Is there a way to evaluate the number of employees (with notes) each unit has that day based on the unit ID(chosen by the user) and only print out those notes and not have to hard code a generic number. Thanks.

Without knowing more about your table structure, this might be difficult to give you specifics. In essence, you want to build a query of notes that includes the note and the Employee ID from your Notes table and JOIN that with the list of Units - but depending on how the employee tables are set up, that might not be necessary.

Are you talking about having as many distinctly different reports as the number of Employees who had at least one note that day? And each person would ONLY get their notes and nobody else's? I think I read that between the lines but it was just unclear enough that I'm not certain.

A sample of the table structure is as follows: **sorry I have tried to line up the example below with no success. the first number is the EmpId, the second number is the UnitId and text just signifies the note.

EmpID UnitID Note
100 05 text
100 05 text
150 05 text
175 10 text
200 10 text

so what would happen is every day the supervisor of a unit will want all the notes belonging to their employees. so in the example above unit 05 supervisor would choose unit 05 and should receive 2 reports. 1 reports containing 2 notes for employee 100 and another report containing 1 note for employee 150. Then the supervisor for unit 10 would come along later and choose unit 10 and they should receive 2 reports, 1 for each of their employees (175, 200) with 1 note in each report. I hope this helps clarify it a bit. Thanks for the assistance.

OK, I'm going to free-associate for a moment here.

If you want this to be separate reports, then you need a query to identify the separate report targets (EmpID) which you would use in a loop to build each report. Then you would need a report designed specifically for this kind of note presentation.

The query might be similar to


There are a couple of ways to get the Unit ID into the query. Look up Parameter queries as one way to do it. Or you could simply build that SELECT DISTINCT string dynamically and append the unit ID to it, then open the SQL string as your recordset. Your choice.

Open this query as a recordset (called rsEmp for this example) and step through it one record at a time. For each record, you can then use


Now, inside the report you can define a header section. In the header's OnFormat routine, you can use the me.OpenArgs value (which is EmpID as a string) to qualify DLookup calls to get the employee name, unit info, etc. You can put that in the header section, which could perhaps be the Report Header. Then put the notes in the report detail section, which auto-repeats for each note. Because you opened the report with a filter, you only see that employee's notes in that report.

One last thought: If you are going to print these right away and forget them, fine. No issue. However, if you wanted to save these as files, I would export them using the right-click option so that you can specify the save format AND the file name. Otherwise one report will overwrite the next one.

As to the other part of this question, setting the number of copies to be printed, see this article and its child articles for the printer object and how to manipulate it.

https://msdn.microsoft.com/en-us/library/office/aa223133(v=office.11).aspx

Thanks for your help. You have given us a idea on how to proceed!!

Glad to help!