source selection for reports- help request

[google]

I am trying to automate the way reports are printed in an Access 2003
database - I have ~200 records (people) who require between 5 and 10
customized reports (depending on values within certain fields) -
currently I print each report by selecting all employees and printing
one report at a time - once all reports are printed, I manually
collate the packages - I would like to be able to have a combo box (or
other option) highlight the required employees, press one button and
have all related reports print for the first employee, then move on to
the second employee, etc - thus each customized package will be
collated on the printer and all I have to do is add a paper clip.

I would use a combo box that displays all employees alphabetically
showing their name and employee number - once selected their employee
number (primary key) is used to select the report. I have looked at
using a macro to select the list of 10 reports, but do not have enough
experience to know how to code the procedure that will cycle through
the employee's and if they don't meet certain criteria, skip the
printing of certain reports.

I am not a programmer by any means but can generally understand code
and modify canned code to make it suit the tasks but for this project
I have not been able find a sample database that would help me
understand what I need to do.

Any help you can provide would be appreciated.

Thanks.

[Tom van Stiphout]

On Fri, 13 Jun 2008 22:46:07 -0700 (PDT), go****@infoland.ca wrote:

I'm voting to save some trees, and email the reports overnight to the
recipients.
How to construct the code depends on the details of your db design.
Hire a competent developer to assist you with that. "Microsoft
Solution Provider" in your yellow pages may be a good place to start.

-Tom.

>I am trying to automate the way reports are printed in an Access 2003
database - I have ~200 records (people) who require between 5 and 10
customized reports (depending on values within certain fields) -
currently I print each report by selecting all employees and printing
one report at a time - once all reports are printed, I manually
collate the packages - I would like to be able to have a combo box (or
other option) highlight the required employees, press one button and
have all related reports print for the first employee, then move on to
the second employee, etc - thus each customized package will be
collated on the printer and all I have to do is add a paper clip.

I would use a combo box that displays all employees alphabetically
showing their name and employee number - once selected their employee
number (primary key) is used to select the report. I have looked at
using a macro to select the list of 10 reports, but do not have enough
experience to know how to code the procedure that will cycle through
the employee's and if they don't meet certain criteria, skip the
printing of certain reports.

I am not a programmer by any means but can generally understand code
and modify canned code to make it suit the tasks but for this project
I have not been able find a sample database that would help me
understand what I need to do.

Any help you can provide would be appreciated.

Thanks.

[google]

I know I'm not alone in looking for a solution for a task such as what
I have listed, emailing the reports is not an option as the employee
must sign the paper report... I know basically what is needed will be
like:

For Each Selected Record
Query if the employee requires the report
Print the report for the current record
Next Record

This project is not funded as I work for a government organization and
unless people want to pay more taxes to allow for larger budgets I'm
stuck trying to work through this on my own with any assistance I can
get via the web.

To reduce the resources needed to complete the tasks in my office, I
volunteer my after hours time developing things such as this database
- any useful assistance is greatly appreciated.

[lyle fairfield]

It's Canada, right? Let me know which government organization it is
and I'll send a cheque to the Ministry right away! I'm completely
confident that the government and its minions spend every one of my
tax dollars carefully, os if there's not enough, just let me know.

On Jun 14, 3:15*pm, [e-mail] wrote:

I know I'm not alone in looking for a solution for a task such as what
I have listed, emailing the reports is not an option as the employee
must sign the paper report... I know basically what is needed will be
like:

For Each Selected Record
Query if the employee requires the report
Print the report for the current record
Next Record

This project is not funded as I work for a government organization and
unless people want to pay more taxes to allow for larger budgets I'm
stuck trying to work through this on my own with any assistance I can
get via the web.

[Salad]

go****@infoland.ca wrote:

I am trying to automate the way reports are printed in an Access 2003
database - I have ~200 records (people) who require between 5 and 10
customized reports (depending on values within certain fields) -
currently I print each report by selecting all employees and printing
one report at a time - once all reports are printed, I manually
collate the packages - I would like to be able to have a combo box (or
other option) highlight the required employees, press one button and
have all related reports print for the first employee, then move on to
the second employee, etc - thus each customized package will be
collated on the printer and all I have to do is add a paper clip.

I would use a combo box that displays all employees alphabetically
showing their name and employee number - once selected their employee
number (primary key) is used to select the report. I have looked at
using a macro to select the list of 10 reports, but do not have enough
experience to know how to code the procedure that will cycle through
the employee's and if they don't meet certain criteria, skip the
printing of certain reports.

I am not a programmer by any means but can generally understand code
and modify canned code to make it suit the tasks but for this project
I have not been able find a sample database that would help me
understand what I need to do.

Any help you can provide would be appreciated.

Thanks.

I'll toss out some starter stuff. I might create a table, called
AppReports. It would have 2 fields (or more). RptID type Autonumber
and ReportDescription type text. I'd then enter your 10 reports.

I think a combo would not suit your needs. I'd use a listbox.

Since you didn't provide much information I might first create two list
boxes. The first listbox, ListEmployee, would have 2 fields, the
employeeid and the employee name. I'd set it (under data tab) to Simple
or Extended (see help for the difference by pressing F1 on that property
row).

The second listbox, ListReports, would list the Reports from table
AppReports. Set to simple or extended as well.

Then have a command button to print the reports. I don't do macros.

The event procedure for the command button might start off like this

Private Sub CommandReport_Click()
On Error GoTo Err_CommandReport_Click
Dim varEmp As Variant 'pointer to selected employees
Dim varRpt As Variant 'pointer to selected reports

Dim lngEmpID As Long 'in case you need employee id
Dim strName As String 'in case you need employee name

Dim strReport As String 'var to hold the report name

'cycle thru emp list and printing reports for
'each employee selected.
For Each varEmp In Me.ListEmployees.ItemsSelected

'1st col is EmpID, 2nd is EmpName
lngEmpID = Me.ListEmployees.Column(0, varEmp)
strName = Me.ListEmployees.Column(1, varEmp)

'cycle thru the rpts list, printing each report selected
For Each varRpt In Me.ListReports.ItemsSelected

'get each report name selected to print
strReport = Me.ListReports.Column(1, varRpt)

DoCmd.OpenReport strReport

Next varRpt
Next varEmp

Exit_CommandReport_Click:
Exit Sub

Err_CommandReport_Click:
MsgBox Err.Description
Resume Exit_CommandReport_Click

End Sub

[Albert D. Kallal]

You can build/use the "where" clause, and base the reports sql *without* any
parameters.

So, you can build a un-bound form (a un-bound form is a form that is NOT
attached to a table - these forms are typically designed for user interface
face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:
dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar controls on the screen. The code
could be:
dim strWhere as string
dim strStartDate as string
dim strEndDate as string
strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[google]

Thank you very much for a solution that looks like it will answer my
problem.

[google]

Thanks Albert, your solution will be ideal for dealing with an
enhancement that has been on the back burner as well. Have a great
weekend!