Table design and relationships for cascading comboboxes

[Access Newbee Dave]

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/access/HA011730581033.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]

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

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/access/HA011730581033.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]

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

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/access/HA011730581033.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

 

[Access Newbee 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

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

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/access/HA011730581033.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

 

[Access Newbee 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.

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,

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

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/access/HA011730581033.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]

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

 

[Hunter57]

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

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.

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,

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


:


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/access/HA011730581033.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

 

[Access Newbee 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

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

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

 

[Access Newbee 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

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

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.

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,

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


:


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/access/HA011730581033.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]

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

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

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

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]

Hi Dave,

Is the form you get your critera from open?

I can email screenshots to you if you Email me at pwood57 at gmail dot com
so I can send you an email.

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

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

 

[Access Newbee Dave]

Hi Hunter57,

I entered the SQL that you mentioned and received a syntax error. I noted a
missing "[" at the beginning of the second line. I fixed it and still get the
"ENTER PARAMETER VALUE"
"Forms!Form1!Failure_Type" popup.

I am wondering if I need to go back to the basics...
Before looking at the SQL, Do I have the tables set up properly?
If the tables are set up properly do I need to provide for object
dependancies or relationships?
Do I need to set up some form of query or does the SQL provide the
appropriate capability to display only those items as categorized by the
first combo box?

Thanks again.
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

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

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

:

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]

Hi Dave,

I am sorry you are having so much trouble.

Relationships should play no part in this problem because your SQL indicates
that all of the data in your query is coming from just one table. If that is
not the case, and your data in the query should be coming from more than one
table, then you need to add that table name to the FROM clause of your query
like this:

FROM TableFailureMode, myothertablename

Question: Is Form1 open and in form view when you run this query? If not,
Access will produce the exact response you are seeing. Form1 must be open
and in Form view (the normal view) or the query cannot find the criteria.

I use the method you are trying often. I have no problems with it.

You might want to try the Queries Forum. You can find much better help than
me there.

Wish you the best,
Hunter57

Hi Hunter57,

I entered the SQL that you mentioned and received a syntax error. I noted a
missing "[" at the beginning of the second line. I fixed it and still get the
"ENTER PARAMETER VALUE"
"Forms!Form1!Failure_Type" popup.

I am wondering if I need to go back to the basics...
Before looking at the SQL, Do I have the tables set up properly?
If the tables are set up properly do I need to provide for object
dependancies or relationships?
Do I need to set up some form of query or does the SQL provide the
appropriate capability to display only those items as categorized by the
first combo box?

Thanks again.
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

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


:

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

:

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

 

[Access Newbee Dave]

Hi Hunter 57,

Yes the form is open in form view when I run the query.

You mentioned that the SQL indicates that the data is all coming from one
table. That may be where my problem lies.

I am dealing with three tables for this query:

[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)

I select a "Failure_Type" (i.e. electrical) from [TableFailureType] and save
it in a field in [Table1]. I am then trying to select a "Failure_Mode" from
[TableFailureMode] based on my previous "FailureType" selection and save it
in a second field in [Table1].

Thanks again for all your help.

Hi Dave,

I am sorry you are having so much trouble.

Relationships should play no part in this problem because your SQL indicates
that all of the data in your query is coming from just one table. If that is
not the case, and your data in the query should be coming from more than one
table, then you need to add that table name to the FROM clause of your query
like this:

FROM TableFailureMode, myothertablename

Question: Is Form1 open and in form view when you run this query? If not,
Access will produce the exact response you are seeing. Form1 must be open
and in Form view (the normal view) or the query cannot find the criteria.

I use the method you are trying often. I have no problems with it.

You might want to try the Queries Forum. You can find much better help than
me there.

Wish you the best,
Hunter57

Hi Hunter57,

I entered the SQL that you mentioned and received a syntax error. I noted a
missing "[" at the beginning of the second line. I fixed it and still get the
"ENTER PARAMETER VALUE"
"Forms!Form1!Failure_Type" popup.

