Table design and relationships for cascading comboboxes

I am trying to put together a database that will collect nonconformance
data, analyze the data, and generate reports.

This database currently is set up with 4 tables:

[Table1]
The main table contains records that would be used to collect and record a
unique nonconformance. I have an autonumber field as the unique identifier
for the record. A key report (Nonconformance Report) would allow the user to
print out everything collected up to the current time for this record.

[TableFailureType]
Includes 2 fields;
“Type_ID� (Autonumber – Primary Key)
"Failure_Type" (Text – Electrical, Mechanical, Optical)

[TableFailureMode]
Includes 3 fields
“Mode_ID� (Autonumber – Primary Key)
"Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Type_ID� (Number)

[TableFailureSubMode]
Includes 3 fields
“SubMode_ID� (Autonumber – Primary Key)
"Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Mode_ID� (Number)

I have been able to design a basic form [Form1] where I can enter data into
[Table1]. I also have combo boxes that can select the data from the other
smaller tables and place the result into [Table1]. I would like to have these
combo boxes select an item (i.e. Electrical) from the "TableFailureType"
table and have it only display those "Failure_Mode" items from
[TableFailureMode] that are associated to the “Electrical"
"TableFailureType". From there I would like to use the [Failure_Submode]
combo box and only have those submodes displayed that are associated to the
selected failure mode. The result should be placed in the appropriate field
of the record from [Table1] so they can be used in reports.

I have tried 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/ac...aspx?pid=CL100
I have also reviewed a number of other posts in the Access forums. So far I
am lost.

Looking at just the combo box tied to the FailureMode I have the following
entered in the rowsource field:

"SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type))
ORDER BY TableFailureMode.Failure_Mode;"

When I try to use the form I receive the following error message:
The record source “~sq_cForm1~sq_cFailureMode� specified on this form or
report does not exist.

At the current time I am not sure if I have the tables set up properly. I am
also not sure how to set up the relationships. Any help would be appreciated.

Thanks in advance
Dave

Hi Dave,

That query syntax can be tricky, can't it?

Access does not work well with spaces between words. The error message is
probably in response to empty spaces. If your field names have empty spaces
then put the names in brackets and leave out the _.

You can try this:
SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type]))
ORDER BY [TableFailureMode].[Failure Mode];

If you have those _ lines in your field names then I would just put
everything in brackets any to see it that works.

Hope it helps.
Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com


"Access Newbee Dave" wrote:


I am trying to put together a database that will collect nonconformance
data, analyze the data, and generate reports.

This database currently is set up with 4 tables:

[Table1]
The main table contains records that would be used to collect and record a
unique nonconformance. I have an autonumber field as the unique identifier
for the record. A key report (Nonconformance Report) would allow the user to
print out everything collected up to the current time for this record.

[TableFailureType]
Includes 2 fields;
“Type_ID� (Autonumber – Primary Key)
"Failure_Type" (Text – Electrical, Mechanical, Optical)

[TableFailureMode]
Includes 3 fields
“Mode_ID� (Autonumber – Primary Key)
"Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Type_ID� (Number)

[TableFailureSubMode]
Includes 3 fields
“SubMode_ID� (Autonumber – Primary Key)
"Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Mode_ID� (Number)

I have been able to design a basic form [Form1] where I can enter data into
[Table1]. I also have combo boxes that can select the data from the other
smaller tables and place the result into [Table1]. I would like to have these
combo boxes select an item (i.e. Electrical) from the "TableFailureType"
table and have it only display those "Failure_Mode" items from
[TableFailureMode] that are associated to the “Electrical"
"TableFailureType". From there I would like to use the [Failure_Submode]
combo box and only have those submodes displayed that are associated to the
selected failure mode. The result should be placed in the appropriate field
of the record from [Table1] so they can be used in reports.

I have tried 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/ac...aspx?pid=CL100
I have also reviewed a number of other posts in the Access forums. So far I
am lost.

Looking at just the combo box tied to the FailureMode I have the following
entered in the rowsource field:

"SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type))
ORDER BY TableFailureMode.Failure_Mode;"

When I try to use the form I receive the following error message:
The record source “~sq_cForm1~sq_cFailureMode� specified on this form or
report does not exist.

At the current time I am not sure if I have the tables set up properly. I am
also not sure how to set up the relationships. Any help would be appreciated.

Thanks in advance
Dave

Hi Dave,

Be sure to bracket the criteria of your Where clause--that could be where
the error is coming from if you do have the _ lines in your field names.

It should look like this.

WHERE (((TableFailureMode.Type_ID)= [Forms]![Form1]![Failure_Type]))

Hunter57

"Access Newbee Dave" wrote:


I am trying to put together a database that will collect nonconformance
data, analyze the data, and generate reports.

This database currently is set up with 4 tables:

[Table1]
The main table contains records that would be used to collect and record a
unique nonconformance. I have an autonumber field as the unique identifier
for the record. A key report (Nonconformance Report) would allow the user to
print out everything collected up to the current time for this record.

[TableFailureType]
Includes 2 fields;
“Type_ID� (Autonumber – Primary Key)
"Failure_Type" (Text – Electrical, Mechanical, Optical)

[TableFailureMode]
Includes 3 fields
“Mode_ID� (Autonumber – Primary Key)
"Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Type_ID� (Number)

[TableFailureSubMode]
Includes 3 fields
“SubMode_ID� (Autonumber – Primary Key)
"Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Mode_ID� (Number)

I have been able to design a basic form [Form1] where I can enter data into
[Table1]. I also have combo boxes that can select the data from the other
smaller tables and place the result into [Table1]. I would like to have these
combo boxes select an item (i.e. Electrical) from the "TableFailureType"
table and have it only display those "Failure_Mode" items from
[TableFailureMode] that are associated to the “Electrical"
"TableFailureType". From there I would like to use the [Failure_Submode]
combo box and only have those submodes displayed that are associated to the
selected failure mode. The result should be placed in the appropriate field
of the record from [Table1] so they can be used in reports.

I have tried 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/ac...aspx?pid=CL100
I have also reviewed a number of other posts in the Access forums. So far I
am lost.

Looking at just the combo box tied to the FailureMode I have the following
entered in the rowsource field:

"SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type))
ORDER BY TableFailureMode.Failure_Mode;"

When I try to use the form I receive the following error message:
The record source “~sq_cForm1~sq_cFailureMode� specified on this form or
report does not exist.

At the current time I am not sure if I have the tables set up properly. I am
also not sure how to set up the relationships. Any help would be appreciated.

Thanks in advance
Dave

Hunter57, thanks for your response. It still looks like I am stuck though.

The underscores (_) indicated below are in fact underscores in the names and
not an indication of spaces. I used no spaces in my naming conventions.
I entered the following:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

I now receive the following pop-up error message.

Enter Parameter Value
Forms!Form1!Failure_Type
(There is an entry field and two buttons OK and Cancel in this popup
window)


Regardless of how I close the box when I click the dropdown arrow for the
combo box I see one row with three blank fields.

Hopefully my explaination is understood. Again, thanks in advance for any
help.

Dave


"Hunter57" wrote:

Hi Dave,

That query syntax can be tricky, can't it?

Access does not work well with spaces between words. The error message is
probably in response to empty spaces. If your field names have empty spaces
then put the names in brackets and leave out the _.

You can try this:
SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type]))
ORDER BY [TableFailureMode].[Failure Mode];

If you have those _ lines in your field names then I would just put
everything in brackets any to see it that works.

Hope it helps.
Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com


"Access Newbee Dave" wrote:


I am trying to put together a database that will collect nonconformance
data, analyze the data, and generate reports.

This database currently is set up with 4 tables:

[Table1]
The main table contains records that would be used to collect and record a
unique nonconformance. I have an autonumber field as the unique identifier
for the record. A key report (Nonconformance Report) would allow the user to
print out everything collected up to the current time for this record.

