Prev: "The search key was not found in any record" solution
Next: Form OnClose vs. subform BeforeUpdate validation: the showdown
From: Robert Jacobs on 19 Jul 2010 10:10
On Jul 19, 7:21 am, "paii, Ron" <n...(a)no.com> wrote:
> "Robert Jacobs" <robertjacob...(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



Awesome awesome awesome. Thank you to everybody - XPS35 - your code
worked perfectly... Salad, your suggestion on replying to the
message worked perfectly as well. Ron, I used your code as it seemed
to work a bit faster and with less quirkiness than answering each
message with true or false. Again, all around... awesome! Thanks
guys!
First  |  Prev  | 
Pages: 1 2
Prev: "The search key was not found in any record" solution
Next: Form OnClose vs. subform BeforeUpdate validation: the showdown