I am wondering if I need to go back to the basics...
Before looking at the SQL, Do I have the tables set up properly?
If the tables are set up properly do I need to provide for object
dependancies or relationships?
Do I need to set up some form of query or does the SQL provide the
appropriate capability to display only those items as categorized by the
first combo box?

Thanks again.
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


:

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


:

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

:

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]

Hi Dave,

If you have not discovered Access's Query Builder grid you don't know what
you are missing!

No wonder you could not get your query to work. You really need to learn
how to use Access Query Grid--it is a great help and you can make queries
without having to type in all of those table and field names. You just drag
and drop or doubleclick!

To open the query design grid.
1) Open your form in design view.

2) Right -click on the combo box that has your Query or SQL statement.

3) Select Properties at the bottom of the list.

4) Next click on the Data tab.

5) Find the row that says RowSource. To the far right of that, double-click
on the area just to the right of the white part. That should open the query
builder grid for you.

To add your other two tables, right click on the top window.

Select Add Table and a list of tables and queries appear.

Double-click on the the table you want to add.

Find the other table you need to add and double click on that.

Click Close.

Now you should be able to see all three tables in the top window. You
should also be able to see their relationships represented by lines
connecting the tables.

To add a field to your query, just click on a field name in one of the
tables and drag it to column you want it to be in the top row. Or you can
double-click on the field name and it will appear at the end of your last
used column.

To see what your query will produce, on the Menu Bar click on the datasheet
button or click View and then select Datasheet view.

You can try different things and see how they work.

Make sure that the Type_ID field is in one of the columns. If not, then add
it. If the [Forms]![Form1]![Failure_Mode] is not in the criteria row in that
column put it there. (You will find the row names to the far left of the
grid.

Have fun!

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

Hi Hunter 57,

Yes the form is open in form view when I run the query.

You mentioned that the SQL indicates that the data is all coming from one
table. That may be where my problem lies.

I am dealing with three tables for this query:

[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)

I select a "Failure_Type" (i.e. electrical) from [TableFailureType] and save
it in a field in [Table1]. I am then trying to select a "Failure_Mode" from
[TableFailureMode] based on my previous "FailureType" selection and save it
in a second field in [Table1].

Thanks again for all your help.

Hi Dave,

I am sorry you are having so much trouble.

Relationships should play no part in this problem because your SQL indicates
that all of the data in your query is coming from just one table. If that is
not the case, and your data in the query should be coming from more than one
table, then you need to add that table name to the FROM clause of your query
like this:

FROM TableFailureMode, myothertablename

Question: Is Form1 open and in form view when you run this query? If not,
Access will produce the exact response you are seeing. Form1 must be open
and in Form view (the normal view) or the query cannot find the criteria.

I use the method you are trying often. I have no problems with it.

You might want to try the Queries Forum. You can find much better help than
me there.

Wish you the best,
Hunter57

Hi Hunter57,

I entered the SQL that you mentioned and received a syntax error. I noted a
missing "[" at the beginning of the second line. I fixed it and still get the
"ENTER PARAMETER VALUE"
"Forms!Form1!Failure_Type" popup.

I am wondering if I need to go back to the basics...
Before looking at the SQL, Do I have the tables set up properly?
If the tables are set up properly do I need to provide for object
dependancies or relationships?
Do I need to set up some form of query or does the SQL provide the
appropriate capability to display only those items as categorized by the
first combo box?

Thanks again.
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


:

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


:

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

:

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

 

[Access Newbee Dave]

Hi Hunter,

I found the SQL Statement:Query builder. Thank you. I am not really sure
what this is showing me though. The best info I have stumbled across that
appears to relate to my situation is on the following page and doesn't appear
to discuss multiple tables.
http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CL100

I had one table displayed in the top window [TableFailureMode]. The lower
window had all three fields from this table displayed.
“Mode_ID†(Autonumber – Primary Key)
"Failure_Mode" (Text)
“Type_ID†(Number)

[Forms]![Form1]![Failure_Mode] is in the criteria row for the “Type_ID†field.

I added the other two tables ([Table1] and [TableFailureType]) to the top
window.
[Table1] does not have any relationship to either of the other tables.
There is a relationship between [TableFailureType] (1) and
[TableFailureMode] (many).

Now a couple of questions come to mind:
1 - Should there be a relationship between [Table1] and either (both?) of
the other two tables.
2 - What fields do I need to drag down from the new tables?

Thanks again.
Dave

Hi Dave,

If you have not discovered Access's Query Builder grid you don't know what
you are missing!

No wonder you could not get your query to work. You really need to learn
how to use Access Query Grid--it is a great help and you can make queries
without having to type in all of those table and field names. You just drag
and drop or doubleclick!

To open the query design grid.
1) Open your form in design view.

