 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2007Table 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/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 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. [quoted text clipped - 60 lines] > Thanks in advance > Dave Hunter57, thanks for your response. It still looks like I am stuck though. The underscores (_) indicated below are in fact underscores in the names and not an indication of spaces. I used no spaces in my naming conventions. I entered the following: SELECT [TableFailureMode].[Mode_ID], [TableFailureMode].[Failure_Mode] FROM TableFailureMode WHERE ((([TableFailureMode].[Type_ID])= [Forms]![Form1]![Failure_Type])) ORDER BY [TableFailureMode].[Failure_Mode]; I now receive the following pop-up error message. Enter Parameter Value Forms!Form1!Failure_Type (There is an entry field and two buttons <OK> and <Cancel> in this popup window) Regardless of how I close the box when I click the dropdown arrow for the combo box I see one row with three blank fields. Hopefully my explaination is understood. Again, thanks in advance for any help. Dave > Hi Dave, > [quoted text clipped - 82 lines] > > Thanks in advance > > Dave I should mention that I select "Electrical" in the "Failure_Type" combo box and the error occurs when I try to select anything in the "Failure_Mode" combo box. > Hunter57, thanks for your response. It still looks like I am stuck though. > [quoted text clipped - 108 lines] > > > Thanks in advance > > > 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" [quoted text clipped - 112 lines] > > > > Thanks in advance > > > > 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, > [quoted text clipped - 132 lines] > > > > > Thanks in advance > > > > > Dave Hi Dave, The error message suggests that there is a problem with the criteria or parameter. Have you checked the name of your combo box by opening the Properties sheet of the combo box, clicking the "Other" Tab to make sure that the Name listed there is the same as the name in the last bracket that reads Failure_Type [Forms]![Form1]![Failure_Type] ? If the name of your combo box is Combo34 or cboFailure_Type instead of Failure_Type that will make your query fail. You need to make the names match by changing either the name in the brackets or the name of the combo box. If that is not the problem, then you may need to make this a Parameter Query. Sometimes Access just doesn't work well with getting the creteria from a Form. You can do this by opening your query in design view, click Query on the Menu Bar up top, and select Parameters at the bottom of the list. A grid like this opens up. ___________________________ |__Parameter__ |__Data Type__ | |_____________|_____________| [quoted text clipped - 5 lines] |_____________|_____________| |_____________|_____________| 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. > [quoted text clipped - 21 lines] > > 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, > [quoted text clipped - 65 lines] > > > > 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" [quoted text clipped - 82 lines] > > > > > > 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, > [quoted text clipped - 117 lines] > > > > > > > > Dave 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, > [quoted text clipped - 135 lines] > > > > > > > > > > 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, > [quoted text clipped - 159 lines] > > > > > > > > > > > > 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, > [quoted text clipped - 193 lines] > > > > > > > > > > > > > > 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, > [quoted text clipped - 251 lines] > > > > > > > > > > > > > > > > Dave 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, > [quoted text clipped - 273 lines] > > > > > > > > > (There is an entry field and two buttons <OK> and <Cancel> in this popup > > > > > > > > > window) 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, > [quoted text clipped - 65 lines] > > > > Dave Hi Dave, Be sure to bracket the criteria of your Where clause--that could be where the error is coming from if you do have the _ lines in your field names. It should look like this. WHERE (((TableFailureMode.Type_ID)= [Forms]![Form1]![Failure_Type])) Hunter57 > I am trying to put together a database that will collect nonconformance > data, analyze the data, and generate reports. [quoted text clipped - 60 lines] > Thanks in advance > Dave |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.