Prev: "The search key was not found in any record" solution
Next: Form OnClose vs. subform BeforeUpdate validation: the showdown
From: Robert Jacobs on 16 Jul 2010 16:08 First off, thank you any experts who have the answer to my final question! I'm almost done with this thing! My database holds all of our assets, and all of our software (boxed and open licenses). Each piece of software needs this info: (no record can have the same CD Key AND the same License Instance - one or the other can match, just not both) Product Name (i.e. Microsoft Office 2010) CD Key License Instance (i.e. 1 of 120, 2 of 120, etc.) Computer Assigned to All of this works... BUT, what I need is the capability to create multiple records from one entry into a form. The way my DB works, you have to enter all licenses as individual records, no matter how many instances you have. For example: I go out and purchase 120 open licenses of Microsoft Office Professional 2010, all with the same Product Name and CD key, and the Computer assigned to can be empty when first setup (can be assigned later) - but the instance needs to change from 1 of 120, 2 of 120, 3 of 120... etc. Is there any way to create a form, where you enter the Product Name and CD Key and the number of licenses you purchased (i.e. 120), and it creates 120 records with the License Instance automatically set to 1 of 120, 2 of 120, etc.? God, I know this is confusing, but if it can be accomplished, it would be GREATLY appreciated. I know I will get people asking why I set my DB up this way, and trust me, I tried other ways, this is just what works best for our particular scenario. In fact, if there is no way to do this, it is still worth my time to enter them all in 1 at a time... I just don't want to. I have other posts on other sites asking about other ways to do these licenses to work the way I need it to, and it just keeps coming back to this is the best route. Please don't try to fix that problem, please try to give a solution to the one I entered above. AGAIN - YOU ARE THE GREATEST, WHOEVER YOU ARE THAT FIXES THIS FOR ME!!!!!
From: XPS35 on 16 Jul 2010 16:37 Robert Jacobs wrote: > First off, thank you any experts who have the answer to my final > question! I'm almost done with this thing! My database holds all of > our assets, and all of our software (boxed and open licenses). > > Each piece of software needs this info: (no record can have the same > CD Key AND the same License Instance - one or the other can match, > just not both) > Product Name (i.e. Microsoft Office 2010) > CD Key > License Instance (i.e. 1 of 120, 2 of 120, etc.) > Computer Assigned to > > All of this works... BUT, what I need is the capability to create > multiple records from one entry into a form. The way my DB works, you > have to enter all licenses as individual records, no matter how many > instances you have. > > For example: I go out and purchase 120 open licenses of Microsoft > Office Professional 2010, all with the same Product Name and CD key, > and the Computer assigned to can be empty when first setup (can be > assigned later) - but the instance needs to change from 1 of 120, 2 of > 120, 3 of 120... etc. Is there any way to create a form, where you > enter the Product Name and CD Key and the number of licenses you > purchased (i.e. 120), and it creates 120 records with the License > Instance automatically set to 1 of 120, 2 of 120, etc.? > > God, I know this is confusing, but if it can be accomplished, it would > be GREATLY appreciated. I know I will get people asking why I set my > DB up this way, and trust me, I tried other ways, this is just what > works best for our particular scenario. In fact, if there is no way > to do this, it is still worth my time to enter them all in 1 at a > time... I just don't want to. I have other posts on other sites > asking about other ways to do these licenses to work the way I need it > to, and it just keeps coming back to this is the best route. Please > don't try to fix that problem, please try to give a solution to the > one I entered above. AGAIN - YOU ARE THE GREATEST, WHOEVER YOU ARE > THAT FIXES THIS FOR ME!!!!! Create an unbound form with 3 fields(txtProduct, txtKey and numLicenses) plus a commandbutton. For this button make a code like: Dim LicCount As Integer For LicCount = 1 To Me.numLicenses DoCmd.RunSQL "INSERT INTO YourTable ([Product Name], [CD Key], [License Instance]) " & _ "VALUES('" & Me.txtProduct & "','" & Me.txtKey & "','" & LicCount & " of " & Me.numLicenses & "')" Next -- Groeten, Peter http://access.xps350.com --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Robert Jacobs on 16 Jul 2010 17:39 On Jul 16, 3:37 pm, XPS35 <xps...(a)gmail.com> wrote: > Robert Jacobs wrote: > > First off, thank you any experts who have the answer to my final > > question! I'm almost done with this thing! My database holds all of > > our assets, and all of our software (boxed and open licenses). > > > Each piece of software needs this info: (no record can have the same > > CD Key AND the same License Instance - one or the other can match, > > just not both) > > Product Name (i.e. Microsoft Office 2010) > > CD Key > > License Instance (i.e. 1 of 120, 2 of 120, etc.) > > Computer Assigned to > > > All of this works... BUT, what I need is the capability to create > > multiple records from one entry into a form. The way my DB works, you > > have to enter all licenses as individual records, no matter how many > > instances you have. > > > For example: I go out and purchase 120 open licenses of Microsoft > > Office Professional 2010, all with the same Product Name and CD key, > > and the Computer assigned to can be empty when first setup (can be > > assigned later) - but the instance needs to change from 1 of 120, 2 of > > 120, 3 of 120... etc. Is there any way to create a form, where you > > enter the Product Name and CD Key and the number of licenses you > > purchased (i.e. 120), and it creates 120 records with the License > > Instance automatically set to 1 of 120, 2 of 120, etc.? > > > God, I know this is confusing, but if it can be accomplished, it would > > be GREATLY appreciated. I know I will get people asking why I set my > > DB up this way, and trust me, I tried other ways, this is just what > > works best for our particular scenario. In fact, if there is no way > > to do this, it is still worth my time to enter them all in 1 at a > > time... I just don't want to. I have other posts on other sites > > asking about other ways to do these licenses to work the way I need it > > to, and it just keeps coming back to this is the best route. Please > > don't try to fix that problem, please try to give a solution to the > > one I entered above. AGAIN - YOU ARE THE GREATEST, WHOEVER YOU ARE > > THAT FIXES THIS FOR ME!!!!! > > Create an unbound form with 3 fields(txtProduct, txtKey and numLicenses) > plus a commandbutton. For this button make a code like: > > Dim LicCount As Integer > > For LicCount = 1 To Me.numLicenses > DoCmd.RunSQL "INSERT INTO YourTable ([Product Name], [CD Key], > [License Instance]) " & _ > "VALUES('" & Me.txtProduct & "','" & Me.txtKey & "','" & > LicCount & " of " & Me.numLicenses & "')" > Next > > -- > Groeten, > > Peterhttp://access.xps350.com > > --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- That worked perfectly! The only annoying part about it is that it prompts you all 120 times if you want to append 1 record to your table, and you have to click yes 120 times. Do you know of a way to skip that message (or default the answer to yes for each message)? If not - I don't really care - you just made my week! Thanks a ton Peter!!!!!
From: Salad on 16 Jul 2010 17:41 Robert Jacobs wrote: > On Jul 16, 3:37 pm, XPS35 <xps...(a)gmail.com> wrote: > >>Robert Jacobs wrote: >> >>>First off, thank you any experts who have the answer to my final >>>question! I'm almost done with this thing! My database holds all of >>>our assets, and all of our software (boxed and open licenses). >> >>>Each piece of software needs this info: (no record can have the same >>>CD Key AND the same License Instance - one or the other can match, >>>just not both) >>> Product Name (i.e. Microsoft Office 2010) >>> CD Key >>> License Instance (i.e. 1 of 120, 2 of 120, etc.) >>> Computer Assigned to >> >>>All of this works... BUT, what I need is the capability to create >>>multiple records from one entry into a form. The way my DB works, you >>>have to enter all licenses as individual records, no matter how many >>>instances you have. >> >>>For example: I go out and purchase 120 open licenses of Microsoft >>>Office Professional 2010, all with the same Product Name and CD key, >>>and the Computer assigned to can be empty when first setup (can be >>>assigned later) - but the instance needs to change from 1 of 120, 2 of >>>120, 3 of 120... etc. Is there any way to create a form, where you >>>enter the Product Name and CD Key and the number of licenses you >>>purchased (i.e. 120), and it creates 120 records with the License >>>Instance automatically set to 1 of 120, 2 of 120, etc.? >> >>>God, I know this is confusing, but if it can be accomplished, it would >>>be GREATLY appreciated. I know I will get people asking why I set my >>>DB up this way, and trust me, I tried other ways, this is just what >>>works best for our particular scenario. In fact, if there is no way >>>to do this, it is still worth my time to enter them all in 1 at a >>>time... I just don't want to. I have other posts on other sites >>>asking about other ways to do these licenses to work the way I need it >>>to, and it just keeps coming back to this is the best route. Please >>>don't try to fix that problem, please try to give a solution to the >>>one I entered above. AGAIN - YOU ARE THE GREATEST, WHOEVER YOU ARE >>>THAT FIXES THIS FOR ME!!!!! >> >>Create an unbound form with 3 fields(txtProduct, txtKey and numLicenses) >>plus a commandbutton. For this button make a code like: >> >>Dim LicCount As Integer >> >>For LicCount = 1 To Me.numLicenses >> DoCmd.RunSQL "INSERT INTO YourTable ([Product Name], [CD Key], >>[License Instance]) " & _ >> "VALUES('" & Me.txtProduct & "','" & Me.txtKey & "','" & >>LicCount & " of " & Me.numLicenses & "')" >>Next >> >>-- >>Groeten, >> >>Peterhttp://access.xps350.com >> >>--- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- > > > That worked perfectly! The only annoying part about it is that it > prompts you all 120 times if you want to append 1 record to your > table, and you have to click yes 120 times. Do you know of a way to > skip that message (or default the answer to yes for each message)? If > not - I don't really care - you just made my week! Thanks a ton > Peter!!!!! Docmd.setwarnings false ....statements docmd.setwarnings true
From: paii, Ron on 19 Jul 2010 08:21
"Robert Jacobs" <robertjacobsit(a)gmail.com> wrote in message news:ffb50bf5-8108-45df-80ca-374a7dc6f7b9(a)b35g2000yqi.googlegroups.com... On Jul 16, 3:37 pm, XPS35 <xps...(a)gmail.com> wrote: > Robert Jacobs wrote: > > First off, thank you any experts who have the answer to my final > > question! I'm almost done with this thing! My database holds all of > > our assets, and all of our software (boxed and open licenses). > > > Each piece of software needs this info: (no record can have the same > > CD Key AND the same License Instance - one or the other can match, > > just not both) > > Product Name (i.e. Microsoft Office 2010) > > CD Key > > License Instance (i.e. 1 of 120, 2 of 120, etc.) > > Computer Assigned to > > > All of this works... BUT, what I need is the capability to create > > multiple records from one entry into a form. The way my DB works, you > > have to enter all licenses as individual records, no matter how many > > instances you have. > > > For example: I go out and purchase 120 open licenses of Microsoft > > Office Professional 2010, all with the same Product Name and CD key, > > and the Computer assigned to can be empty when first setup (can be > > assigned later) - but the instance needs to change from 1 of 120, 2 of > > 120, 3 of 120... etc. Is there any way to create a form, where you > > enter the Product Name and CD Key and the number of licenses you > > purchased (i.e. 120), and it creates 120 records with the License > > Instance automatically set to 1 of 120, 2 of 120, etc.? > > > God, I know this is confusing, but if it can be accomplished, it would > > be GREATLY appreciated. I know I will get people asking why I set my > > DB up this way, and trust me, I tried other ways, this is just what > > works best for our particular scenario. In fact, if there is no way > > to do this, it is still worth my time to enter them all in 1 at a > > time... I just don't want to. I have other posts on other sites > > asking about other ways to do these licenses to work the way I need it > > to, and it just keeps coming back to this is the best route. Please > > don't try to fix that problem, please try to give a solution to the > > one I entered above. AGAIN - YOU ARE THE GREATEST, WHOEVER YOU ARE > > THAT FIXES THIS FOR ME!!!!! > > Create an unbound form with 3 fields(txtProduct, txtKey and numLicenses) > plus a commandbutton. For this button make a code like: > > Dim LicCount As Integer > > For LicCount = 1 To Me.numLicenses > DoCmd.RunSQL "INSERT INTO YourTable ([Product Name], [CD Key], > [License Instance]) " & _ > "VALUES('" & Me.txtProduct & "','" & Me.txtKey & "','" & > LicCount & " of " & Me.numLicenses & "')" > Next > > -- > Groeten, > > Peterhttp://access.xps350.com > > --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- >That worked perfectly! The only annoying part about it is that it >prompts you all 120 times if you want to append 1 record to your >table, and you have to click yes 120 times. Do you know of a way to >skip that message (or default the answer to yes for each message)? If >not - I don't really care - you just made my week! Thanks a ton >Peter!!!!! Use Execute instead of RunSQL dim db as Database Set db = currentdb For LicCount = 1 To Me.numLicenses db.execute "INSERT INTO YourTable ([Product Name], [CD Key], [License Instance]) " & _ "VALUES('" & Me.txtProduct & "','" & Me.txtKey & "','" & LicCount & " of " & Me.numLicenses & "')" Next set db = nothing |