2) Right -click on the combo box that has your Query or SQL statement.

3) Select Properties at the bottom of the list.

4) Next click on the Data tab.

5) Find the row that says RowSource. To the far right of that, double-click
on the area just to the right of the white part. That should open the query
builder grid for you.

To add your other two tables, right click on the top window.

Select Add Table and a list of tables and queries appear.

Double-click on the the table you want to add.

Find the other table you need to add and double click on that.

Click Close.

Now you should be able to see all three tables in the top window. You
should also be able to see their relationships represented by lines
connecting the tables.

To add a field to your query, just click on a field name in one of the
tables and drag it to column you want it to be in the top row. Or you can
double-click on the field name and it will appear at the end of your last
used column.

To see what your query will produce, on the Menu Bar click on the datasheet
button or click View and then select Datasheet view.

You can try different things and see how they work.

Make sure that the Type_ID field is in one of the columns. If not, then add
it. If the [Forms]![Form1]![Failure_Mode] is not in the criteria row in that
column put it there. (You will find the row names to the far left of the
grid.

Have fun!

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

Hi Hunter 57,

Yes the form is open in form view when I run the query.

You mentioned that the SQL indicates that the data is all coming from one
table. That may be where my problem lies.

I am dealing with three tables for this query:

[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)

I select a "Failure_Type" (i.e. electrical) from [TableFailureType] and save
it in a field in [Table1]. I am then trying to select a "Failure_Mode" from
[TableFailureMode] based on my previous "FailureType" selection and save it
in a second field in [Table1].

Thanks again for all your help.

Hi Dave,

I am sorry you are having so much trouble.

Relationships should play no part in this problem because your SQL indicates
that all of the data in your query is coming from just one table. If that is
not the case, and your data in the query should be coming from more than one
table, then you need to add that table name to the FROM clause of your query
like this:

FROM TableFailureMode, myothertablename

Question: Is Form1 open and in form view when you run this query? If not,
Access will produce the exact response you are seeing. Form1 must be open
and in Form view (the normal view) or the query cannot find the criteria.

I use the method you are trying often. I have no problems with it.

You might want to try the Queries Forum. You can find much better help than
me there.

Wish you the best,
Hunter57

:

Hi Hunter57,

I entered the SQL that you mentioned and received a syntax error. I noted a
missing "[" at the beginning of the second line. I fixed it and still get the
"ENTER PARAMETER VALUE"
"Forms!Form1!Failure_Type" popup.

I am wondering if I need to go back to the basics...
Before looking at the SQL, Do I have the tables set up properly?
If the tables are set up properly do I need to provide for object
dependancies or relationships?
Do I need to set up some form of query or does the SQL provide the
appropriate capability to display only those items as categorized by the
first combo box?

Thanks again.
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


:

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


:

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

:

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]

Hi Dave,

You are making progress. You do need to have a relationship between the
tables. That is, no table should be without connections in your window.
Usually, the best fields to use are the Primary Key fields.

When you create a relationship in the query grid it exists there and no
where else. You don't have to worry about causing problems with your
database. So experiment all you want and see what results you have.

I could write more but I gotta go.

Best wishes,
Hunter57

Hi Hunter,