[TableFailureType]
Includes 2 fields;
“Type_ID� (Autonumber – Primary Key)
"Failure_Type" (Text – Electrical, Mechanical, Optical)

[TableFailureMode]
Includes 3 fields
“Mode_ID� (Autonumber – Primary Key)
"Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Type_ID� (Number)

[TableFailureSubMode]
Includes 3 fields
“SubMode_ID� (Autonumber – Primary Key)
"Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Mode_ID� (Number)

I have been able to design a basic form [Form1] where I can enter data into
[Table1]. I also have combo boxes that can select the data from the other
smaller tables and place the result into [Table1]. I would like to have these
combo boxes select an item (i.e. Electrical) from the "TableFailureType"
table and have it only display those "Failure_Mode" items from
[TableFailureMode] that are associated to the “Electrical"
"TableFailureType". From there I would like to use the [Failure_Submode]
combo box and only have those submodes displayed that are associated to the
selected failure mode. The result should be placed in the appropriate field
of the record from [Table1] so they can be used in reports.

I have tried 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/ac...aspx?pid=CL100
I have also reviewed a number of other posts in the Access forums. So far I
am lost.

Looking at just the combo box tied to the FailureMode I have the following
entered in the rowsource field:

"SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type))
ORDER BY TableFailureMode.Failure_Mode;"

When I try to use the form I receive the following error message:
The record source “~sq_cForm1~sq_cFailureMode� specified on this form or
report does not exist.

At the current time I am not sure if I have the tables set up properly. I am
also not sure how to set up the relationships. Any help would be appreciated.

Thanks in advance
Dave

I should mention that I select "Electrical" in the "Failure_Type" combo box
and the error occurs when I try to select anything in the "Failure_Mode"
combo box.

"Access Newbee Dave" wrote:

Hunter57, thanks for your response. It still looks like I am stuck though.

The underscores (_) indicated below are in fact underscores in the names and
not an indication of spaces. I used no spaces in my naming conventions.
I entered the following:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

I now receive the following pop-up error message.

Enter Parameter Value
Forms!Form1!Failure_Type
(There is an entry field and two buttons OK and Cancel in this popup
window)


Regardless of how I close the box when I click the dropdown arrow for the
combo box I see one row with three blank fields.

Hopefully my explaination is understood. Again, thanks in advance for any
help.

Dave


"Hunter57" wrote:

Hi Dave,

That query syntax can be tricky, can't it?

Access does not work well with spaces between words. The error message is
probably in response to empty spaces. If your field names have empty spaces
then put the names in brackets and leave out the _.

You can try this:
SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type]))
ORDER BY [TableFailureMode].[Failure Mode];

If you have those _ lines in your field names then I would just put
everything in brackets any to see it that works.

Hope it helps.
Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com


"Access Newbee Dave" wrote:


I am trying to put together a database that will collect nonconformance
data, analyze the data, and generate reports.

This database currently is set up with 4 tables:

[Table1]
The main table contains records that would be used to collect and record a
unique nonconformance. I have an autonumber field as the unique identifier
for the record. A key report (Nonconformance Report) would allow the user to
print out everything collected up to the current time for this record.

[TableFailureType]
Includes 2 fields;
“Type_ID� (Autonumber – Primary Key)
"Failure_Type" (Text – Electrical, Mechanical, Optical)

[TableFailureMode]
Includes 3 fields
“Mode_ID� (Autonumber – Primary Key)
"Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Type_ID� (Number)

[TableFailureSubMode]
Includes 3 fields
“SubMode_ID� (Autonumber – Primary Key)
"Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Mode_ID� (Number)

I have been able to design a basic form [Form1] where I can enter data into
[Table1]. I also have combo boxes that can select the data from the other
smaller tables and place the result into [Table1]. I would like to have these
combo boxes select an item (i.e. Electrical) from the "TableFailureType"
table and have it only display those "Failure_Mode" items from
[TableFailureMode] that are associated to the “Electrical"
"TableFailureType". From there I would like to use the [Failure_Submode]
combo box and only have those submodes displayed that are associated to the
selected failure mode. The result should be placed in the appropriate field
of the record from [Table1] so they can be used in reports.

