i have a database of equipment inspections in a hospital. after a series of testing we get a report, for a date range of all the inspections done, grouped by department. previously we used to print out the whole report and send each department their own report. then we would scan them in and convert them to PDF. We just discovered that we could print directly to image writer and send them electronically, saving on paper.

Is there a way to export each individual departments results to its own seperate document from this date range report? And then save it to a folder designated by the department name?

Thanks

Adam

Not: You can also save a report to a snapshot (.snp) file. Then the user doenms not have to hae 2003 installed to view the file. They can use the free snapshot viewer


It is possible to do what you want. The the the report must be run separately for each. You can use VBA code to loop through the deparments and print a report for each department.

I am pretty sure that there is an example in the Access Code Archive that do this using a PDF and then emails the PDF.

Edit:

See: ending Emails To A Group, One Person At A Time

Edited by: HiTechCoach on Fri Mar 14 13:03:22 EDT 2008.

thanks for the help, but emailing automatically really isn't what i need to do. our reports get reviewed first before they are sent out...is there a more simplified approach? all i need is access to split a report into reports for each individual department and save them as a snapshot.

Hi adam.
You can create "frmFilterByDept" and have open this form at the same time as your report on report Load Event
with DoCmd.OpenForm "frmFilterByDept", acNormal
In a design view of "frmFilterByDept" add one unbound combo with a row source from Query qryFilterByDept
with field Department selected from tblDepartments or however your table is named.
Then add 2 cmdButtons on your frmFilterByDept.
Name one button Filter Report and another Clear Filter.

Behind Filter Report button put this On Click Event
Dim strSQL As String, intCounter As Integer
For intCounter = 1 To 1
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
strSQL = left(strSQL, (Len(strSQL) - 5))
Reports![YourReportName].Filter = strSQL
Reports![YourReportName].FilterOn = True
Else
Reports![YourReportName].FilterOn = False
End If

Behind Clear Filter button put this On Click Event
Dim intCouter As Integer
For intCouter = 1 To 1
Me("Filter" & intCouter) = ""
Next

Advantage of this set up in comparison to to entering criteria such as Enter Department is that you can
filter report on the fly and take a snapshot of each report filtered by department without openening and closing and
opening report again with different Dept criteria.

Then when you done you can close the frmFilterByDept and on form close event put
DoCmd.Close acReport, "YourReportName"

HTH

this seems like it will work, except i get an error message when i click the filter report button, it says it cannot find the field filter1 in my expression. when i debug this is what is highlighted

If Me("Filter" & intCounter) <> "" Then

do i need to change "Filter" to something else?

Hi,
Name that unbound combo box Filter1 and in Tag Property type in Department

now i get a runtime error 438

object doesn't support this property or method

Hi,
What is your field name you used in query to populate your combo box?

Department

Hi.
Create new DB and import the table that query is based on to select department in your combo.
Also import that query and frmFilterByDept. Zip it and post it and I fix you up.

Also,
Is your report grouped on Department?

ok it is a little more complicated then that but i think i put something together that will work. the report i am working with is created from a date range that is input by the user. The query for the department list is based off of the query for the report so that it only lists the departments tested in that date range. everything should by included in the DB i am posting. Thanks for all the help

Adam



Yes it is grouped by department

Edited by: adamquinton on Mon Mar 17 13:30:00 EDT 2008.

Hi,
First of all, can you describe in detail the business model.
What this DB is suppose track and what is desired output.

Second, it is not normalized properly, spaces in field names, use of reserved words and characters, relationship not
correctly set between PK and FK, RI not set, etc., etc.

Table structure needs to be first developed correctly before continuing any further.

it tracks the inventory and inspections of a type of monitor used in our hospital system. Each time a monitor is inspected, an entry is made into the history table. we have to keep a running log of all inspections for regulatory purposes. the report i have included shows what monitors were tested and the results within a input date range. the monitors are grouped together by department.

In the end what i would like to have is to put in a date range, have a seperate report for each department that was tested in that date range, and each one exported as a snapshot or pdf.

This DB was made before i began working, and the creator has since left. no one in our system has anything but basic knowledge of access, so it does not suprise me that things are not set up correctly. any advice, or help you can give is appreciated. I understand that some things just can't be fixed and is probably better off just starting new.

Adam

adam,
Here's a basic tblStructure I'd recomend.
When you build subform Inspections, build it based on qry and add calculated field Date Due if your monitor
inspection is based on some time frequency such as semiannual or annual.
Example for semi annual inspection. Date Due: DateAdd("m",6,[DateInspected])

All your forms and combo boxes should be based on saved query instead just a sql statement.

See att. and see what you can do with it. It's build in 07 but converted to 02-03

Also Room is indexed wirh no duplicates so you not repeat rooms.
Identify all the rooms and enter them and than assign Region. Location and Department
Based on this set up with tblLocations you can use cascading combo boxes for your selections


Edited by: jurotek on Fri Mar 21 13:25:55 EDT 2008.