Update multiple records based on multiple fields?

R

Robert Jacobs

Thank you in advance, experts! I have a DB that creates multiple
instances of a piece of software. i.e. If I purchase 150 licenses of
MS Office, I enter it one time, and it creates 150 instances, with the
"swLicenseInstance" set to 1 of 150 / 2 of 150 / 3 of 150 / etc. This
is done because I NEED an individual record for each instance of the
software, so I can apply it to a specified piece of equipment. This
all works PERFECTLY using this code:

Dim db As Database
Set db = CurrentDb
For LicCount = 1 To Me.swNumberOfLicenses
db.Execute "INSERT INTO Software ([swTypeVersion],
[swAcquired Date], [swPurchase Price], [swPurchase From], [swCDKey],
[swLicenseNumber], [swInvoiceNumber], [Renew], [RenewByDate],
[swLicenseInstance]) " & _
"VALUES('" & Me.swTypeVersion & "','" &
Me.swAcquired_Date & "','" & Me.swPurchase_Price & "','" &
Me.swPurchase_From & "','" & Me.swCDKey & "','" & Me.swLicenseNumber &
"','" & Me.swInvoiceNumber & "','" & Me.Renew & "','" & Me.RenewByDate
& "','" & LicCount & " of " & Me.swNumberOfLicenses & "')"
Next
Set db = Nothing
DoCmd.Close acForm, "Software Multiple Records", acSaveYes


What I need is the ability to update these records. For MS Office,
this will never happen, as I get a new license number, etc. for new
purchases. BUT, for software like AntiVirus and Lotus Notes, we
sometimes purchase additional licenses, but they show up with all of
the same information - just extra licenses.

What I need to be able to do is take the field swLicenseInstance, and
increase the number after "of" for each record with a specified
swTypeVersion and swAcquired_Date (and maybe additional fields if I
feel I need that). So, if I have 1 of 150 thru 150 of 150, and I buy
30 new licenses, I need to be able to change all of the existing
licenses to 1 of 180 thru 150 of 180. Is this possible?

Thanks again, very much, for anybody who can help!!!!
 
B

Bob Quintal

:
Thank you in advance, experts! I have a DB that creates multiple
instances of a piece of software. i.e. If I purchase 150 licenses
of MS Office, I enter it one time, and it creates 150 instances,
with the "swLicenseInstance" set to 1 of 150 / 2 of 150 / 3 of 150
/ etc. This is done because I NEED an individual record for each
instance of the software, so I can apply it to a specified piece
of equipment. This all works PERFECTLY using this code:

Dim db As Database
Set db = CurrentDb
For LicCount = 1 To Me.swNumberOfLicenses
db.Execute "INSERT INTO Software ([swTypeVersion],
[swAcquired Date], [swPurchase Price], [swPurchase From],
[swCDKey], [swLicenseNumber], [swInvoiceNumber], [Renew],
[RenewByDate], [swLicenseInstance]) " & _
"VALUES('" & Me.swTypeVersion & "','" &
Me.swAcquired_Date & "','" & Me.swPurchase_Price & "','" &
Me.swPurchase_From & "','" & Me.swCDKey & "','" &
Me.swLicenseNumber & "','" & Me.swInvoiceNumber & "','" & Me.Renew
& "','" & Me.RenewByDate & "','" & LicCount & " of " &
Me.swNumberOfLicenses & "')"
Next
Set db = Nothing
DoCmd.Close acForm, "Software Multiple Records", acSaveYes


What I need is the ability to update these records. For MS
Office, this will never happen, as I get a new license number,
etc. for new purchases. BUT, for software like AntiVirus and
Lotus Notes, we sometimes purchase additional licenses, but they
show up with all of the same information - just extra licenses.

What I need to be able to do is take the field swLicenseInstance,
and increase the number after "of" for each record with a
specified swTypeVersion and swAcquired_Date (and maybe additional
fields if I feel I need that). So, if I have 1 of 150 thru 150 of
150, and I buy 30 new licenses, I need to be able to change all of
the existing licenses to 1 of 180 thru 150 of 180. Is this
possible?

Thanks again, very much, for anybody who can help!!!!
Click to expand...
It might be possible, but you really need to rethink yopur database
structure as it is not normalized.

You have one table that should be two tables or maybe even three.

Software and Allocations
Software contains the common info that is repeated in every instance
of your present table, plus
an ID number (Primary key)
swNumberOfLicenses

Allocations would contain
the ID as a Foreign key,
LicCount,
ComputerID as foreign key into the third table, Computers

Then you use a query to join the two, and build your string
Allocations.LicCount & " of " & Software.swNumberOfLicenses
in the forms and reports that need it, not in the table.
 
Top