I have tried 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/ac...aspx?pid=CL100
I have also reviewed a number of other posts in the Access forums. So far I
am lost.

Looking at just the combo box tied to the FailureMode I have the following
entered in the rowsource field:

"SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type))
ORDER BY TableFailureMode.Failure_Mode;"

When I try to use the form I receive the following error message:
The record source “~sq_cForm1~sq_cFailureMode� specified on this form or
report does not exist.

At the current time I am not sure if I have the tables set up properly. I am
also not sure how to set up the relationships. Any help would be appreciated.

Thanks in advance
Dave

Hi Dave,

The error message suggests that there is a problem with the criteria or
parameter.

Have you checked the name of your combo box by opening the Properties sheet
of the combo box, clicking the "Other" Tab to make sure that the Name listed
there is the same as the name in the last bracket that reads Failure_Type
[Forms]![Form1]![Failure_Type] ? If the name of your combo box is Combo34 or
cboFailure_Type instead of Failure_Type that will make your query fail.

You need to make the names match by changing either the name in the brackets
or the name of the combo box.

If that is not the problem, then you may need to make this a Parameter
Query. Sometimes Access just doesn't work well with getting the creteria
from a Form. You can do this by opening your query in design view, click
Query on the Menu Bar up top, and select Parameters at the bottom of the
list. A grid like this opens up.
___________________________
|__Parameter__ |__Data Type__ |
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|


Insert your [Forms]![Form1]![Failure_Type] in the Parameter Column and
appropriately select either Text or Long Integer in the Data Type Column.

Save your query and pray that it works!

If none of this works, try posting your request for help again in the
Queries Forum. Hopefully one of the Access MVP's (an expert) will be able to
help you.

Also, please post back here and let me know if you are able to get your
query to work.

Hunter57

"Access Newbee Dave" wrote:

Hunter57, thanks for your response. It still looks like I am stuck though.

The underscores (_) indicated below are in fact underscores in the names and
not an indication of spaces. I used no spaces in my naming conventions.
I entered the following:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

I now receive the following pop-up error message.

Enter Parameter Value
Forms!Form1!Failure_Type
(There is an entry field and two buttons OK and Cancel in this popup
window)


Regardless of how I close the box when I click the dropdown arrow for the
combo box I see one row with three blank fields.

Hopefully my explaination is understood. Again, thanks in advance for any
help.

Dave

Hi Dave,

I just noticed something I should not have missed. It is very important--it
appears that you do not have Type_ID as one of the columns in your query--you
just have it listed as Criteria. You need to add Type_ID to your Query.

If you use the Query grid add it to your query. Then put the
[Forms]![Form1]![Failure_Type] under it in the criteria column.

The SQL should look something like this:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode],
[TableFailureMode].[Type_ID]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

Sorry I missed that,
Hunter57

"Access Newbee Dave" wrote:

I should mention that I select "Electrical" in the "Failure_Type" combo box
and the error occurs when I try to select anything in the "Failure_Mode"
combo box.

"Access Newbee Dave" wrote:

Hunter57, thanks for your response. It still looks like I am stuck though.

The underscores (_) indicated below are in fact underscores in the names and
not an indication of spaces. I used no spaces in my naming conventions.
I entered the following:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

I now receive the following pop-up error message.

Enter Parameter Value
Forms!Form1!Failure_Type
(There is an entry field and two buttons OK and Cancel in this popup
window)


Regardless of how I close the box when I click the dropdown arrow for the
combo box I see one row with three blank fields.

Hopefully my explaination is understood. Again, thanks in advance for any
help.

Dave


"Hunter57" wrote:

Hi Dave,

That query syntax can be tricky, can't it?

Access does not work well with spaces between words. The error message is
probably in response to empty spaces. If your field names have empty spaces
then put the names in brackets and leave out the _.

You can try this:
SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type]))
ORDER BY [TableFailureMode].[Failure Mode];

