Two combo boxes on a form

[Howard Brody]

I would do a couple of things differently.

First, rename your controls. [cboJobType] is a lot more
recognizable than [Combo47], especially if you want to use
it as a reference elsewhere in your database.

I usually use the wizard to build a ComboBox and select
the fields via a SQL string instead of setting a source
query.

So, I think I would end up with 2 ComboBoxes using a SQl
string insead of an existung query as the source:

[cboJobType]
RowSourceType: Table/Query
RowSource: SELECT [tblJobTypes].[jType], [tblJobTypes].
[jDescription] FROM tblJobTypes;

And

[cboJobNumber]
RowSourceType: Table/Query
RowSource: SELECT [tblJobNumberss].[JobNumber] FROM
tblJobNumbers WHERE [tblJobNumbers].[JobType]=[cboJobType];

This also assumes a table (tblJobTypes) to list job types
and another table (tblJobNumbers) that lists job numbers
and their job types, with a One-to-Many relation between
the two on the JobType field.

In the OnChange event for [cboJobTypes], requery
[cboJobNumbers]. You can do it in a macro or with the
following code:

Private Sub cboJobType_Change()
[cboJobNumbers].Requery
End Sub

As for your reports, build a seperate report for each type
of job (which it looks like you did) and rebuild your
macro with three lines, one for each value in
[cboJobTypes]:

Line1
Condition: [Forms]![frmFormName]![cboJobType]="G"
Command: OpenReport "rptJobReport-Graphic"

Line2
Condition: [Forms]![frmFormName]![cboJobType]="P"
Command: OpenReport "rptJobReport-Photography"

Line3
Condition: [Forms]![frmFormName]![cboJobType]="W"
Command: OpenReport "rptJobReport-WebDesign"


Hope this helps!

Howard Brody

-----Original Message-----
Hello,

I am working on an application for keeping track of
various kinds of projects. They are contained in a
projects folder with a job number being the unique
identifier. I am trying to create a form called
JIS_Select which will allow an end user to select from one
of three types of jobs (Photography, Graphic Art and Web
Design). After selecting the Job Type from the combo box
(combo47) I would then like to have a second combo box
(combo49) in which only the job numbers that correspond to
the job type selected from the first combo box to show
up. One the user selects a job number, I want to direct
them to one of three types of reports (a Photopgrahy
report, a web design report and a graphic arts report).


The first combo box (Combo47) is fed from a query called
ProjectType that contains two fields: 1) ProjectType which
is a one letter code for each project type and 2)
Description - the name of the project type. So w would be
the project type of web design would be the description
The second combo box (combo49) is fed from a query called
JobInformationSheets that queries the projects table and
has job number and the ProjectType (the Project Table and
ProjectType table are joined by the field ProjectType).
In the criteria field of ProjectType for this second
query, I put [forms]![JIS_Select]![combo47]. When I run
the JIS_Select form and select a value such as Web Design,
and
then run the query, I get all job numbers that are Web
Design projects.

When I tried create another combo box 49 (the second one)
for selecting my job numbers, the wizard stops me and
says "No value given for one or more paramters" meaning it
can see values in my query. I went into the properties of
combo box (combo49) and selected the JobInformationSheets
as my row source. A strange thing happens. When I run
the form and select a Job Type such as web design, the
second combo box will display all job numbers that are web
design. But when I go to change the job type to
photography, it give me the same web design job numbers.

Also, I created a macro called SelectReports with the
condition statement of combo47=Web Design and had it open
a web design report that I created. I placed the macro
select reports in the on click event in combo 49. But
when I run the report, I get a message saying "The object
doesn't contain the automation object Web Design. You
tried to run a VB procedure to set a property or method
for an object.

Any ideas as to what I am doing wrong?

Thanks,

Chuck


.