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 - Repost
It seems my original thread of 4/29/10 "Quote Form with Options" has been forgotten. Tom van Stiphout was helping and gave me the instructions for a button to ""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." I have researched and tried several methods of code, but can't seem to get anything to work other than the option field increasing in number. This is currently what I have which throws an error message "can't go to specified record" and highlights the GoToRecord,,acNewRec line. I think it has something to do with the job number, but can't figure out how to correct it. Private Sub LblQuoteOptions_Click() DoCmd.RunCommand acCmdSave DoCmd.GoToRecord , , acNewRec Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" Me.Option = Nz(DMax("Option", "tquotemainform"), 0) + 1 End Sub Any suggestions would be greatly appreciated. Thank in advance, Pam |
#2
|
|||
|
|||
Quote Form With Options - Repost
I don't know what exactly was in the original string, but I see a few things
in the code you posted. I'm not sure what this line of code is supposed to do: Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" For clarity in posting and in reading the code, I suggest naming the text box differently from the field to which it is bound. For instance, the text box could be txtJobNumber. However, that is probably unrelated to the problem with this line. It seems the Default Value of JobNumber is the value of Job Number, which is circular. Also, the quotes are not necessary, and may be doing some harm. From your posting, you are trying to carry JobNumber from the previous record to the new record. Why is that? Is this code in a subform? If so, and if JobNumber is the linking field between the main form and subform tables, it needs to be the Link Child and Link Master properties of the subform control (the "box" containing the subform). One problem with the next line may be that Option is a reserved word. For more about reserved words: http://www.allenbrowne.com/Ap****ueBadWord.html Option should not be used as a name, but if it is used it should be in square brackets, and it probably should be the DefaultValue, or else it will change whenever the code is run. You can't rely on users not clicking a "dangerous" button: Me.txtOption.DefaultValue = Nz(DMax("[Option]", "tquotemainform"), 0) + 1 Is tquotemainform a table? PHisaw wrote: It seems my original thread of 4/29/10 "Quote Form with Options" has been forgotten. Tom van Stiphout was helping and gave me the instructions for a button to ""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." I have researched and tried several methods of code, but can't seem to get anything to work other than the option field increasing in number. This is currently what I have which throws an error message "can't go to specified record" and highlights the GoToRecord,,acNewRec line. I think it has something to do with the job number, but can't figure out how to correct it. Private Sub LblQuoteOptions_Click() DoCmd.RunCommand acCmdSave DoCmd.GoToRecord , , acNewRec Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" Me.Option = Nz(DMax("Option", "tquotemainform"), 0) + 1 End Sub Any suggestions would be greatly appreciated. Thank in advance, Pam -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Quote Form With Options - Repost
PHisaw,
It appears as though you also had a new "re-post" dealing with this issue on April 30th @ 3:17PM "Multiple quotes for same job number using option buttons" John Vinson replied to that... with no reply from you, as yet. Please be sure your up to snuff on all your replies... and... if this is another post dealing with primarily the same issue... please return to any old ones, and let folks know that you're re-posting. Thanks... -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "PHisaw" wrote in message ... It seems my original thread of 4/29/10 "Quote Form with Options" has been forgotten. Tom van Stiphout was helping and gave me the instructions for a button to ""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." I have researched and tried several methods of code, but can't seem to get anything to work other than the option field increasing in number. This is currently what I have which throws an error message "can't go to specified record" and highlights the GoToRecord,,acNewRec line. I think it has something to do with the job number, but can't figure out how to correct it. Private Sub LblQuoteOptions_Click() DoCmd.RunCommand acCmdSave DoCmd.GoToRecord , , acNewRec Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" Me.Option = Nz(DMax("Option", "tquotemainform"), 0) + 1 End Sub Any suggestions would be greatly appreciated. Thank in advance, Pam |
#4
|
|||
|
|||
Quote Form With Options - Repost
Bruce,
Thank you for replying. Per your message, I'm not sure what this line of code is supposed to do: Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" In researching on how to save field info and then repopulate field with saved info from previous record, this line of code is what I came across, but obviously it isn't working. Users have requested the ability to have more than one quote option for the same job number. Tom's suggestion was to have the main table relate to the subforms with JobNumber and Option fields - both PK's. Then a button on the main form that would save the job number from the previous record, insert it in the new record (new option, same job number) and increase the option by 1 based on last option number. I have a main quote table, tquotemainform, which supplies frepairquotemain form. On it are several subforms - labor, parts, and misc parts and all are related by JobNumber (autonumber and PK) and now by Option (also a PK - I'll change the name to something other than the reserved word). John Vinson responded that it may be the jobnumber in the related table. I asked how I could go about resolving the problem, but received no response. I just need to know if this is acutally possible as in example below with both fields being PKs. JobNumber Option 9549 1 9549 2 9550 1 9551 1 9551 2 Again, thanks for your help. Pam "BruceM via AccessMonster.com" wrote: I don't know what exactly was in the original string, but I see a few things in the code you posted. I'm not sure what this line of code is supposed to do: Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" For clarity in posting and in reading the code, I suggest naming the text box differently from the field to which it is bound. For instance, the text box could be txtJobNumber. However, that is probably unrelated to the problem with this line. It seems the Default Value of JobNumber is the value of Job Number, which is circular. Also, the quotes are not necessary, and may be doing some harm. From your posting, you are trying to carry JobNumber from the previous record to the new record. Why is that? Is this code in a subform? If so, and if JobNumber is the linking field between the main form and subform tables, it needs to be the Link Child and Link Master properties of the subform control (the "box" containing the subform). One problem with the next line may be that Option is a reserved word. For more about reserved words: http://www.allenbrowne.com/Ap****ueBadWord.html Option should not be used as a name, but if it is used it should be in square brackets, and it probably should be the DefaultValue, or else it will change whenever the code is run. You can't rely on users not clicking a "dangerous" button: Me.txtOption.DefaultValue = Nz(DMax("[Option]", "tquotemainform"), 0) + 1 Is tquotemainform a table? PHisaw wrote: It seems my original thread of 4/29/10 "Quote Form with Options" has been forgotten. Tom van Stiphout was helping and gave me the instructions for a button to ""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." I have researched and tried several methods of code, but can't seem to get anything to work other than the option field increasing in number. This is currently what I have which throws an error message "can't go to specified record" and highlights the GoToRecord,,acNewRec line. I think it has something to do with the job number, but can't figure out how to correct it. Private Sub LblQuoteOptions_Click() DoCmd.RunCommand acCmdSave DoCmd.GoToRecord , , acNewRec Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" Me.Option = Nz(DMax("Option", "tquotemainform"), 0) + 1 End Sub Any suggestions would be greatly appreciated. Thank in advance, Pam -- Message posted via http://www.accessmonster.com . |
#5
|
|||
|
|||
Quote Form With Options - Repost
If a job may have several quotes, quote information should be in a related
table: tblJob JobID (primary key, or PK; could be the job number) Customer etc. tblQuote QuoteID (PK) JobID (related to JobID in tblJob) other Quote fields as needed Establish the relationship between tblJob and tblQuote in the Relationships window. Build a main form based on tblJob, with a subform based on tblQuote. Set the Link Child and Link Master properties of the subfoirm control to JobID. It may seem like semantics, but I doubt Tom would have suggested "to have the main table relate to the subforms with JobNumber and Option fields". Tables relate to other tables, so I expect his reference to relationships was about relating one table to another. The subforms may be based on the related tables, but we can't see your database, so can only infer how it is constructed. Any discussion of this sort needs to start with the table structure, which depends on the real-world situation behind the database. What is the database supposed to do? PHisaw wrote: Bruce, Thank you for replying. Per your message, I'm not sure what this line of code is supposed to do: Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" In researching on how to save field info and then repopulate field with saved info from previous record, this line of code is what I came across, but obviously it isn't working. Users have requested the ability to have more than one quote option for the same job number. Tom's suggestion was to have the main table relate to the subforms with JobNumber and Option fields - both PK's. Then a button on the main form that would save the job number from the previous record, insert it in the new record (new option, same job number) and increase the option by 1 based on last option number. I have a main quote table, tquotemainform, which supplies frepairquotemain form. On it are several subforms - labor, parts, and misc parts and all are related by JobNumber (autonumber and PK) and now by Option (also a PK - I'll change the name to something other than the reserved word). John Vinson responded that it may be the jobnumber in the related table. I asked how I could go about resolving the problem, but received no response. I just need to know if this is acutally possible as in example below with both fields being PKs. JobNumber Option 9549 1 9549 2 9550 1 9551 1 9551 2 Again, thanks for your help. Pam I don't know what exactly was in the original string, but I see a few things in the code you posted. [quoted text clipped - 56 lines] Thank in advance, Pam -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Quote Form With Options - Repost
Bruce,
I already have a main table that holds all job info, tgeninfo, and is related to the main quote table, tmainquoteform, in the relationship window. I have a form for all the general information based on tgeninfo and a button when clicked takes user to the main form for quotes, frepairquotemainform, which includes the subforms for labor, parts and misc parts. These subforms are backed by tables for each that are related in a 1:M relationship in the relationship window also. I really don't want a subform of the main quote that contains other subforms on the main form for the general information. The subforms are linked with Master and Child fields to the main quote form by JobNumber and Option. In my previous message, it was an error on my part when I stated Tom suggested relating the main table to the subforms. I meant the main form to the subforms. Here is his original suggestion which I hope will shed more light on what I wish to accomplish. "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." I've set my tables as such, but I cannot figure the code needed as he suggested to save the field info from the previous record, apply it to the same field for the new record and increase the option number based on the last one used. Does any of this help explain what I want db to do? If not, I may have to move on to creating basically duplicate forms, queries and tables and limiting users to only 1, maybe 2, additional options which would seem redundant and more bloat to the db. Thanks, Pam "BruceM via AccessMonster.com" wrote: If a job may have several quotes, quote information should be in a related table: tblJob JobID (primary key, or PK; could be the job number) Customer etc. tblQuote QuoteID (PK) JobID (related to JobID in tblJob) other Quote fields as needed Establish the relationship between tblJob and tblQuote in the Relationships window. Build a main form based on tblJob, with a subform based on tblQuote. Set the Link Child and Link Master properties of the subfoirm control to JobID. It may seem like semantics, but I doubt Tom would have suggested "to have the main table relate to the subforms with JobNumber and Option fields". Tables relate to other tables, so I expect his reference to relationships was about relating one table to another. The subforms may be based on the related tables, but we can't see your database, so can only infer how it is constructed. Any discussion of this sort needs to start with the table structure, which depends on the real-world situation behind the database. What is the database supposed to do? PHisaw wrote: Bruce, Thank you for replying. Per your message, I'm not sure what this line of code is supposed to do: Me.JobNumber.DefaultValue = """" & Me.JobNumber.Value & """" In researching on how to save field info and then repopulate field with saved info from previous record, this line of code is what I came across, but obviously it isn't working. Users have requested the ability to have more than one quote option for the same job number. Tom's suggestion was to have the main table relate to the subforms with JobNumber and Option fields - both PK's. Then a button on the main form that would save the job number from the previous record, insert it in the new record (new option, same job number) and increase the option by 1 based on last option number. I have a main quote table, tquotemainform, which supplies frepairquotemain form. On it are several subforms - labor, parts, and misc parts and all are related by JobNumber (autonumber and PK) and now by Option (also a PK - I'll change the name to something other than the reserved word). John Vinson responded that it may be the jobnumber in the related table. I asked how I could go about resolving the problem, but received no response. I just need to know if this is acutally possible as in example below with both fields being PKs. JobNumber Option 9549 1 9549 2 9550 1 9551 1 9551 2 Again, thanks for your help. Pam I don't know what exactly was in the original string, but I see a few things in the code you posted. [quoted text clipped - 56 lines] Thank in advance, Pam -- Message posted via http://www.accessmonster.com . |
Thread Tools | |
Display Modes | |
|
|