If you have those _ lines in your field names then I would just put
everything in brackets any to see it that works.

Hope it helps.
Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com


"Access Newbee Dave" wrote:


I am trying to put together a database that will collect nonconformance
data, analyze the data, and generate reports.

This database currently is set up with 4 tables:

[Table1]
The main table contains records that would be used to collect and record a
unique nonconformance. I have an autonumber field as the unique identifier
for the record. A key report (Nonconformance Report) would allow the user to
print out everything collected up to the current time for this record.

[TableFailureType]
Includes 2 fields;
“Type_ID� (Autonumber – Primary Key)
"Failure_Type" (Text – Electrical, Mechanical, Optical)

[TableFailureMode]
Includes 3 fields
“Mode_ID� (Autonumber – Primary Key)
"Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Type_ID� (Number)

[TableFailureSubMode]
Includes 3 fields
“SubMode_ID� (Autonumber – Primary Key)
"Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Mode_ID� (Number)

I have been able to design a basic form [Form1] where I can enter data into
[Table1]. I also have combo boxes that can select the data from the other
smaller tables and place the result into [Table1]. I would like to have these
combo boxes select an item (i.e. Electrical) from the "TableFailureType"
table and have it only display those "Failure_Mode" items from
[TableFailureMode] that are associated to the “Electrical"
"TableFailureType". From there I would like to use the [Failure_Submode]
combo box and only have those submodes displayed that are associated to the
selected failure mode. The result should be placed in the appropriate field
of the record from [Table1] so they can be used in reports.

I have tried 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/ac...aspx?pid=CL100
I have also reviewed a number of other posts in the Access forums. So far I
am lost.

Looking at just the combo box tied to the FailureMode I have the following
entered in the rowsource field:

"SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type))
ORDER BY TableFailureMode.Failure_Mode;"

When I try to use the form I receive the following error message:
The record source “~sq_cForm1~sq_cFailureMode� specified on this form or
report does not exist.

At the current time I am not sure if I have the tables set up properly. I am
also not sure how to set up the relationships. Any help would be appreciated.

Thanks in advance
Dave

The name at the top of the other tab in the property sheet of the combo box
I am trying to set up is "Failure_Mode". I tried to change the "WHERE"
statement as follows and still see the "ENTER PARAMETER VALUE" popup:


SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Mode]))
ORDER BY [TableFailureMode].[Failure_Mode];

I am not sure what you mean by making this a parameter query. Do I use the
"Create query in design view" wizard or is this done from the design view of
the form?

Thanks

Dave


"Hunter57" wrote:

Hi Dave,

The error message suggests that there is a problem with the criteria or
parameter.

Have you checked the name of your combo box by opening the Properties sheet
of the combo box, clicking the "Other" Tab to make sure that the Name listed
there is the same as the name in the last bracket that reads Failure_Type
[Forms]![Form1]![Failure_Type] ? If the name of your combo box is Combo34 or
cboFailure_Type instead of Failure_Type that will make your query fail.

You need to make the names match by changing either the name in the brackets
or the name of the combo box.

If that is not the problem, then you may need to make this a Parameter
Query. Sometimes Access just doesn't work well with getting the creteria
from a Form. You can do this by opening your query in design view, click
Query on the Menu Bar up top, and select Parameters at the bottom of the
list. A grid like this opens up.
___________________________
|__Parameter__ |__Data Type__ |
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|


Insert your [Forms]![Form1]![Failure_Type] in the Parameter Column and
appropriately select either Text or Long Integer in the Data Type Column.

Save your query and pray that it works!

If none of this works, try posting your request for help again in the
Queries Forum. Hopefully one of the Access MVP's (an expert) will be able to
help you.

Also, please post back here and let me know if you are able to get your
query to work.

Hunter57

"Access Newbee Dave" wrote:

Hunter57, thanks for your response. It still looks like I am stuck though.

The underscores (_) indicated below are in fact underscores in the names and
not an indication of spaces. I used no spaces in my naming conventions.
I entered the following:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

