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
|
|||
|
|||
Quote Form with Options
Hi,
I have a main quote form with subforms for labor, parts and misc parts all tied to a job number with several calculations on the main form from each of the subforms. This works great as it is. Now users tell me they need to have ability to create optional quotes for same job number. Is it possible to have an option group on the form so that the first main default quote would be option 1 and then if user needs to create another quote for the same job number (job number is autonumber and primary from tgeninfo table) they can press "Option 2" button and use the same (now cleared) form? This is the general idea of what I want to do, but I'm not sure how to go about doing it, if its even possible? If anyone has suggestions to make it work, I would love to hear them. Thanks, Pam |
#2
|
|||
|
|||
Quote Form with Options
On Thu, 29 Apr 2010 13:58:02 -0700, PHisaw
wrote: Before we worry about how to implement it, let's worry about the database design. I understand we have this requirement: JobNumber is currently unique and optional quotes need to reference the same jobnumber. You have a few options: 1: JobNumber + a new field QuoteOption will be the new PK. QuoteOption is 1 by default, but can be 2, 3 for optional quotes. 2: Quote table will have much fewer fields, mostly JobNumber, QuoteOption, and most fields move to a QuoteInstance table in 1:M relation, since now each Quote (an abstract entity) has many QuoteInstances. You probably need an IsPrimary field in the QuoteInstance table, or by convention the one with the lowest PK value is the primary. 3: Quote table will get a new field ParentQuote. For the current quotes the value is same as the current QuoteID (it points to itself), but for optional quotes it points to the ParentQuote where it came from. 4: Others? Which option appeals to you the most, and why? -Tom. Microsoft Access MVP Hi, I have a main quote form with subforms for labor, parts and misc parts all tied to a job number with several calculations on the main form from each of the subforms. This works great as it is. Now users tell me they need to have ability to create optional quotes for same job number. Is it possible to have an option group on the form so that the first main default quote would be option 1 and then if user needs to create another quote for the same job number (job number is autonumber and primary from tgeninfo table) they can press "Option 2" button and use the same (now cleared) form? This is the general idea of what I want to do, but I'm not sure how to go about doing it, if its even possible? If anyone has suggestions to make it work, I would love to hear them. Thanks, Pam |
#3
|
|||
|
|||
Quote Form with Options
Tom,
Thank you for taking time to reply. Option 2 & 3 were not real clear - maybe because I've been working with this too long. As for option 1, I do have a field for "option" on the table tQuoteMain. I've also included the field on the subform tables as well. I've put the option group on the main form and when I click on option 2, it will clear all boxes on the main form, but does not clear the subforms for new data for option 2. I've tried relating the option fields in the subform queries, but have had no success. Can you please tell me what I need to do to make this work as simply as possible? Thanks again, Pam "Tom van Stiphout" wrote: On Thu, 29 Apr 2010 13:58:02 -0700, PHisaw wrote: Before we worry about how to implement it, let's worry about the database design. I understand we have this requirement: JobNumber is currently unique and optional quotes need to reference the same jobnumber. You have a few options: 1: JobNumber + a new field QuoteOption will be the new PK. QuoteOption is 1 by default, but can be 2, 3 for optional quotes. 2: Quote table will have much fewer fields, mostly JobNumber, QuoteOption, and most fields move to a QuoteInstance table in 1:M relation, since now each Quote (an abstract entity) has many QuoteInstances. You probably need an IsPrimary field in the QuoteInstance table, or by convention the one with the lowest PK value is the primary. 3: Quote table will get a new field ParentQuote. For the current quotes the value is same as the current QuoteID (it points to itself), but for optional quotes it points to the ParentQuote where it came from. 4: Others? Which option appeals to you the most, and why? -Tom. Microsoft Access MVP Hi, I have a main quote form with subforms for labor, parts and misc parts all tied to a job number with several calculations on the main form from each of the subforms. This works great as it is. Now users tell me they need to have ability to create optional quotes for same job number. Is it possible to have an option group on the form so that the first main default quote would be option 1 and then if user needs to create another quote for the same job number (job number is autonumber and primary from tgeninfo table) they can press "Option 2" button and use the same (now cleared) form? This is the general idea of what I want to do, but I'm not sure how to go about doing it, if its even possible? If anyone has suggestions to make it work, I would love to hear them. Thanks, Pam . |
#4
|
|||
|
|||
Quote Form with Options
On Fri, 30 Apr 2010 08:05:01 -0700, PHisaw
wrote: Hi Pam, Yes it does get somewhat technical sometimes. Sorry about that. Database design is where most casual Access users have the most problems, and IMHO it's the most important part to get right. Given that you already have an Option field (I'm assuming a number starting at 1) we're going to use my suggestion #1. You have to include the Option field in the Primary Key, so it will be over the combination of JobNumber and Option. Once that is in place let's look at your form. I'm assuming it is in Form View showing one JobNumber at a time. In the future it will show one JobNumber/Option combination at a time. For example Record 1 is JobNumber 123, Option 1. Record 2 is JobNumber 123, Option 2. How does the user create this second record? You seem to indicate by selecting #2 from the option group. That seems like a bad idea, because at least in theory there can be an unlimited number of options. You create an Option Group with 5 options, and I'll show you a job that needs 6. Maybe better to provide a button: "Create New Option". It would look at the maximum option used so far (DMax function) and add 1 to it. When the button is clicked, you save the JobNumber information and perhaps some other key information, go to a new record, populate the JobNumber with the saved value, and set the Option field to whatever DMax(...)+1 returns. Now let's look at the subforms. The reason they are currently showing the same data for all Options is that LinkMasterFIelds and LinkParentFIelds are set to QuoteID only. We'll fix that in a minute. In the database design you have tables like LaborItemsForQuote(LaborItemID, QuoteID, ...), PartsForQuote (PartID, QuoteID, Qty, ...), etc. They are currently related 1:M (shorthand for one-to-many) to the main Quotes table via the QuoteID field or some such. Since we have changed the main table to have a 2-field primary key, that will have to change: QuoteID + Option will be the foreign key in each of those related tables, and you need to update the Relationships window. Don't forget to check the box to Enforce the relationships. Once in place getting the subforms to work properly is simple: Set the LinkMasterFields and LinkChildFields of the subform control to both field names separated by a semicolon. That way when you scroll from record 1 to 2 (using my earlier example) it's going to find JobNumber=123, Option=2 and the subforms will initially be empty. -Tom. Microsoft Access MVP Tom, Thank you for taking time to reply. Option 2 & 3 were not real clear - maybe because I've been working with this too long. As for option 1, I do have a field for "option" on the table tQuoteMain. I've also included the field on the subform tables as well. I've put the option group on the main form and when I click on option 2, it will clear all boxes on the main form, but does not clear the subforms for new data for option 2. I've tried relating the option fields in the subform queries, but have had no success. Can you please tell me what I need to do to make this work as simply as possible? Thanks again, Pam "Tom van Stiphout" wrote: On Thu, 29 Apr 2010 13:58:02 -0700, PHisaw wrote: Before we worry about how to implement it, let's worry about the database design. I understand we have this requirement: JobNumber is currently unique and optional quotes need to reference the same jobnumber. You have a few options: 1: JobNumber + a new field QuoteOption will be the new PK. QuoteOption is 1 by default, but can be 2, 3 for optional quotes. 2: Quote table will have much fewer fields, mostly JobNumber, QuoteOption, and most fields move to a QuoteInstance table in 1:M relation, since now each Quote (an abstract entity) has many QuoteInstances. You probably need an IsPrimary field in the QuoteInstance table, or by convention the one with the lowest PK value is the primary. 3: Quote table will get a new field ParentQuote. For the current quotes the value is same as the current QuoteID (it points to itself), but for optional quotes it points to the ParentQuote where it came from. 4: Others? Which option appeals to you the most, and why? -Tom. Microsoft Access MVP Hi, I have a main quote form with subforms for labor, parts and misc parts all tied to a job number with several calculations on the main form from each of the subforms. This works great as it is. Now users tell me they need to have ability to create optional quotes for same job number. Is it possible to have an option group on the form so that the first main default quote would be option 1 and then if user needs to create another quote for the same job number (job number is autonumber and primary from tgeninfo table) they can press "Option 2" button and use the same (now cleared) form? This is the general idea of what I want to do, but I'm not sure how to go about doing it, if its even possible? If anyone has suggestions to make it work, I would love to hear them. Thanks, Pam . |
#5
|
|||
|
|||
Quote Form with Options
Tom,
Thank you so much for your very detailed reply. I think I'm almost there but have run into an error by not having enough code for the label you suggested. I'm hoping you can help me with it. I included Option field as primary key along with JobNumber on the table tQuoteMain and tables backing subforms also have the JobNumber and Option fields on them. I set the tables in the relationship layout as 1:M (tQuoteMain:tsubformtables) The subforms are linked by JobNumber; Option. I created a label (don't like the color of the command buttons) "Create New Option" with the following code, but do not know how to write in everything you stated I would need - save the JobNumber information and other key information, go to a new record, populate JobNumber with saved value and set Option field to whatever returns (the option field is populating). Private Sub LblQuoteOptions_Click() If Me.Dirty Then Me.Dirty = False Me.Option = Nz(DMax("Option", "tquotemainform"), 0) + 1 End Sub How do I go about saving the jobnumber and other information and go to a new record and then populate jobnumber and other fields with the saved values? I think it will work if I can just get the right code in the label. I really appreciate your time and help. Pam "Tom van Stiphout" wrote: On Fri, 30 Apr 2010 08:05:01 -0700, PHisaw wrote: Hi Pam, Yes it does get somewhat technical sometimes. Sorry about that. Database design is where most casual Access users have the most problems, and IMHO it's the most important part to get right. Given that you already have an Option field (I'm assuming a number starting at 1) we're going to use my suggestion #1. You have to include the Option field in the Primary Key, so it will be over the combination of JobNumber and Option. Once that is in place let's look at your form. I'm assuming it is in Form View showing one JobNumber at a time. In the future it will show one JobNumber/Option combination at a time. For example Record 1 is JobNumber 123, Option 1. Record 2 is JobNumber 123, Option 2. How does the user create this second record? You seem to indicate by selecting #2 from the option group. That seems like a bad idea, because at least in theory there can be an unlimited number of options. You create an Option Group with 5 options, and I'll show you a job that needs 6. Maybe better to provide a button: "Create New Option". It would look at the maximum option used so far (DMax function) and add 1 to it. When the button is clicked, you save the JobNumber information and perhaps some other key information, go to a new record, populate the JobNumber with the saved value, and set the Option field to whatever DMax(...)+1 returns. Now let's look at the subforms. The reason they are currently showing the same data for all Options is that LinkMasterFIelds and LinkParentFIelds are set to QuoteID only. We'll fix that in a minute. In the database design you have tables like LaborItemsForQuote(LaborItemID, QuoteID, ...), PartsForQuote (PartID, QuoteID, Qty, ...), etc. They are currently related 1:M (shorthand for one-to-many) to the main Quotes table via the QuoteID field or some such. Since we have changed the main table to have a 2-field primary key, that will have to change: QuoteID + Option will be the foreign key in each of those related tables, and you need to update the Relationships window. Don't forget to check the box to Enforce the relationships. Once in place getting the subforms to work properly is simple: Set the LinkMasterFields and LinkChildFields of the subform control to both field names separated by a semicolon. That way when you scroll from record 1 to 2 (using my earlier example) it's going to find JobNumber=123, Option=2 and the subforms will initially be empty. -Tom. Microsoft Access MVP Tom, Thank you for taking time to reply. Option 2 & 3 were not real clear - maybe because I've been working with this too long. As for option 1, I do have a field for "option" on the table tQuoteMain. I've also included the field on the subform tables as well. I've put the option group on the main form and when I click on option 2, it will clear all boxes on the main form, but does not clear the subforms for new data for option 2. I've tried relating the option fields in the subform queries, but have had no success. Can you please tell me what I need to do to make this work as simply as possible? Thanks again, Pam "Tom van Stiphout" wrote: On Thu, 29 Apr 2010 13:58:02 -0700, PHisaw wrote: Before we worry about how to implement it, let's worry about the database design. I understand we have this requirement: JobNumber is currently unique and optional quotes need to reference the same jobnumber. You have a few options: 1: JobNumber + a new field QuoteOption will be the new PK. QuoteOption is 1 by default, but can be 2, 3 for optional quotes. 2: Quote table will have much fewer fields, mostly JobNumber, QuoteOption, and most fields move to a QuoteInstance table in 1:M relation, since now each Quote (an abstract entity) has many QuoteInstances. You probably need an IsPrimary field in the QuoteInstance table, or by convention the one with the lowest PK value is the primary. 3: Quote table will get a new field ParentQuote. For the current quotes the value is same as the current QuoteID (it points to itself), but for optional quotes it points to the ParentQuote where it came from. 4: Others? Which option appeals to you the most, and why? -Tom. Microsoft Access MVP Hi, I have a main quote form with subforms for labor, parts and misc parts all tied to a job number with several calculations on the main form from each of the subforms. This works great as it is. Now users tell me they need to have ability to create optional quotes for same job number. Is it possible to have an option group on the form so that the first main default quote would be option 1 and then if user needs to create another quote for the same job number (job number is autonumber and primary from tgeninfo table) they can press "Option 2" button and use the same (now cleared) form? This is the general idea of what I want to do, but I'm not sure how to go about doing it, if its even possible? If anyone has suggestions to make it work, I would love to hear them. Thanks, Pam . . |
#6
|
|||
|
|||
Quote Form with Options
Tom,
I'm still working on the code to make option quoting work. In the AfterUpdate event for jobnumber on the form fmainquote I've added the following: Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" but when I click on the label, option changes to 2, but the job number goes to 0 and then I get an error message that "record can't be changed or deleted due to related record not in tgeneralinfo". The form fmainquote opens from the form frepairs where jobnumber = jobnumber. What am I doing wrong to get 0 in the job number field? I've spent all day searching posts trying to get code to work, but still can't get it to work as needed. Thanks, Pam "Tom van Stiphout" wrote: On Fri, 30 Apr 2010 08:05:01 -0700, PHisaw wrote: Hi Pam, Yes it does get somewhat technical sometimes. Sorry about that. Database design is where most casual Access users have the most problems, and IMHO it's the most important part to get right. Given that you already have an Option field (I'm assuming a number starting at 1) we're going to use my suggestion #1. You have to include the Option field in the Primary Key, so it will be over the combination of JobNumber and Option. Once that is in place let's look at your form. I'm assuming it is in Form View showing one JobNumber at a time. In the future it will show one JobNumber/Option combination at a time. For example Record 1 is JobNumber 123, Option 1. Record 2 is JobNumber 123, Option 2. How does the user create this second record? You seem to indicate by selecting #2 from the option group. That seems like a bad idea, because at least in theory there can be an unlimited number of options. You create an Option Group with 5 options, and I'll show you a job that needs 6. Maybe better to provide a button: "Create New Option". It would look at the maximum option used so far (DMax function) and add 1 to it. When the button is clicked, you save the JobNumber information and perhaps some other key information, go to a new record, populate the JobNumber with the saved value, and set the Option field to whatever DMax(...)+1 returns. Now let's look at the subforms. The reason they are currently showing the same data for all Options is that LinkMasterFIelds and LinkParentFIelds are set to QuoteID only. We'll fix that in a minute. In the database design you have tables like LaborItemsForQuote(LaborItemID, QuoteID, ...), PartsForQuote (PartID, QuoteID, Qty, ...), etc. They are currently related 1:M (shorthand for one-to-many) to the main Quotes table via the QuoteID field or some such. Since we have changed the main table to have a 2-field primary key, that will have to change: QuoteID + Option will be the foreign key in each of those related tables, and you need to update the Relationships window. Don't forget to check the box to Enforce the relationships. Once in place getting the subforms to work properly is simple: Set the LinkMasterFields and LinkChildFields of the subform control to both field names separated by a semicolon. That way when you scroll from record 1 to 2 (using my earlier example) it's going to find JobNumber=123, Option=2 and the subforms will initially be empty. -Tom. Microsoft Access MVP Tom, Thank you for taking time to reply. Option 2 & 3 were not real clear - maybe because I've been working with this too long. As for option 1, I do have a field for "option" on the table tQuoteMain. I've also included the field on the subform tables as well. I've put the option group on the main form and when I click on option 2, it will clear all boxes on the main form, but does not clear the subforms for new data for option 2. I've tried relating the option fields in the subform queries, but have had no success. Can you please tell me what I need to do to make this work as simply as possible? Thanks again, Pam "Tom van Stiphout" wrote: On Thu, 29 Apr 2010 13:58:02 -0700, PHisaw wrote: Before we worry about how to implement it, let's worry about the database design. I understand we have this requirement: JobNumber is currently unique and optional quotes need to reference the same jobnumber. You have a few options: 1: JobNumber + a new field QuoteOption will be the new PK. QuoteOption is 1 by default, but can be 2, 3 for optional quotes. 2: Quote table will have much fewer fields, mostly JobNumber, QuoteOption, and most fields move to a QuoteInstance table in 1:M relation, since now each Quote (an abstract entity) has many QuoteInstances. You probably need an IsPrimary field in the QuoteInstance table, or by convention the one with the lowest PK value is the primary. 3: Quote table will get a new field ParentQuote. For the current quotes the value is same as the current QuoteID (it points to itself), but for optional quotes it points to the ParentQuote where it came from. 4: Others? Which option appeals to you the most, and why? -Tom. Microsoft Access MVP Hi, I have a main quote form with subforms for labor, parts and misc parts all tied to a job number with several calculations on the main form from each of the subforms. This works great as it is. Now users tell me they need to have ability to create optional quotes for same job number. Is it possible to have an option group on the form so that the first main default quote would be option 1 and then if user needs to create another quote for the same job number (job number is autonumber and primary from tgeninfo table) they can press "Option 2" button and use the same (now cleared) form? This is the general idea of what I want to do, but I'm not sure how to go about doing it, if its even possible? If anyone has suggestions to make it work, I would love to hear them. Thanks, Pam . . |
#7
|
|||
|
|||
Quote Form with Options
On Wed, 5 May 2010 14:30:01 -0700, PHisaw
wrote: What am I doing wrong to get 0 in the job number field? I've spent all day searching posts trying to get code to work, but still can't get it to work as needed. I would guess that in the Table design for the table, the Default Value property for Job Number is 0 (Access stupidly assumes that all Number type fields should default to zero; some should, but foreign keys definitely should NOT). Try removing this default. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Quote Form with Options
John,
Thanks for replying to post. The jobnumber field in the table tgeneralinfo is an autonumber field and primary key for table. When I looked at the properties at the bottom of the screen, I did not see "default value". We've been running like this for many years now, but if I need to change this field to a number+1 type, I can. Would you suggest this or could something else possibly be wrong causing this error? Thanks again for your help. Pam "John W. Vinson" wrote: On Wed, 5 May 2010 14:30:01 -0700, PHisaw wrote: What am I doing wrong to get 0 in the job number field? I've spent all day searching posts trying to get code to work, but still can't get it to work as needed. I would guess that in the Table design for the table, the Default Value property for Job Number is 0 (Access stupidly assumes that all Number type fields should default to zero; some should, but foreign keys definitely should NOT). Try removing this default. -- John W. Vinson [MVP] . |
#9
|
|||
|
|||
Quote Form with Options
On Thu, 6 May 2010 05:59:01 -0700, PHisaw
wrote: John, Thanks for replying to post. The jobnumber field in the table tgeneralinfo is an autonumber field and primary key for table. When I looked at the properties at the bottom of the screen, I did not see "default value". We've been running like this for many years now, but if I need to change this field to a number+1 type, I can. Would you suggest this or could something else possibly be wrong causing this error? I suspect it's the jobnumber field in the *related* table. An Autonumber would not have a default and should not cause this problem. I didn't go back through your discussion with Tom and probably should not have jumped in without understanding the whole situation! The error message, though, suggests that it's a foreign key that's being set to zero, not a primary key. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Quote Form with Options
Do you have any suggestions as to what I need to do to correct it so that it
will work properly? "John W. Vinson" wrote: On Thu, 6 May 2010 05:59:01 -0700, PHisaw wrote: John, Thanks for replying to post. The jobnumber field in the table tgeneralinfo is an autonumber field and primary key for table. When I looked at the properties at the bottom of the screen, I did not see "default value". We've been running like this for many years now, but if I need to change this field to a number+1 type, I can. Would you suggest this or could something else possibly be wrong causing this error? I suspect it's the jobnumber field in the *related* table. An Autonumber would not have a default and should not cause this problem. I didn't go back through your discussion with Tom and probably should not have jumped in without understanding the whole situation! The error message, though, suggests that it's a foreign key that's being set to zero, not a primary key. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|