Repost - I'm REALLY stuck :-(

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks Ann

I probably didn't make myself clear, but I need to generate a separate report for each of the companies that are eligible, not one big report containing all of the eligible companies.

I want the report to run several times (based on how many companies are eligible from the "selection" query), and generate individual reports per company

I then need to print them separately (with headers, footer and page numbers), or export individual reports for emailing... (I can see another question when I get that far :-)

If it needs code, so be it, but I've never used it..

Hope to hear from you, or anyone else that may be able to help.

Mik


----- ann wrote: ----

Mik
your report has to be driven off a 2nd query
1st query identifies the companies for report
2nd query pulls data for repor

in 2nd query add in your 1st query and do an equal join
on the company identifier - name or id or whatever you
have to tie the 2 together

this way, when the 2nd query runs - it will only run
for the companies that are in the 1st query result

the 2nd query is then your data source for the repor


-----Original Message----
Hi,
Click to expand...
hopefully easy to answer, but I'm new to this so go easy
on me..parameter to which generates a report for a particular
company. I have a query that identify which companies are
eligible for a reportfor each of the companies selected by the query?
Preferably without code
 
Mike:

Yes, this will require code. In essence, the procedure is to open your
query which determine which companies are eligible and loop through it,
using its primary key Company ID to filter your report. To do this you need
to follow these steps.

1.) You'll need to adjust your report to eliminate any parameter that
prompts for the companyID

2.) In a general module create a function like this:

'----------begin code ----------------
Function PrintEligibles(Optional ManualCompany as Boolean = 0) as Boolean
On Error GoTo ErrHandler
'Manual CompanyID is set to -1 or True to prompt when you
'want to print this report manually for a single company.
'This code use DAO for its data access method. If the db is an Access
'2000 or 2002 db, then you may need to add a reference to the
'Microsoft DAO 3.6 library in the Tools->References option
'in the visual basic editor.
'Code assumes that the companyid is a number and not a text value
'---------------------------
Dim rsCompanies as DAO.Recordset
Dim dbCurrent as DAO.Database
Dim strReport as String
Dim strCompanyID as String

strReport = YourTargetReportName
If CompanyID = 0 Then
Set dbCurrent = CurrentDb()
Set rsCompanies = dbCurrent.OpenRecordset("Select Distinct " & _
"CompanyID FROM YourEligibleCompanyQuery", dbOpenSnapshot)
With rsCompanies
Do Until .EOF 'end of file
'use the company id from the recordset row as a filter on the
report.
DoCmd.OpenReport strReport, acViewNormal, , "[CompanyID] = " &
!CompanyID
DoEvents
.MoveNext 'go to the next record or eligible company id
Loop
.Close
End With
Set rsCompanies =Nothing
Set dbCurrent = Nothing
Else
'Manual Report
strCompanyID = InputBox ("Enter A Company ID To Print", "Print Company
Report")
If len(strCompanyID)>0 Then
DoCmd.OpenReport strReport, acViewNormal, , "[CompanyID] = " &
CLng(strCompanyID)
End if
End If
PrintEligibles = True 'Function result
Exit Process:
Exit Function
ErrHandler:
MsgBox "Error " & Err.Number & " " & Err.Description,16
Resume ExitProcess
End Function
'--------------end code -------------

Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg
 
Back
Top