I now receive the following pop-up error message.

Enter Parameter Value
Forms!Form1!Failure_Type
(There is an entry field and two buttons OK and Cancel in this popup
window)


Regardless of how I close the box when I click the dropdown arrow for the
combo box I see one row with three blank fields.

Hopefully my explaination is understood. Again, thanks in advance for any
help.

Dave

Hi Hunter57,

I tried making the changes to the SQL exactly as indicated and still hit the
same "ENTER PARAMETER VALUE" popup. I am not sure what you mean by query
grid. Is this the SQL statement Query builder you get to from the 3 elipses
at the end of the rowsource field in the property sheet? If so I have the
following 3 fields: [Mode_ID]
[Failure_Mode]
[Type_ID]
All 3 are checked as "Show" and [Forms]![Form1]![Failure_Type] is in the
"Criteria" under the [Type_ID] field.

Thanks for all the ideas and help.
Dave

"Hunter57" wrote:

Hi Dave,

I just noticed something I should not have missed. It is very important--it
appears that you do not have Type_ID as one of the columns in your query--you
just have it listed as Criteria. You need to add Type_ID to your Query.

If you use the Query grid add it to your query. Then put the
[Forms]![Form1]![Failure_Type] under it in the criteria column.

The SQL should look something like this:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode],
[TableFailureMode].[Type_ID]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

Sorry I missed that,
Hunter57

"Access Newbee Dave" wrote:

I should mention that I select "Electrical" in the "Failure_Type" combo box
and the error occurs when I try to select anything in the "Failure_Mode"
combo box.

"Access Newbee Dave" wrote:

Hunter57, thanks for your response. It still looks like I am stuck though.

The underscores (_) indicated below are in fact underscores in the names and
not an indication of spaces. I used no spaces in my naming conventions.
I entered the following:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

I now receive the following pop-up error message.

Enter Parameter Value
Forms!Form1!Failure_Type
(There is an entry field and two buttons OK and Cancel in this popup
window)


Regardless of how I close the box when I click the dropdown arrow for the
combo box I see one row with three blank fields.

Hopefully my explaination is understood. Again, thanks in advance for any
help.

Dave


"Hunter57" wrote:

Hi Dave,

That query syntax can be tricky, can't it?

Access does not work well with spaces between words. The error message is
probably in response to empty spaces. If your field names have empty spaces
then put the names in brackets and leave out the _.

You can try this:
SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type]))
ORDER BY [TableFailureMode].[Failure Mode];

If you have those _ lines in your field names then I would just put
everything in brackets any to see it that works.

Hope it helps.
Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com


"Access Newbee Dave" wrote:


I am trying to put together a database that will collect nonconformance
data, analyze the data, and generate reports.

This database currently is set up with 4 tables:

[Table1]
The main table contains records that would be used to collect and record a
unique nonconformance. I have an autonumber field as the unique identifier
for the record. A key report (Nonconformance Report) would allow the user to
print out everything collected up to the current time for this record.

[TableFailureType]
Includes 2 fields;
“Type_ID� (Autonumber – Primary Key)
"Failure_Type" (Text – Electrical, Mechanical, Optical)

[TableFailureMode]
Includes 3 fields
“Mode_ID� (Autonumber – Primary Key)
"Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Type_ID� (Number)

[TableFailureSubMode]
Includes 3 fields
“SubMode_ID� (Autonumber – Primary Key)
"Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of
failure type))
“Mode_ID� (Number)

I have been able to design a basic form [Form1] where I can enter data into
[Table1]. I also have combo boxes that can select the data from the other
smaller tables and place the result into [Table1]. I would like to have these
combo boxes select an item (i.e. Electrical) from the "TableFailureType"
table and have it only display those "Failure_Mode" items from
[TableFailureMode] that are associated to the “Electrical"
"TableFailureType". From there I would like to use the [Failure_Submode]
combo box and only have those submodes displayed that are associated to the
selected failure mode. The result should be placed in the appropriate field
of the record from [Table1] so they can be used in reports.

