Cascading Combo Boxes

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

Guest

I am trying to follow the instructions to set up a combo box based on the
results of a second box from...

http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CL100

Per the section "How to add criteria to the product query" I set "Row Source
Type" to Table/Query and click the 3 elipses in the "Row Source" field to get
to the query builder. I have two fields (columns) pulled from one table. The
Table name is "Failure Mode". The two fields are "Failure Type" and "Failure
Mode".

I entered the following statement...
"[Forms]![Failure Mode]![Failure Mode]" in the "Failure Mode" criteria. (I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>.

After I exit and try to open the form I receive the following Pop up
message...

"ENTER PARAMETER VALUE"
"Forms!Failure Mode!Failure Mode"

What am I doing wrong?

Thanks in Advance.
Dave
 
Access interprets a space to mean that you've finished with one thing and
moved on.

Your Forms!... expression has a space in the middle.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Dave:

I think there is an inconsistency in your post. On the one hand you say "I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>". But you are restricting the
combo box (bound to the Failure Mode column I assume) on the Failure Mode
column rather than on the Failure Type column. I'd have thought the
parameter should be on the Failure Type column, i.e. [Forms]![Failure
Type]![Failure Type], leaving the 'show' checkbox for the column unchecked as
you don't need to return it in the query, only the Failure Mode column. In
SQL the RowSource property would be something like:

SELECT [Failure Mode]
FROM [Failure Mode]
WHERE [Failure Type] =
[Forms]![Failure Type]![Failure Type]
ORDER BY [Failure Mode];

This does assume you've used the same names for the combo boxes as the
columns to which they are bound. BTW I'd strongly recommend against using
the same name for a table and one of its columns. It’s a potential source of
confusion or worse. Wherever possible I follow the convention advocated by
Joe Celko of using plural or collective nouns for table names and singular
nouns for column names. I also never use spaces in table or column names, so
the table name would be FailureModes and the column FailureMode.

Ken Sheridan
Stafford, England
 
After reviewing your post I made some changes...

Tables and Fields are renamed

Table Name: "TableFailureType"
Includes 1 Field: "Failure_Type" (Electrical, Mechanical, Optical...)
Failure_Type is the primary key

Table Name: "TableFailureMode"
Includes 3 Fields
"Failure_Mode" (Resistor, Capacitor, etc... (a sub-category of failure type))
"Failure_Type" (Electrical, Mechanical, Optical...)
"Index" (autonumber - Not sure if I even need this)
There is no primary key assigned

I would like to select "Electrical" from the "TableFailureType" table and
have it only display those "Failure_Mode" items where the associated
"Failure_Type" items are Electrical" "TableFailureMode"

I used [Forms]![TableFailureMode]![Failure_Type] in the criteria field of
the query builder with a rowsource result of:

SELECT TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Failure_Mode)=
[Forms]![TableFailureMode]![Failure_Type]));

When I try to enter anything in the form field I receive the popup message:
"Enter Parameter Value"
"Forms!TableFailureMode!Failure_Type"

Your help is sincerly appreciated.
Dave

Ken Sheridan said:
Dave:

I think there is an inconsistency in your post. On the one hand you say "I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>". But you are restricting the
combo box (bound to the Failure Mode column I assume) on the Failure Mode
column rather than on the Failure Type column. I'd have thought the
parameter should be on the Failure Type column, i.e. [Forms]![Failure
Type]![Failure Type], leaving the 'show' checkbox for the column unchecked as
you don't need to return it in the query, only the Failure Mode column. In
SQL the RowSource property would be something like:

SELECT [Failure Mode]
FROM [Failure Mode]
WHERE [Failure Type] =
[Forms]![Failure Type]![Failure Type]
ORDER BY [Failure Mode];

This does assume you've used the same names for the combo boxes as the
columns to which they are bound. BTW I'd strongly recommend against using
the same name for a table and one of its columns. It’s a potential source of
confusion or worse. Wherever possible I follow the convention advocated by
Joe Celko of using plural or collective nouns for table names and singular
nouns for column names. I also never use spaces in table or column names, so
the table name would be FailureModes and the column FailureMode.

Ken Sheridan
Stafford, England

Access Newbee Dave said:
I am trying to follow the instructions to set up a combo box based on the
results of a second box from...

http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CL100

Per the section "How to add criteria to the product query" I set "Row Source
Type" to Table/Query and click the 3 elipses in the "Row Source" field to get
to the query builder. I have two fields (columns) pulled from one table. The
Table name is "Failure Mode". The two fields are "Failure Type" and "Failure
Mode".