I found the SQL Statement:Query builder. Thank you. I am not really sure
what this is showing me though. The best info I have stumbled across that
appears to relate to my situation is on the following page and doesn't appear
to discuss multiple tables.
http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CL100

I had one table displayed in the top window [TableFailureMode]. The lower
window had all three fields from this table displayed.
“Mode_ID†(Autonumber – Primary Key)
"Failure_Mode" (Text)
“Type_ID†(Number)

[Forms]![Form1]![Failure_Mode] is in the criteria row for the “Type_ID†field.

I added the other two tables ([Table1] and [TableFailureType]) to the top
window.
[Table1] does not have any relationship to either of the other tables.
There is a relationship between [TableFailureType] (1) and
[TableFailureMode] (many).

Now a couple of questions come to mind:
1 - Should there be a relationship between [Table1] and either (both?) of
the other two tables.
2 - What fields do I need to drag down from the new tables?

Thanks again.
Dave

Hi Dave,

If you have not discovered Access's Query Builder grid you don't know what
you are missing!

No wonder you could not get your query to work. You really need to learn
how to use Access Query Grid--it is a great help and you can make queries
without having to type in all of those table and field names. You just drag
and drop or doubleclick!

To open the query design grid.
1) Open your form in design view.

2) Right -click on the combo box that has your Query or SQL statement.

3) Select Properties at the bottom of the list.

4) Next click on the Data tab.

5) Find the row that says RowSource. To the far right of that, double-click
on the area just to the right of the white part. That should open the query
builder grid for you.

To add your other two tables, right click on the top window.

Select Add Table and a list of tables and queries appear.

Double-click on the the table you want to add.

Find the other table you need to add and double click on that.

Click Close.

Now you should be able to see all three tables in the top window. You
should also be able to see their relationships represented by lines
connecting the tables.

To add a field to your query, just click on a field name in one of the
tables and drag it to column you want it to be in the top row. Or you can
double-click on the field name and it will appear at the end of your last
used column.

To see what your query will produce, on the Menu Bar click on the datasheet
button or click View and then select Datasheet view.

You can try different things and see how they work.

Make sure that the Type_ID field is in one of the columns. If not, then add
it. If the [Forms]![Form1]![Failure_Mode] is not in the criteria row in that
column put it there. (You will find the row names to the far left of the
grid.

Have fun!

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

Hi Hunter 57,

Yes the form is open in form view when I run the query.

You mentioned that the SQL indicates that the data is all coming from one
table. That may be where my problem lies.

I am dealing with three tables for this query:

[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)

I select a "Failure_Type" (i.e. electrical) from [TableFailureType] and save
it in a field in [Table1]. I am then trying to select a "Failure_Mode" from
[TableFailureMode] based on my previous "FailureType" selection and save it
in a second field in [Table1].

Thanks again for all your help.

:

Hi Dave,

I am sorry you are having so much trouble.

Relationships should play no part in this problem because your SQL indicates
that all of the data in your query is coming from just one table. If that is
not the case, and your data in the query should be coming from more than one
table, then you need to add that table name to the FROM clause of your query
like this:

FROM TableFailureMode, myothertablename

Question: Is Form1 open and in form view when you run this query? If not,
Access will produce the exact response you are seeing. Form1 must be open
and in Form view (the normal view) or the query cannot find the criteria.

I use the method you are trying often. I have no problems with it.

You might want to try the Queries Forum. You can find much better help than
me there.

Wish you the best,
Hunter57

:

Hi Hunter57,

I entered the SQL that you mentioned and received a syntax error. I noted a
missing "[" at the beginning of the second line. I fixed it and still get the
"ENTER PARAMETER VALUE"
"Forms!Form1!Failure_Type" popup.

I am wondering if I need to go back to the basics...
Before looking at the SQL, Do I have the tables set up properly?
If the tables are set up properly do I need to provide for object
dependancies or relationships?
Do I need to set up some form of query or does the SQL provide the
appropriate capability to display only those items as categorized by the
first combo box?

Thanks again.
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


:

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


:

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

:

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)