I have tried 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/ac...aspx?pid=CL100
I have also reviewed a number of other posts in the Access forums. So far I
am lost.

Looking at just the combo box tied to the FailureMode I have the following
entered in the rowsource field:

"SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode
FROM TableFailureMode
WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type))
ORDER BY TableFailureMode.Failure_Mode;"

When I try to use the form I receive the following error message:
The record source “~sq_cForm1~sq_cFailureMode� specified on this form or
report does not exist.

At the current time I am not sure if I have the tables set up properly. I am
also not sure how to set up the relationships. Any help would be appreciated.

Thanks in advance
Dave

Hi Dave,

Before you try to make it a Parameter Query I saw something that will
definately cause a query to fail. You are using critera for a field that is
not included in your query.

This is your Criteria:
[TableFailureMode].[Type_ID]

But you do not have [TableFailureMode].[Type_ID] in the first part of your
query so Access will keep asking you for the criteria or parameter until you
take car of that.

First, make a copy of your database. (Just copy the file to another folder
or to your desktop.)

Open your database. In design view click on your Failure_Submode combo box
and then open the properties sheet. Click the Data Tab. In the RowSource
REPLACE what you have there now with the SQL below. Just copy and past it
in. Try that and let me know if that takes care of the problem.

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode],
TableFailureMode].[Type_ID]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Mode]))
ORDER BY [TableFailureMode].[Failure_Mode];

The only difference between this and what you have been using is that
TableFailureMode].[Type_ID] has been added to the SELECT part of the
statement.

Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com


"Access Newbee Dave" wrote:

The name at the top of the other tab in the property sheet of the combo box
I am trying to set up is "Failure_Mode". I tried to change the "WHERE"
statement as follows and still see the "ENTER PARAMETER VALUE" popup:


SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Mode]))
ORDER BY [TableFailureMode].[Failure_Mode];

I am not sure what you mean by making this a parameter query. Do I use the
"Create query in design view" wizard or is this done from the design view of
the form?

Thanks

Dave


"Hunter57" wrote:

Hi Dave,

The error message suggests that there is a problem with the criteria or
parameter.

Have you checked the name of your combo box by opening the Properties sheet
of the combo box, clicking the "Other" Tab to make sure that the Name listed
there is the same as the name in the last bracket that reads Failure_Type
[Forms]![Form1]![Failure_Type] ? If the name of your combo box is Combo34 or
cboFailure_Type instead of Failure_Type that will make your query fail.

You need to make the names match by changing either the name in the brackets
or the name of the combo box.

If that is not the problem, then you may need to make this a Parameter
Query. Sometimes Access just doesn't work well with getting the creteria
from a Form. You can do this by opening your query in design view, click
Query on the Menu Bar up top, and select Parameters at the bottom of the
list. A grid like this opens up.
___________________________
|__Parameter__ |__Data Type__ |
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|
|_____________|_____________|


Insert your [Forms]![Form1]![Failure_Type] in the Parameter Column and
appropriately select either Text or Long Integer in the Data Type Column.

Save your query and pray that it works!

If none of this works, try posting your request for help again in the
Queries Forum. Hopefully one of the Access MVP's (an expert) will be able to
help you.

Also, please post back here and let me know if you are able to get your
query to work.

Hunter57

"Access Newbee Dave" wrote:

Hunter57, thanks for your response. It still looks like I am stuck though.

The underscores (_) indicated below are in fact underscores in the names and
not an indication of spaces. I used no spaces in my naming conventions.
I entered the following:

SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode]
FROM TableFailureMode
WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type]))
ORDER BY [TableFailureMode].[Failure_Mode];

I now receive the following pop-up error message.

Enter Parameter Value
Forms!Form1!Failure_Type
(There is an entry field and two buttons OK and Cancel in this popup
window)


Regardless of how I close the box when I click the dropdown arrow for the
combo box I see one row with three blank fields.

Hopefully my explaination is understood. Again, thanks in advance for any
help.

Dave