I entered the following statement...
"[Forms]![Failure Mode]![Failure Mode]" in the "Failure Mode" criteria. (I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>.

After I exit and try to open the form I receive the following Pop up
message...

"ENTER PARAMETER VALUE"
"Forms!Failure Mode!Failure Mode"

What am I doing wrong?

Thanks in Advance.
Dave
Click to expand...
Click to expand...
 
Dave:

Firstly you need to restrict the combo box on the Failure_Type column not
the Failure_Mode column, so the RowSource property should (not allowing for
any corrections to the parameter as below) be:

SELECT TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Failure_Type)=
[Forms]![TableFailureMode]![Failure_Type]));

With the parameter, [Forms]![TableFailureMode]![Failure_Type], which you've
used the name of the form would be TableFailureMode, and the name of the
combo box in which you select the failure type would be Failure_Type. If
either the form or the control have different names Access will prompt for
the parameter, which is what's happening. Check the Name properties of both
the form and the combo box and amend the parameter in the RowSource query if
necessary.

Ken Sheridan
Stafford, England

Access Newbee Dave said:
After reviewing your post I made some changes...

Tables and Fields are renamed

Table Name: "TableFailureType"
Includes 1 Field: "Failure_Type" (Electrical, Mechanical, Optical...)
Failure_Type is the primary key

Table Name: "TableFailureMode"
Includes 3 Fields
"Failure_Mode" (Resistor, Capacitor, etc... (a sub-category of failure type))
"Failure_Type" (Electrical, Mechanical, Optical...)
"Index" (autonumber - Not sure if I even need this)
There is no primary key assigned

I would like to select "Electrical" from the "TableFailureType" table and
have it only display those "Failure_Mode" items where the associated
"Failure_Type" items are Electrical" "TableFailureMode"

I used [Forms]![TableFailureMode]![Failure_Type] in the criteria field of
the query builder with a rowsource result of:

SELECT TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Failure_Mode)=
[Forms]![TableFailureMode]![Failure_Type]));

When I try to enter anything in the form field I receive the popup message:
"Enter Parameter Value"
"Forms!TableFailureMode!Failure_Type"

Your help is sincerly appreciated.
Dave

Ken Sheridan said:
Dave:

I think there is an inconsistency in your post. On the one hand you say "I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>". But you are restricting the
combo box (bound to the Failure Mode column I assume) on the Failure Mode
column rather than on the Failure Type column. I'd have thought the
parameter should be on the Failure Type column, i.e. [Forms]![Failure
Type]![Failure Type], leaving the 'show' checkbox for the column unchecked as
you don't need to return it in the query, only the Failure Mode column. In
SQL the RowSource property would be something like:

SELECT [Failure Mode]
FROM [Failure Mode]
WHERE [Failure Type] =
[Forms]![Failure Type]![Failure Type]
ORDER BY [Failure Mode];

This does assume you've used the same names for the combo boxes as the
columns to which they are bound. BTW I'd strongly recommend against using
the same name for a table and one of its columns. It’s a potential source of
confusion or worse. Wherever possible I follow the convention advocated by
Joe Celko of using plural or collective nouns for table names and singular
nouns for column names. I also never use spaces in table or column names, so
the table name would be FailureModes and the column FailureMode.

Ken Sheridan
Stafford, England

Access Newbee Dave said:
I am trying to follow the instructions to set up a combo box based on the
results of a second box from...

http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CL100

Per the section "How to add criteria to the product query" I set "Row Source
Type" to Table/Query and click the 3 elipses in the "Row Source" field to get
to the query builder. I have two fields (columns) pulled from one table. The
Table name is "Failure Mode". The two fields are "Failure Type" and "Failure
Mode".

I entered the following statement...
"[Forms]![Failure Mode]![Failure Mode]" in the "Failure Mode" criteria. (I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>.

After I exit and try to open the form I receive the following Pop up
message...

"ENTER PARAMETER VALUE"
"Forms!Failure Mode!Failure Mode"

What am I doing wrong?

Thanks in Advance.
Dave
Click to expand...
Click to expand...
Click to expand...
 
Hi Ken,

I sincerely appreciate the help you have been providing. You have provided
me a great learning experience.
I am still stuck and beginning to think I am over my head. I entered the
code you provided into the "rowsource of the "Failure_Mode" combo box:

SELECT TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Failure_Type)=
[Forms]![TableFailureMode]![Failure_Type]));

I still get the error message previously discussed so I believe I am still
missing something.

I am wondering if my problem might be the table I selected when setting up
the form. i.e. I have a master table "Table1" where the results of this
parameter should go. When I created the form I wanted to have the results
placed in the "Table1:Failure_Mode" field. I therefore selected the
"Table1:Failure_Mode" field when creating the combobox for this form. This is
the combo box I am trying to work with. At this point I am not sure where the
form might be trying to save the results and if this might be my problem.

