Generating different type of Reports

[ChuckW]

Hi,

I am working on an application for a design team. They
have three different types of Jobs: Graphic Design, Web
Design and Photography. I created three different types
of forms for each job type in which they enter various
kinds of information about the job. The data from these
forms gets fed into a series of tables. The main table is
called the Projects table. The forms are based on
queries. Some of the fields for the three types of jobs
are the same (i.e. Contact Name, Company) while some are
different (Type of Film is for photography only. The is a
variable.

There is a field called Job Type which denotes the three
types of Jobs. There is also a job number field which is
sequential. Job 100 might be photography, 101 might be
web design, 102 might be Graphic design.

What I want to do is to create a button on a form that
runs a parameter query and creates a report. I know how
to do this in order to create one report. But I want to
create a possibility of three reports. When the user
clicks on a button they will be asked for a job number,
they plug this in and a Photography report is created. It
only has fields relevent to photogprahy. Does anyone know
who to create reports that will vary based on job type?

Thanks,

Chuck W

 

[Steve Schapel]

Chuck,

My suggestion is to scrap the Parameter Query idea, and instead put an
unbound option group or combobox, and another unbound combobox, on the
form. The option group or first combobox will allow a selection of
one of the three types of jobs. The second combobox will only list
the Job Numbers of those jobs of the selected type (you can still just
type the number in, but if you set its LimitToList property to Yes, it
will only accept one of the listed job numbers, which will possibly
help prevent a mistake). Then, on the AfterUpdate event of the Job
Number combobox, print the report, using code to determine which
report based on the entry in the Job Type selection.

Another approach to this situation, is to just have one report,
designed so it caters to all three Job Types, and using code on the
report sections' Format event to hide or display the applicable
controls, depending on the value of the Job Type.

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Chuck,

Instead of using the Project table as the RowSource of the second
combobox, make a query for this. The query will be based on the
Project table, but in the criteria of the ProjectType field, you will
put something like...
[Forms]![create_job_sheet]![NameOfFirstCombo]

To activate the printing of the report from your command button, you
will need to use either a macro or VBA code on the button's Click
event. In both cases you will need to refer to the entry in the first
combobox. In a macro, you would do this in the Condition column of
the Macro design (if you can't see this, select Conditions from the
View menu). For example, you might enter the condition like this...
[FirstCombobox]="Web Design"
.... and then use an OpenReport macro action, and nomimate the required
report name. Repeat for the other two. If you use VBA code, there
are various ways to write it, but here's an example...
Dim ReportName As String
Select Case Me.NameOfFirstCombobox
Case "Web Design"
ReportName = "Name Of Your Web Report"
Case "Graphics"
ReportName = "Name Of Your Graphics Report"
Case "Photography"
ReportName = "Name Of Your Photo Report"
End Select
DoCmd.OpenReport ReportName

- Steve Schapel, Microsoft Access MVP