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 |
#11
|
|||
|
|||
Design questions
If the subform is based directly on (has as its record
source) tblPartListing (the junction table) which contains only its own PK plus the FKs from tblComponents and tblProcessPlan, then ComponentNumber is not available as the combo box's control source. Therefore, it seems that the control source for a combo box based on tblComponent can only be an expression: =[tblComponent]![ComponentNumber] Column 1 is the PK, 2 is ComponentNumber, 3 is ComponentDescription; I tried both 1 and 2 as the bound column. However, any attempt to select from the list or to add a part number results in the message (on the status bar) that the control can't be edited because it is bound to an expression. Therefore, the Not In List event cannot run. By the way, the sorting I need to do is in the combo box's row source, which is easy enough in a SQL expression. I can sort the Component numbers that are associated with a Process Plan in a report as needed. I was not clear about that. Thanks again for taking the time to reply, but it is clear that I am not explaining something properly. I will need to do more research. -----Original Message----- i'd say skip the query. instead, base the subform directly on tblPartListing. base the combo box for Components solely on tblComponents. since you're including all three fields of tblComponents in the query's columns, those values are available to you to show in the subform record as you choose - which i think you already know. from the above setup, you should be able to add new components to the combo box "on the fly". if you still have trouble with it after making those changes, post the code from your combo box's NotInList event. if you need to do a sort on the subform, based on the part numbers....hmmm, you might try sorting on a calculated control whose control source is set to =ComboBoxName.Column(1) i've never tried to do a form sort on a calculated control - don't know if it'll work or not. hth "Bruce" wrote in message ... The way this needs to work is for the Process Plan to show all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . . |
#12
|
|||
|
|||
Design questions
stick with this just a little longer. comments inline:
"Bruce" wrote in message ... If the subform is based directly on (has as its record source) tblPartListing (the junction table) which contains only its own PK plus the FKs from tblComponents and tblProcessPlan, then ComponentNumber is not available as the combo box's control source. correct. and it's *not supposed to be*. the foreign key field in tblPartListing is ComponentID, NOT ComponentNumber. the value you need to save in tblPartListing is ComponentID, NOT ComponentNumber. your combo box needs to be bound to (have it's ControlSource set to) ComponentID, NOT ComponentNumber. Therefore, it seems that the control source for a combo box based on tblComponent can only be an expression: =[tblComponent]![ComponentNumber] see above. Column 1 is the PK, 2 is ComponentNumber, 3 is ComponentDescription; I tried both 1 and 2 as the bound column. set the combo box BoundColumn to 1. the primary key (ComponentID) is the value you need to save. However, any attempt to select from the list or to add a part number results in the message (on the status bar) that the control can't be edited because it is bound to an expression. Therefore, the Not In List event cannot run. once you set up the combo box the way i said, the user will *see* the ComponentNumber in the combo box (because you have the first column's width set to zero, which is correct). when he/she enters a component number that does not exist in tblComponents, the NotInList event *will* run. By the way, the sorting I need to do is in the combo box's row source, which is easy enough in a SQL expression. I can sort the Component numbers that are associated with a Process Plan in a report as needed. I was not clear about that. good. that makes it even easier - no complications involving sorting the subform records. Thanks again for taking the time to reply, but it is clear that I am not explaining something properly. I will need to do more research. hopefully, you'll come back to the thread and read this reply. you've explained your setup and what you're doing very well. it seems that i'm the one who has not been explaining something properly, because i haven't been able to help you understand how i'm instructing you to set up the form/subform/combobox solution - which is a standard solution, by the way, not some crazy thing i made up. g hth -----Original Message----- i'd say skip the query. instead, base the subform directly on tblPartListing. base the combo box for Components solely on tblComponents. since you're including all three fields of tblComponents in the query's columns, those values are available to you to show in the subform record as you choose - which i think you already know. from the above setup, you should be able to add new components to the combo box "on the fly". if you still have trouble with it after making those changes, post the code from your combo box's NotInList event. if you need to do a sort on the subform, based on the part numbers....hmmm, you might try sorting on a calculated control whose control source is set to =ComboBoxName.Column(1) i've never tried to do a form sort on a calculated control - don't know if it'll work or not. hth "Bruce" wrote in message ... The way this needs to work is for the Process Plan to show all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . . |
#13
|
|||
|
|||
Design questions
Thanks again for all of your patience in the midst of my
frustration. I was indeed making the mistake of trying to set the control source of the combo box to the visible value rather than to the PK. As you explained, I need to set the combo box control source to the PK from the junction table, and the row source to the table (by way of a SQL statement). Component description is drawn from combo box.Column(2) as I mentioned earlier. I have the Not In List event set to hop over to a form for editing the list of part numbers. I have lots more to do on this database. I hope you won't duck the next time you see my name come up in the newsgroup. Your help has been invaluable. -----Original Message----- stick with this just a little longer. comments inline: "Bruce" wrote in message ... If the subform is based directly on (has as its record source) tblPartListing (the junction table) which contains only its own PK plus the FKs from tblComponents and tblProcessPlan, then ComponentNumber is not available as the combo box's control source. correct. and it's *not supposed to be*. the foreign key field in tblPartListing is ComponentID, NOT ComponentNumber. the value you need to save in tblPartListing is ComponentID, NOT ComponentNumber. your combo box needs to be bound to (have it's ControlSource set to) ComponentID, NOT ComponentNumber. Therefore, it seems that the control source for a combo box based on tblComponent can only be an expression: =[tblComponent]![ComponentNumber] see above. Column 1 is the PK, 2 is ComponentNumber, 3 is ComponentDescription; I tried both 1 and 2 as the bound column. set the combo box BoundColumn to 1. the primary key (ComponentID) is the value you need to save. However, any attempt to select from the list or to add a part number results in the message (on the status bar) that the control can't be edited because it is bound to an expression. Therefore, the Not In List event cannot run. once you set up the combo box the way i said, the user will *see* the ComponentNumber in the combo box (because you have the first column's width set to zero, which is correct). when he/she enters a component number that does not exist in tblComponents, the NotInList event *will* run. By the way, the sorting I need to do is in the combo box's row source, which is easy enough in a SQL expression. I can sort the Component numbers that are associated with a Process Plan in a report as needed. I was not clear about that. good. that makes it even easier - no complications involving sorting the subform records. Thanks again for taking the time to reply, but it is clear that I am not explaining something properly. I will need to do more research. hopefully, you'll come back to the thread and read this reply. you've explained your setup and what you're doing very well. it seems that i'm the one who has not been explaining something properly, because i haven't been able to help you understand how i'm instructing you to set up the form/subform/combobox solution - which is a standard solution, by the way, not some crazy thing i made up. g hth -----Original Message----- i'd say skip the query. instead, base the subform directly on tblPartListing. base the combo box for Components solely on tblComponents. since you're including all three fields of tblComponents in the query's columns, those values are available to you to show in the subform record as you choose - which i think you already know. from the above setup, you should be able to add new components to the combo box "on the fly". if you still have trouble with it after making those changes, post the code from your combo box's NotInList event. if you need to do a sort on the subform, based on the part numbers....hmmm, you might try sorting on a calculated control whose control source is set to =ComboBoxName.Column(1) i've never tried to do a form sort on a calculated control - don't know if it'll work or not. hth "Bruce" wrote in message ... The way this needs to work is for the Process Plan to show all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . . . |
#14
|
|||
|
|||
Design questions
no problem, and you're very welcome. the tough ones just make me that much
happier when it finally works! one minor note: from previous descriptions, your combo box is 3 columns altogether (though not all showing in the droplist, of course). the first column being the primary key ComponentID, and the second column being ComponentNumber. i noticed you referring to the second column as ComboBox.Column(2). just an fyi - columns in a combo box are zero-based. so if you want to refer to column one, in code or in an expression, it would be ComboBoxName.Column(0). to refer to column two, it's ComboBoxName.Column(1). etc, etc. "Bruce" wrote in message ... Thanks again for all of your patience in the midst of my frustration. I was indeed making the mistake of trying to set the control source of the combo box to the visible value rather than to the PK. As you explained, I need to set the combo box control source to the PK from the junction table, and the row source to the table (by way of a SQL statement). Component description is drawn from combo box.Column(2) as I mentioned earlier. I have the Not In List event set to hop over to a form for editing the list of part numbers. I have lots more to do on this database. I hope you won't duck the next time you see my name come up in the newsgroup. Your help has been invaluable. -----Original Message----- stick with this just a little longer. comments inline: "Bruce" wrote in message ... If the subform is based directly on (has as its record source) tblPartListing (the junction table) which contains only its own PK plus the FKs from tblComponents and tblProcessPlan, then ComponentNumber is not available as the combo box's control source. correct. and it's *not supposed to be*. the foreign key field in tblPartListing is ComponentID, NOT ComponentNumber. the value you need to save in tblPartListing is ComponentID, NOT ComponentNumber. your combo box needs to be bound to (have it's ControlSource set to) ComponentID, NOT ComponentNumber. Therefore, it seems that the control source for a combo box based on tblComponent can only be an expression: =[tblComponent]![ComponentNumber] see above. Column 1 is the PK, 2 is ComponentNumber, 3 is ComponentDescription; I tried both 1 and 2 as the bound column. set the combo box BoundColumn to 1. the primary key (ComponentID) is the value you need to save. However, any attempt to select from the list or to add a part number results in the message (on the status bar) that the control can't be edited because it is bound to an expression. Therefore, the Not In List event cannot run. once you set up the combo box the way i said, the user will *see* the ComponentNumber in the combo box (because you have the first column's width set to zero, which is correct). when he/she enters a component number that does not exist in tblComponents, the NotInList event *will* run. By the way, the sorting I need to do is in the combo box's row source, which is easy enough in a SQL expression. I can sort the Component numbers that are associated with a Process Plan in a report as needed. I was not clear about that. good. that makes it even easier - no complications involving sorting the subform records. Thanks again for taking the time to reply, but it is clear that I am not explaining something properly. I will need to do more research. hopefully, you'll come back to the thread and read this reply. you've explained your setup and what you're doing very well. it seems that i'm the one who has not been explaining something properly, because i haven't been able to help you understand how i'm instructing you to set up the form/subform/combobox solution - which is a standard solution, by the way, not some crazy thing i made up. g hth -----Original Message----- i'd say skip the query. instead, base the subform directly on tblPartListing. base the combo box for Components solely on tblComponents. since you're including all three fields of tblComponents in the query's columns, those values are available to you to show in the subform record as you choose - which i think you already know. from the above setup, you should be able to add new components to the combo box "on the fly". if you still have trouble with it after making those changes, post the code from your combo box's NotInList event. if you need to do a sort on the subform, based on the part numbers....hmmm, you might try sorting on a calculated control whose control source is set to =ComboBoxName.Column(1) i've never tried to do a form sort on a calculated control - don't know if it'll work or not. hth "Bruce" wrote in message ... The way this needs to work is for the Process Plan to show all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . . . |
#15
|
|||
|
|||
Design questions
LIteral column 1 , or Column(0) in AccessSpeak is the PK;
literal column 2, or Column(1) is the Component Number; and literal column 3, or Column(2) is the description. My Combo box is three columns: Literal column 1, or Column (0) is the bound column, and column widths are 0";1";0". When I select the component number, the Description text box swipes the invisible literal third column, or Column (2). I had encountered that curiosity on an earlier project. It does make it difficult to talk about the columns. You certainly gave my posting a very thorough reading to pick up on that. By the way, I have begun to enter test data into the DB, and all is working as hoped. -----Original Message----- no problem, and you're very welcome. the tough ones just make me that much happier when it finally works! one minor note: from previous descriptions, your combo box is 3 columns altogether (though not all showing in the droplist, of course). the first column being the primary key ComponentID, and the second column being ComponentNumber. i noticed you referring to the second column as ComboBox.Column(2). just an fyi - columns in a combo box are zero-based. so if you want to refer to column one, in code or in an expression, it would be ComboBoxName.Column(0). to refer to column two, it's ComboBoxName.Column(1). etc, etc. "Bruce" wrote in message ... Thanks again for all of your patience in the midst of my frustration. I was indeed making the mistake of trying to set the control source of the combo box to the visible value rather than to the PK. As you explained, I need to set the combo box control source to the PK from the junction table, and the row source to the table (by way of a SQL statement). Component description is drawn from combo box.Column(2) as I mentioned earlier. I have the Not In List event set to hop over to a form for editing the list of part numbers. I have lots more to do on this database. I hope you won't duck the next time you see my name come up in the newsgroup. Your help has been invaluable. -----Original Message----- stick with this just a little longer. comments inline: "Bruce" wrote in message ... If the subform is based directly on (has as its record source) tblPartListing (the junction table) which contains only its own PK plus the FKs from tblComponents and tblProcessPlan, then ComponentNumber is not available as the combo box's control source. correct. and it's *not supposed to be*. the foreign key field in tblPartListing is ComponentID, NOT ComponentNumber. the value you need to save in tblPartListing is ComponentID, NOT ComponentNumber. your combo box needs to be bound to (have it's ControlSource set to) ComponentID, NOT ComponentNumber. Therefore, it seems that the control source for a combo box based on tblComponent can only be an expression: =[tblComponent]![ComponentNumber] see above. Column 1 is the PK, 2 is ComponentNumber, 3 is ComponentDescription; I tried both 1 and 2 as the bound column. set the combo box BoundColumn to 1. the primary key (ComponentID) is the value you need to save. However, any attempt to select from the list or to add a part number results in the message (on the status bar) that the control can't be edited because it is bound to an expression. Therefore, the Not In List event cannot run. once you set up the combo box the way i said, the user will *see* the ComponentNumber in the combo box (because you have the first column's width set to zero, which is correct). when he/she enters a component number that does not exist in tblComponents, the NotInList event *will* run. By the way, the sorting I need to do is in the combo box's row source, which is easy enough in a SQL expression. I can sort the Component numbers that are associated with a Process Plan in a report as needed. I was not clear about that. good. that makes it even easier - no complications involving sorting the subform records. Thanks again for taking the time to reply, but it is clear that I am not explaining something properly. I will need to do more research. hopefully, you'll come back to the thread and read this reply. you've explained your setup and what you're doing very well. it seems that i'm the one who has not been explaining something properly, because i haven't been able to help you understand how i'm instructing you to set up the form/subform/combobox solution - which is a standard solution, by the way, not some crazy thing i made up. g hth -----Original Message----- i'd say skip the query. instead, base the subform directly on tblPartListing. base the combo box for Components solely on tblComponents. since you're including all three fields of tblComponents in the query's columns, those values are available to you to show in the subform record as you choose - which i think you already know. from the above setup, you should be able to add new components to the combo box "on the fly". if you still have trouble with it after making those changes, post the code from your combo box's NotInList event. if you need to do a sort on the subform, based on the part numbers....hmmm, you might try sorting on a calculated control whose control source is set to =ComboBoxName.Column(1) i've never tried to do a form sort on a calculated control - don't know if it'll work or not. hth "Bruce" wrote in message ... The way this needs to work is for the Process Plan to show all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . . . . |
#16
|
|||
|
|||
Design questions
oh good, glad it's working for you.
re the combo box columns: i overkill sometimes, telling people what they already know - but better safe than sorry! g "Bruce" wrote in message ... LIteral column 1 , or Column(0) in AccessSpeak is the PK; literal column 2, or Column(1) is the Component Number; and literal column 3, or Column(2) is the description. My Combo box is three columns: Literal column 1, or Column (0) is the bound column, and column widths are 0";1";0". When I select the component number, the Description text box swipes the invisible literal third column, or Column (2). I had encountered that curiosity on an earlier project. It does make it difficult to talk about the columns. You certainly gave my posting a very thorough reading to pick up on that. By the way, I have begun to enter test data into the DB, and all is working as hoped. -----Original Message----- no problem, and you're very welcome. the tough ones just make me that much happier when it finally works! one minor note: from previous descriptions, your combo box is 3 columns altogether (though not all showing in the droplist, of course). the first column being the primary key ComponentID, and the second column being ComponentNumber. i noticed you referring to the second column as ComboBox.Column(2). just an fyi - columns in a combo box are zero-based. so if you want to refer to column one, in code or in an expression, it would be ComboBoxName.Column(0). to refer to column two, it's ComboBoxName.Column(1). etc, etc. "Bruce" wrote in message ... Thanks again for all of your patience in the midst of my frustration. I was indeed making the mistake of trying to set the control source of the combo box to the visible value rather than to the PK. As you explained, I need to set the combo box control source to the PK from the junction table, and the row source to the table (by way of a SQL statement). Component description is drawn from combo box.Column(2) as I mentioned earlier. I have the Not In List event set to hop over to a form for editing the list of part numbers. I have lots more to do on this database. I hope you won't duck the next time you see my name come up in the newsgroup. Your help has been invaluable. -----Original Message----- stick with this just a little longer. comments inline: "Bruce" wrote in message ... If the subform is based directly on (has as its record source) tblPartListing (the junction table) which contains only its own PK plus the FKs from tblComponents and tblProcessPlan, then ComponentNumber is not available as the combo box's control source. correct. and it's *not supposed to be*. the foreign key field in tblPartListing is ComponentID, NOT ComponentNumber. the value you need to save in tblPartListing is ComponentID, NOT ComponentNumber. your combo box needs to be bound to (have it's ControlSource set to) ComponentID, NOT ComponentNumber. Therefore, it seems that the control source for a combo box based on tblComponent can only be an expression: =[tblComponent]![ComponentNumber] see above. Column 1 is the PK, 2 is ComponentNumber, 3 is ComponentDescription; I tried both 1 and 2 as the bound column. set the combo box BoundColumn to 1. the primary key (ComponentID) is the value you need to save. However, any attempt to select from the list or to add a part number results in the message (on the status bar) that the control can't be edited because it is bound to an expression. Therefore, the Not In List event cannot run. once you set up the combo box the way i said, the user will *see* the ComponentNumber in the combo box (because you have the first column's width set to zero, which is correct). when he/she enters a component number that does not exist in tblComponents, the NotInList event *will* run. By the way, the sorting I need to do is in the combo box's row source, which is easy enough in a SQL expression. I can sort the Component numbers that are associated with a Process Plan in a report as needed. I was not clear about that. good. that makes it even easier - no complications involving sorting the subform records. Thanks again for taking the time to reply, but it is clear that I am not explaining something properly. I will need to do more research. hopefully, you'll come back to the thread and read this reply. you've explained your setup and what you're doing very well. it seems that i'm the one who has not been explaining something properly, because i haven't been able to help you understand how i'm instructing you to set up the form/subform/combobox solution - which is a standard solution, by the way, not some crazy thing i made up. g hth -----Original Message----- i'd say skip the query. instead, base the subform directly on tblPartListing. base the combo box for Components solely on tblComponents. since you're including all three fields of tblComponents in the query's columns, those values are available to you to show in the subform record as you choose - which i think you already know. from the above setup, you should be able to add new components to the combo box "on the fly". if you still have trouble with it after making those changes, post the code from your combo box's NotInList event. if you need to do a sort on the subform, based on the part numbers....hmmm, you might try sorting on a calculated control whose control source is set to =ComboBoxName.Column(1) i've never tried to do a form sort on a calculated control - don't know if it'll work or not. hth "Bruce" wrote in message ... The way this needs to work is for the Process Plan to show all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . . . . |
|
Thread Tools | |
Display Modes | |
|
|