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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table design and relationships for cascading comboboxes
I am trying to put together a database that will collect nonconformance data, analyze the data, and generate reports. This database currently is set up with 4 tables: [Table1] The main table contains records that would be used to collect and record a unique nonconformance. I have an autonumber field as the unique identifier for the record. A key report (Nonconformance Report) would allow the user to print out everything collected up to the current time for this record. [TableFailureType] Includes 2 fields; “Type_ID” (Autonumber – Primary Key) "Failure_Type" (Text – Electrical, Mechanical, Optical) [TableFailureMode] Includes 3 fields “Mode_ID” (Autonumber – Primary Key) "Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Type_ID” (Number) [TableFailureSubMode] Includes 3 fields “SubMode_ID” (Autonumber – Primary Key) "Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Mode_ID” (Number) I have been able to design a basic form [Form1] where I can enter data into [Table1]. I also have combo boxes that can select the data from the other smaller tables and place the result into [Table1]. I would like to have these combo boxes select an item (i.e. Electrical) from the "TableFailureType" table and have it only display those "Failure_Mode" items from [TableFailureMode] that are associated to the “Electrical" "TableFailureType". From there I would like to use the [Failure_Submode] combo box and only have those submodes displayed that are associated to the selected failure mode. The result should be placed in the appropriate field of the record from [Table1] so they can be used in reports. I have tried to follow the instructions to set up a combo box based on the results of a second box from... http://office.microsoft.com/en-us/ac...aspx?pid=CL100 I have also reviewed a number of other posts in the Access forums. So far I am lost. Looking at just the combo box tied to the FailureMode I have the following entered in the rowsource field: "SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode FROM TableFailureMode WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type)) ORDER BY TableFailureMode.Failure_Mode;" When I try to use the form I receive the following error message: The record source “~sq_cForm1~sq_cFailureMode” specified on this form or report does not exist. At the current time I am not sure if I have the tables set up properly. I am also not sure how to set up the relationships. Any help would be appreciated. Thanks in advance Dave |
#2
|
|||
|
|||
Table design and relationships for cascading comboboxes
Hi Dave,
That query syntax can be tricky, can't it? Access does not work well with spaces between words. The error message is probably in response to empty spaces. If your field names have empty spaces then put the names in brackets and leave out the _. You can try this: SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type])) ORDER BY [TableFailureMode].[Failure Mode]; If you have those _ lines in your field names then I would just put everything in brackets any to see it that works. Hope it helps. Hunter57 Just huntin' for some data. http://churchmanagementsoftware.googlepages.com "Access Newbee Dave" wrote: I am trying to put together a database that will collect nonconformance data, analyze the data, and generate reports. This database currently is set up with 4 tables: [Table1] The main table contains records that would be used to collect and record a unique nonconformance. I have an autonumber field as the unique identifier for the record. A key report (Nonconformance Report) would allow the user to print out everything collected up to the current time for this record. [TableFailureType] Includes 2 fields; “Type_ID” (Autonumber – Primary Key) "Failure_Type" (Text – Electrical, Mechanical, Optical) [TableFailureMode] Includes 3 fields “Mode_ID” (Autonumber – Primary Key) "Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Type_ID” (Number) [TableFailureSubMode] Includes 3 fields “SubMode_ID” (Autonumber – Primary Key) "Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Mode_ID” (Number) I have been able to design a basic form [Form1] where I can enter data into [Table1]. I also have combo boxes that can select the data from the other smaller tables and place the result into [Table1]. I would like to have these combo boxes select an item (i.e. Electrical) from the "TableFailureType" table and have it only display those "Failure_Mode" items from [TableFailureMode] that are associated to the “Electrical" "TableFailureType". From there I would like to use the [Failure_Submode] combo box and only have those submodes displayed that are associated to the selected failure mode. The result should be placed in the appropriate field of the record from [Table1] so they can be used in reports. I have tried to follow the instructions to set up a combo box based on the results of a second box from... http://office.microsoft.com/en-us/ac...aspx?pid=CL100 I have also reviewed a number of other posts in the Access forums. So far I am lost. Looking at just the combo box tied to the FailureMode I have the following entered in the rowsource field: "SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode FROM TableFailureMode WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type)) ORDER BY TableFailureMode.Failure_Mode;" When I try to use the form I receive the following error message: The record source “~sq_cForm1~sq_cFailureMode” specified on this form or report does not exist. At the current time I am not sure if I have the tables set up properly. I am also not sure how to set up the relationships. Any help would be appreciated. Thanks in advance Dave |
#3
|
|||
|
|||
Table design and relationships for cascading comboboxes
Hi Dave,
Be sure to bracket the criteria of your Where clause--that could be where the error is coming from if you do have the _ lines in your field names. It should look like this. WHERE (((TableFailureMode.Type_ID)= [Forms]![Form1]![Failure_Type])) Hunter57 "Access Newbee Dave" wrote: I am trying to put together a database that will collect nonconformance data, analyze the data, and generate reports. This database currently is set up with 4 tables: [Table1] The main table contains records that would be used to collect and record a unique nonconformance. I have an autonumber field as the unique identifier for the record. A key report (Nonconformance Report) would allow the user to print out everything collected up to the current time for this record. [TableFailureType] Includes 2 fields; “Type_ID” (Autonumber – Primary Key) "Failure_Type" (Text – Electrical, Mechanical, Optical) [TableFailureMode] Includes 3 fields “Mode_ID” (Autonumber – Primary Key) "Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Type_ID” (Number) [TableFailureSubMode] Includes 3 fields “SubMode_ID” (Autonumber – Primary Key) "Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Mode_ID” (Number) I have been able to design a basic form [Form1] where I can enter data into [Table1]. I also have combo boxes that can select the data from the other smaller tables and place the result into [Table1]. I would like to have these combo boxes select an item (i.e. Electrical) from the "TableFailureType" table and have it only display those "Failure_Mode" items from [TableFailureMode] that are associated to the “Electrical" "TableFailureType". From there I would like to use the [Failure_Submode] combo box and only have those submodes displayed that are associated to the selected failure mode. The result should be placed in the appropriate field of the record from [Table1] so they can be used in reports. I have tried to follow the instructions to set up a combo box based on the results of a second box from... http://office.microsoft.com/en-us/ac...aspx?pid=CL100 I have also reviewed a number of other posts in the Access forums. So far I am lost. Looking at just the combo box tied to the FailureMode I have the following entered in the rowsource field: "SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode FROM TableFailureMode WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type)) ORDER BY TableFailureMode.Failure_Mode;" When I try to use the form I receive the following error message: The record source “~sq_cForm1~sq_cFailureMode” specified on this form or report does not exist. At the current time I am not sure if I have the tables set up properly. I am also not sure how to set up the relationships. Any help would be appreciated. Thanks in advance Dave |
#4
|
|||
|
|||
Table design and relationships for cascading comboboxes
Hunter57, thanks for your response. It still looks like I am stuck though.
The underscores (_) indicated below are in fact underscores in the names and not an indication of spaces. I used no spaces in my naming conventions. I entered the following: SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type])) ORDER BY [TableFailureMode].[Failure_Mode]; I now receive the following pop-up error message. Enter Parameter Value Forms!Form1!Failure_Type (There is an entry field and two buttons OK and Cancel in this popup window) Regardless of how I close the box when I click the dropdown arrow for the combo box I see one row with three blank fields. Hopefully my explaination is understood. Again, thanks in advance for any help. Dave "Hunter57" wrote: Hi Dave, That query syntax can be tricky, can't it? Access does not work well with spaces between words. The error message is probably in response to empty spaces. If your field names have empty spaces then put the names in brackets and leave out the _. You can try this: SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type])) ORDER BY [TableFailureMode].[Failure Mode]; If you have those _ lines in your field names then I would just put everything in brackets any to see it that works. Hope it helps. Hunter57 Just huntin' for some data. http://churchmanagementsoftware.googlepages.com "Access Newbee Dave" wrote: I am trying to put together a database that will collect nonconformance data, analyze the data, and generate reports. This database currently is set up with 4 tables: [Table1] The main table contains records that would be used to collect and record a unique nonconformance. I have an autonumber field as the unique identifier for the record. A key report (Nonconformance Report) would allow the user to print out everything collected up to the current time for this record. [TableFailureType] Includes 2 fields; “Type_ID” (Autonumber – Primary Key) "Failure_Type" (Text – Electrical, Mechanical, Optical) [TableFailureMode] Includes 3 fields “Mode_ID” (Autonumber – Primary Key) "Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Type_ID” (Number) [TableFailureSubMode] Includes 3 fields “SubMode_ID” (Autonumber – Primary Key) "Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Mode_ID” (Number) I have been able to design a basic form [Form1] where I can enter data into [Table1]. I also have combo boxes that can select the data from the other smaller tables and place the result into [Table1]. I would like to have these combo boxes select an item (i.e. Electrical) from the "TableFailureType" table and have it only display those "Failure_Mode" items from [TableFailureMode] that are associated to the “Electrical" "TableFailureType". From there I would like to use the [Failure_Submode] combo box and only have those submodes displayed that are associated to the selected failure mode. The result should be placed in the appropriate field of the record from [Table1] so they can be used in reports. I have tried to follow the instructions to set up a combo box based on the results of a second box from... http://office.microsoft.com/en-us/ac...aspx?pid=CL100 I have also reviewed a number of other posts in the Access forums. So far I am lost. Looking at just the combo box tied to the FailureMode I have the following entered in the rowsource field: "SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode FROM TableFailureMode WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type)) ORDER BY TableFailureMode.Failure_Mode;" When I try to use the form I receive the following error message: The record source “~sq_cForm1~sq_cFailureMode” specified on this form or report does not exist. At the current time I am not sure if I have the tables set up properly. I am also not sure how to set up the relationships. Any help would be appreciated. Thanks in advance Dave |
#5
|
|||
|
|||
Table design and relationships for cascading comboboxes
I should mention that I select "Electrical" in the "Failure_Type" combo box
and the error occurs when I try to select anything in the "Failure_Mode" combo box. "Access Newbee Dave" wrote: Hunter57, thanks for your response. It still looks like I am stuck though. The underscores (_) indicated below are in fact underscores in the names and not an indication of spaces. I used no spaces in my naming conventions. I entered the following: SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type])) ORDER BY [TableFailureMode].[Failure_Mode]; I now receive the following pop-up error message. Enter Parameter Value Forms!Form1!Failure_Type (There is an entry field and two buttons OK and Cancel in this popup window) Regardless of how I close the box when I click the dropdown arrow for the combo box I see one row with three blank fields. Hopefully my explaination is understood. Again, thanks in advance for any help. Dave "Hunter57" wrote: Hi Dave, That query syntax can be tricky, can't it? Access does not work well with spaces between words. The error message is probably in response to empty spaces. If your field names have empty spaces then put the names in brackets and leave out the _. You can try this: SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type])) ORDER BY [TableFailureMode].[Failure Mode]; If you have those _ lines in your field names then I would just put everything in brackets any to see it that works. Hope it helps. Hunter57 Just huntin' for some data. http://churchmanagementsoftware.googlepages.com "Access Newbee Dave" wrote: I am trying to put together a database that will collect nonconformance data, analyze the data, and generate reports. This database currently is set up with 4 tables: [Table1] The main table contains records that would be used to collect and record a unique nonconformance. I have an autonumber field as the unique identifier for the record. A key report (Nonconformance Report) would allow the user to print out everything collected up to the current time for this record. [TableFailureType] Includes 2 fields; “Type_ID” (Autonumber – Primary Key) "Failure_Type" (Text – Electrical, Mechanical, Optical) [TableFailureMode] Includes 3 fields “Mode_ID” (Autonumber – Primary Key) "Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Type_ID” (Number) [TableFailureSubMode] Includes 3 fields “SubMode_ID” (Autonumber – Primary Key) "Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Mode_ID” (Number) I have been able to design a basic form [Form1] where I can enter data into [Table1]. I also have combo boxes that can select the data from the other smaller tables and place the result into [Table1]. I would like to have these combo boxes select an item (i.e. Electrical) from the "TableFailureType" table and have it only display those "Failure_Mode" items from [TableFailureMode] that are associated to the “Electrical" "TableFailureType". From there I would like to use the [Failure_Submode] combo box and only have those submodes displayed that are associated to the selected failure mode. The result should be placed in the appropriate field of the record from [Table1] so they can be used in reports. I have tried to follow the instructions to set up a combo box based on the results of a second box from... http://office.microsoft.com/en-us/ac...aspx?pid=CL100 I have also reviewed a number of other posts in the Access forums. So far I am lost. Looking at just the combo box tied to the FailureMode I have the following entered in the rowsource field: "SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode FROM TableFailureMode WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type)) ORDER BY TableFailureMode.Failure_Mode;" When I try to use the form I receive the following error message: The record source “~sq_cForm1~sq_cFailureMode” specified on this form or report does not exist. At the current time I am not sure if I have the tables set up properly. I am also not sure how to set up the relationships. Any help would be appreciated. Thanks in advance Dave |
#6
|
|||
|
|||
Table design and relationships for cascading comboboxes
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 |
#7
|
|||
|
|||
Table design and relationships for cascading comboboxes
Hi Dave,
I just noticed something I should not have missed. It is very important--it appears that you do not have Type_ID as one of the columns in your query--you just have it listed as Criteria. You need to add Type_ID to your Query. If you use the Query grid add it to your query. Then put the [Forms]![Form1]![Failure_Type] under it in the criteria column. The SQL should look something like this: SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode], [TableFailureMode].[Type_ID] FROM TableFailureMode WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type])) ORDER BY [TableFailureMode].[Failure_Mode]; Sorry I missed that, Hunter57 "Access Newbee Dave" wrote: I should mention that I select "Electrical" in the "Failure_Type" combo box and the error occurs when I try to select anything in the "Failure_Mode" combo box. "Access Newbee Dave" wrote: Hunter57, thanks for your response. It still looks like I am stuck though. The underscores (_) indicated below are in fact underscores in the names and not an indication of spaces. I used no spaces in my naming conventions. I entered the following: SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type])) ORDER BY [TableFailureMode].[Failure_Mode]; I now receive the following pop-up error message. Enter Parameter Value Forms!Form1!Failure_Type (There is an entry field and two buttons OK and Cancel in this popup window) Regardless of how I close the box when I click the dropdown arrow for the combo box I see one row with three blank fields. Hopefully my explaination is understood. Again, thanks in advance for any help. Dave "Hunter57" wrote: Hi Dave, That query syntax can be tricky, can't it? Access does not work well with spaces between words. The error message is probably in response to empty spaces. If your field names have empty spaces then put the names in brackets and leave out the _. You can try this: SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type])) ORDER BY [TableFailureMode].[Failure Mode]; If you have those _ lines in your field names then I would just put everything in brackets any to see it that works. Hope it helps. Hunter57 Just huntin' for some data. http://churchmanagementsoftware.googlepages.com "Access Newbee Dave" wrote: I am trying to put together a database that will collect nonconformance data, analyze the data, and generate reports. This database currently is set up with 4 tables: [Table1] The main table contains records that would be used to collect and record a unique nonconformance. I have an autonumber field as the unique identifier for the record. A key report (Nonconformance Report) would allow the user to print out everything collected up to the current time for this record. [TableFailureType] Includes 2 fields; “Type_ID” (Autonumber – Primary Key) "Failure_Type" (Text – Electrical, Mechanical, Optical) [TableFailureMode] Includes 3 fields “Mode_ID” (Autonumber – Primary Key) "Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Type_ID” (Number) [TableFailureSubMode] Includes 3 fields “SubMode_ID” (Autonumber – Primary Key) "Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Mode_ID” (Number) I have been able to design a basic form [Form1] where I can enter data into [Table1]. I also have combo boxes that can select the data from the other smaller tables and place the result into [Table1]. I would like to have these combo boxes select an item (i.e. Electrical) from the "TableFailureType" table and have it only display those "Failure_Mode" items from [TableFailureMode] that are associated to the “Electrical" "TableFailureType". From there I would like to use the [Failure_Submode] combo box and only have those submodes displayed that are associated to the selected failure mode. The result should be placed in the appropriate field of the record from [Table1] so they can be used in reports. I have tried to follow the instructions to set up a combo box based on the results of a second box from... http://office.microsoft.com/en-us/ac...aspx?pid=CL100 I have also reviewed a number of other posts in the Access forums. So far I am lost. Looking at just the combo box tied to the FailureMode I have the following entered in the rowsource field: "SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode FROM TableFailureMode WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type)) ORDER BY TableFailureMode.Failure_Mode;" When I try to use the form I receive the following error message: The record source “~sq_cForm1~sq_cFailureMode” specified on this form or report does not exist. At the current time I am not sure if I have the tables set up properly. I am also not sure how to set up the relationships. Any help would be appreciated. Thanks in advance Dave |
#8
|
|||
|
|||
Table design and relationships for cascading comboboxes
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 |
#9
|
|||
|
|||
Table design and relationships for cascading comboboxes
Hi Hunter57,
I tried making the changes to the SQL exactly as indicated and still hit the same "ENTER PARAMETER VALUE" popup. I am not sure what you mean by query grid. Is this the SQL statement Query builder you get to from the 3 elipses at the end of the rowsource field in the property sheet? If so I have the following 3 fields: [Mode_ID] [Failure_Mode] [Type_ID] All 3 are checked as "Show" and [Forms]![Form1]![Failure_Type] is in the "Criteria" under the [Type_ID] field. Thanks for all the ideas and help. Dave "Hunter57" wrote: Hi Dave, I just noticed something I should not have missed. It is very important--it appears that you do not have Type_ID as one of the columns in your query--you just have it listed as Criteria. You need to add Type_ID to your Query. If you use the Query grid add it to your query. Then put the [Forms]![Form1]![Failure_Type] under it in the criteria column. The SQL should look something like this: SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode], [TableFailureMode].[Type_ID] FROM TableFailureMode WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type])) ORDER BY [TableFailureMode].[Failure_Mode]; Sorry I missed that, Hunter57 "Access Newbee Dave" wrote: I should mention that I select "Electrical" in the "Failure_Type" combo box and the error occurs when I try to select anything in the "Failure_Mode" combo box. "Access Newbee Dave" wrote: Hunter57, thanks for your response. It still looks like I am stuck though. The underscores (_) indicated below are in fact underscores in the names and not an indication of spaces. I used no spaces in my naming conventions. I entered the following: SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type])) ORDER BY [TableFailureMode].[Failure_Mode]; I now receive the following pop-up error message. Enter Parameter Value Forms!Form1!Failure_Type (There is an entry field and two buttons OK and Cancel in this popup window) Regardless of how I close the box when I click the dropdown arrow for the combo box I see one row with three blank fields. Hopefully my explaination is understood. Again, thanks in advance for any help. Dave "Hunter57" wrote: Hi Dave, That query syntax can be tricky, can't it? Access does not work well with spaces between words. The error message is probably in response to empty spaces. If your field names have empty spaces then put the names in brackets and leave out the _. You can try this: SELECT [TableFailureMode].[Mode ID], [TableFailureMode].[Failure Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type ID])= [Forms]![Form1]![Failure Type])) ORDER BY [TableFailureMode].[Failure Mode]; If you have those _ lines in your field names then I would just put everything in brackets any to see it that works. Hope it helps. Hunter57 Just huntin' for some data. http://churchmanagementsoftware.googlepages.com "Access Newbee Dave" wrote: I am trying to put together a database that will collect nonconformance data, analyze the data, and generate reports. This database currently is set up with 4 tables: [Table1] The main table contains records that would be used to collect and record a unique nonconformance. I have an autonumber field as the unique identifier for the record. A key report (Nonconformance Report) would allow the user to print out everything collected up to the current time for this record. [TableFailureType] Includes 2 fields; “Type_ID” (Autonumber – Primary Key) "Failure_Type" (Text – Electrical, Mechanical, Optical) [TableFailureMode] Includes 3 fields “Mode_ID” (Autonumber – Primary Key) "Failure_Mode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Type_ID” (Number) [TableFailureSubMode] Includes 3 fields “SubMode_ID” (Autonumber – Primary Key) "Failure_SubMode" (Text - Resistor, Capacitor, etc... (a sub-category of failure type)) “Mode_ID” (Number) I have been able to design a basic form [Form1] where I can enter data into [Table1]. I also have combo boxes that can select the data from the other smaller tables and place the result into [Table1]. I would like to have these combo boxes select an item (i.e. Electrical) from the "TableFailureType" table and have it only display those "Failure_Mode" items from [TableFailureMode] that are associated to the “Electrical" "TableFailureType". From there I would like to use the [Failure_Submode] combo box and only have those submodes displayed that are associated to the selected failure mode. The result should be placed in the appropriate field of the record from [Table1] so they can be used in reports. I have tried to follow the instructions to set up a combo box based on the results of a second box from... http://office.microsoft.com/en-us/ac...aspx?pid=CL100 I have also reviewed a number of other posts in the Access forums. So far I am lost. Looking at just the combo box tied to the FailureMode I have the following entered in the rowsource field: "SELECT TableFailureMode.Mode_ID, TableFailureMode.Failure_Mode FROM TableFailureMode WHERE (((TableFailureMode.Type_ID)= Forms![Form1]!Failure_Type)) ORDER BY TableFailureMode.Failure_Mode;" When I try to use the form I receive the following error message: The record source “~sq_cForm1~sq_cFailureMode” specified on this form or report does not exist. At the current time I am not sure if I have the tables set up properly. I am also not sure how to set up the relationships. Any help would be appreciated. Thanks in advance Dave |
#10
|
|||
|
|||
Table design and relationships for cascading comboboxes
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 |
|
Thread Tools | |
Display Modes | |
|
|