A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table design and relationships for cascading comboboxes



 
 
Thread Tools Display Modes
  #11  
Old May 9th, 2007, 01:13 AM posted to microsoft.public.access.tablesdbdesign
Hunter57
external usenet poster
 
Posts: 113
Default Table design and relationships for cascading comboboxes

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.

"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


  #12  
Old May 9th, 2007, 07:29 PM posted to microsoft.public.access.tablesdbdesign
Access Newbee Dave[_2_]
external usenet poster
 
Posts: 13
Default Table design and relationships for cascading comboboxes

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


"Hunter57" wrote:

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

  #13  
Old May 9th, 2007, 09:04 PM posted to microsoft.public.access.tablesdbdesign
Hunter57
external usenet poster
 
Posts: 113
Default Table design and relationships for cascading comboboxes

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

"Access Newbee Dave" wrote:

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


"Hunter57" wrote:

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

  #14  
Old May 10th, 2007, 02:07 PM posted to microsoft.public.access.tablesdbdesign
Access Newbee Dave[_2_]
external usenet poster
 
Posts: 13
Default Table design and relationships for cascading comboboxes

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.

"Hunter57" wrote:

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

"Access Newbee Dave" wrote:

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


"Hunter57" wrote:

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

  #15  
Old May 10th, 2007, 05:32 PM posted to microsoft.public.access.tablesdbdesign
Hunter57
external usenet poster
 
Posts: 113
Default Table design and relationships for cascading comboboxes

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

"Access Newbee Dave" wrote:

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.

"Hunter57" wrote:

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

"Access Newbee Dave" wrote:

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


"Hunter57" wrote:

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

  #16  
Old May 11th, 2007, 02:21 PM posted to microsoft.public.access.tablesdbdesign
Access Newbee Dave[_2_]
external usenet poster
 
Posts: 13
Default Table design and relationships for cascading comboboxes

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/ac...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


"Hunter57" wrote:

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

"Access Newbee Dave" wrote:

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.

"Hunter57" wrote:

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

"Access Newbee Dave" wrote:

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


"Hunter57" wrote:

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

  #17  
Old May 11th, 2007, 02:39 PM posted to microsoft.public.access.tablesdbdesign
Hunter57
external usenet poster
 
Posts: 113
Default Table design and relationships for cascading comboboxes

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

"Access Newbee Dave" wrote:

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/ac...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


"Hunter57" wrote:

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

"Access Newbee Dave" wrote:

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.

"Hunter57" wrote:

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

"Access Newbee Dave" wrote:

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


"Hunter57" wrote:

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)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.