UPDATE statement that takes into consideration a limited quantity

stephenlecompte
10-17-2004, 05:57 PM
I have a particular table PROJ_EQ-CPB that basically is structured with four fields: Room Number, Equipment Code, Quantity and Deployed. This table is not normalized and thus you can have several rows with the same equipment number per different room number with its different quantities.

Ex: 01302 equipment code could have 3 as a quantity in Room 1.281, 2 quantity in Room 1.282, and 13 quantity in Room 1.283- thus 3 different rows in PROJ_EQ-CPB. The deploy column is a Boolean type where if that particular quantity has been shipped then it is checked off as True!

What I have to do is take a total quantity that is shipped out (deployed out) and check it off back in this non-normalized table that its been deployed. Let's say 5 was deployed as a total for 01302 - I have to go back into the database and find those two particular quantites - 3 in Room 1.281 and 2 in Room 1.282 and check them off in their perspective field Deployed as True to match the total of 5 as deployed.

To do the above, I've decided to pull a query of the information as below, with the equipment code, and then quantity in ascending order. I go through and check off True in Deployed and add the quantity to a sCounter variable. When the sCounter variable becomes greater than the actual total collectively in the database (sCounter > sEquipTotal) and there is still more to match what's been deployed, I go ahead and assign it to another table PROJ_EQ-ACB.

Where I'm running into trouble is a highlight on the CPBRow.Update
I keep getting that highlighted with an error message of -2147467259 (80004005)': Key Column information is insufficient or incorrect. Too many rows were affected by update.

If I were better at writing Update queries I think I can avoid a lot of unncessary code and this error. But I'm still unsure because I still have to account for the sCounter not being greater than sEquipTotal.
Any suggestions is much appreciated!
Thanks for your time.


sCounter = sEquipDeploy
vCPBRow = "SELECT [PROJ_EQ-CPB].Equip, [PROJ_EQ-CPB].Quantity, [PROJ_EQ-CPB].Deployed
From [PROJ_EQ-CPB]
Where ((([PROJ_EQ-CPB].Equip) = '" & sEquip & "'))
ORDER BY [PROJ_EQ-CPB].Equip, [PROJ_EQ-CPB].Quantity"
CPBRow.Open (vCPBRow), gcnnData, adOpenDynamic, adLockOptimistic
CPBRow.MoveFirst
Do Until sCounter = CItem.Fields(1)
lblMonitor.Caption = "Adjusting " & sEquip & " which has only " &
sEquipDeploy & " deployed vs. CCTS's " & CItem.Fields(1)
If CPBRow.EOF Then MsgBox "Unknown error in program!"
sCounter = sCounter + CPBRow.Fields(1)
If sCounter > sEquipTotal Then
lblMonitor.Caption = sEquip & "'s quantity of deployed has been filled in CPB -
moving excess to ACB!"
'In this scenario any excess quantity is to go to the ACB table.
sExcess = Val(CItem.Fields(1)) - sEquipTotal
vAItem = "SELECT DISTINCTROW [ACB Items].[Equip#], Sum([ACB Items].
[Sum of Quantit]) AS [SumOfSum of Quantit] FROM [PROJ_EQ-ACB],
[ACB Items] GROUP BY [ACB Items].[Equip#]"
AItem.Open (vAItem), gcnnData, adOpenDynamic, adLockOptimistic
AItem.Find "[Equip] = '" & sEquip & "'"
If AItem.EOF Then GoTo skip_search
sTotal = Val(AItem.Fields(1)) + sExcess
AItem.Fields(1) = sTotal
AItem.Update
AItem.Close
GoTo skip_search
End If
CPBRow.Fields("Deployed") = True
CPBRow.Update
CPBRow.MoveNext
Loop
CPBRow.Close

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum