'VISUAL MACRO

'NAME: VMPRTMNT_UpdatePartPrices.vms
'DESCRIPTION: Updates part prices based on user supplied
'   info for each part:
'   part.user_1 contains effective date
'   part.user_2 contains increase value
'   part.user_3 contains '%' or '$' indicating value type
'Prices are updated in PART, CUSTOMER_PRICE, DISCOUNT_PRICE
'
'AUTHOR: Peter Erdman
'REVISION CONTROL:
'   1.0 10/5/04 PDE Initial Release

Dim con
Dim rst
Dim rst2
Dim cmd
Dim strCon
Dim strSql
Dim EffectiveDate
Dim ChangeType
Dim ChangeValue
Dim CurUnitPrice
Dim CurPartID

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = 1

On Error Resume Next

'Get criteria from the user
EffectiveDate = InputBox("Enter Effective Date")
'EffectiveDate = DateValue(EffectiveDate)

'Make a DB connection
'The following line is for SqlBase
strCon = "Provider=SQLBASEOLEDB;Data Source=SB638VE;User ID=sysadm;Password=sysadm"
'The following line is for SqlServer
'strCon = "driver=SQL Server;server=BAGHERRA\LOCAL;uid=sysadm;pwd=sysadm;database=vmfg"
Set con = CreateObject("ADODB.Connection")
con.Open strCon

'Open a recordset of all parts needing updating
strSql = "SELECT id, unit_price, user_1, user_2, user_3 FROM part WHERE
   user_1 = '" & EffectiveDate & "'"
Set rst = CreateObject("ADODB.Recordset")
rst.Open strSql, con, adOpenStatic, adLockOptimistic, adCmdText

'Loop through each part changing the unit price in all related records
Do While Not rst.EOF
   'Load up parameters for this part
   CurPartID = rst.Fields("id")
   ChangeType = rst.Fields("user_3")
   ChangeValue = CCur(rst.Fields("user_2"))

   'Update the part table
   CurUnitPrice = CCur(rst.Fields("unit_price"))
   If ChangeType = "$" Then
      rst.Fields("unit_price") = CurUnitPrice + ChangeValue
   End If
   If ChangeType = "%" Then
      rst.Fields("unit_price") = CurUnitPrice + (CurUnitPrice *
         ChangeValue /100)
   End If

   'Update the customer_price table
   strSql = "SELECT unit_price_1, unit_price_2, unit_price_3, " & _
      "unit_price_4, unit_price_5, unit_price_6, unit_price_7, " & _
      "unit_price_8, default_unit_price FROM customer_price " & _
      "WHERE part_id = '" & CurPartID & "'"
   Set rst2 = CreateObject("ADODB.Recordset")
   rst2.Open strSql, con, adOpenStatic, adLockOptimistic, adCmdText

   If Not rst2.EOF Then
      Do While rst2.EOF
         If ChangeType = "$" Then
            If rst2.Fields("unit_price_1") <> "" Then
               CurUnitPrice = CCur(rst2.Fields("unit_price_1"))
               rst2.Fields("unit_price_1") = CurUnitPrice + ChangeValue
            End If

*Code Edited Out*

            If rst2.Fields("default_unit_price") <> "" Then
               CurUnitPrice = CCur(rst2.Fields("default_unit_price"))
               rst2.Fields("default_unit_price") = CurUnitPrice +
                  (CurUnitPrice * ChangeValue /100)
            End If
         End If
         rst2.Update
         rst2.MoveNext
      Loop
   End If

   'Go onto the next part
   rst.Update
   rst.MoveNext
Loop

'Cleanup
rst.Close
rst2.Close
Set con = Nothing
Set cmd = Nothing
Set strCon = Nothing
Set strSql = Nothing