Where I am unfamiliar with this program code (Is this visual basic?) I am
guessing that the data is coming "FROM TableFailureMode"
(How do I define which fields the data comes from? This particular table has
3 fields. When I bypass the error popup I get one blank row that appears.
This row looks like it is long enough to display all three fields.)

If the criteria used to determine what goes into the field is:
"WHERE (((TableFailureMode.Failure_Type)=
[Forms]![TableFailureMode]![Failure_Type]));"
(Where the "TABLE" Failure_Type is equal to the "FORM" Failure_Type?)

Does this mean that the results of the query go into the "SELECT"ed table
and field "TableFailureMode.Failure_Mode"?

Also, if you could point me to where I can get a basic overview of this
program code I would appreciate it.

Dave Thuillier
New Hampshire, USA

Ken Sheridan said:
Dave:

Firstly you need to restrict the combo box on the Failure_Type column not
the Failure_Mode column, so the RowSource property should (not allowing for
any corrections to the parameter as below) be:

SELECT TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Failure_Type)=
[Forms]![TableFailureMode]![Failure_Type]));

With the parameter, [Forms]![TableFailureMode]![Failure_Type], which you've
used the name of the form would be TableFailureMode, and the name of the
combo box in which you select the failure type would be Failure_Type. If
either the form or the control have different names Access will prompt for
the parameter, which is what's happening. Check the Name properties of both
the form and the combo box and amend the parameter in the RowSource query if
necessary.

Ken Sheridan
Stafford, England

Access Newbee Dave said:
After reviewing your post I made some changes...

Tables and Fields are renamed

Table Name: "TableFailureType"
Includes 1 Field: "Failure_Type" (Electrical, Mechanical, Optical...)
Failure_Type is the primary key

Table Name: "TableFailureMode"
Includes 3 Fields
"Failure_Mode" (Resistor, Capacitor, etc... (a sub-category of failure type))
"Failure_Type" (Electrical, Mechanical, Optical...)
"Index" (autonumber - Not sure if I even need this)
There is no primary key assigned

I would like to select "Electrical" from the "TableFailureType" table and
have it only display those "Failure_Mode" items where the associated
"Failure_Type" items are Electrical" "TableFailureMode"

I used [Forms]![TableFailureMode]![Failure_Type] in the criteria field of
the query builder with a rowsource result of:

SELECT TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Failure_Mode)=
[Forms]![TableFailureMode]![Failure_Type]));

When I try to enter anything in the form field I receive the popup message:
"Enter Parameter Value"
"Forms!TableFailureMode!Failure_Type"

Your help is sincerly appreciated.
Dave

Ken Sheridan said:
Dave:

I think there is an inconsistency in your post. On the one hand you say "I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>". But you are restricting the
combo box (bound to the Failure Mode column I assume) on the Failure Mode
column rather than on the Failure Type column. I'd have thought the
parameter should be on the Failure Type column, i.e. [Forms]![Failure
Type]![Failure Type], leaving the 'show' checkbox for the column unchecked as
you don't need to return it in the query, only the Failure Mode column. In
SQL the RowSource property would be something like:

SELECT [Failure Mode]
FROM [Failure Mode]
WHERE [Failure Type] =
[Forms]![Failure Type]![Failure Type]
ORDER BY [Failure Mode];

This does assume you've used the same names for the combo boxes as the
columns to which they are bound. BTW I'd strongly recommend against using
the same name for a table and one of its columns. It’s a potential source of
confusion or worse. Wherever possible I follow the convention advocated by
Joe Celko of using plural or collective nouns for table names and singular
nouns for column names. I also never use spaces in table or column names, so
the table name would be FailureModes and the column FailureMode.

Ken Sheridan
Stafford, England

:

I am trying to follow the instructions to set up a combo box based on the
results of a second box from...

http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CL100

Per the section "How to add criteria to the product query" I set "Row Source
Type" to Table/Query and click the 3 elipses in the "Row Source" field to get
to the query builder. I have two fields (columns) pulled from one table. The
Table name is "Failure Mode". The two fields are "Failure Type" and "Failure
Mode".

I entered the following statement...
"[Forms]![Failure Mode]![Failure Mode]" in the "Failure Mode" criteria. (I
am looking to select a <Failure Type> and limit my <Failure Mode> selections
to only those items under one <Failure Type>.

After I exit and try to open the form I receive the following Pop up
message...

"ENTER PARAMETER VALUE"
"Forms!Failure Mode!Failure Mode"

What am I doing wrong?

Thanks in Advance.
Dave
Click to expand...
Click to expand...
Click to expand...
 
Back
Top