How to update bulk selling prices at one go

[nector]

We are keeping some SKU or product lines around 45,000 in the product table now updating prices line by is very cumbersome and time consuming, and so I'm compelled to building a split form which shows all the products with the listed controls and commands:

(1) Percentage Change (This is where the percentage price change will be inserted)
(2) Check Action (Checking this box means all the products lines will be selected)
(3) The Run update Here (This command is used to take into account percentage change and increase all checked Product and prices by the same percentage)
(4) Refresh Data (This data is meant to reflect the new changes on the same screen)

Problem area

(1) Running the command does to increase the prices
(2) Check Action does not allow to deselect if some products are not required to have the price increase

VBA Code

Option Compare Database
Option Explicit

Private Sub CheckStatus_Click()
Dim strSQL As String
'make sure any unsaved changes are saved to avoid conflicts:
    DoCmd.RunCommand (acCmdSaveRecord)
    'build the update query SQL string:
    strSQL = "UPDATE [tblProducts]" _
    & " SET [Sales]=Not NZ([Sales],0)"
    'if the form has an active filter - include it:
    If Me.Filter <> "" And Me.FilterOn = True Then
        strSQL = strSQL & " WHERE " & Me.Filter
    End If
    'run the update query SQL string:
    CurrentDb.Execute strSQL
    'refresh the form so that changes in the table "bubble-up" to the form:
    Me.Refresh
exit_sub:
End Sub

 

[Minty]

I'm intrigued by what you think this line will achieve:

SET [Sales]=Not NZ([Sales],0)

I doubt it's what you think?
Have you examined the values. Have you tried a simple select query to "see" the possible results?

SELECT Sales, Not NZ([Sales],0) as MyNewValue FROM tblProducts

 

[The_Doc_Man]

According to strict syntax rules,

SET [Sales] = NOT NZ( [Sales], 0 )

Will take all null [Sales] and turn them into -1 - because 0 is ALSO the value of FALSE, NOT is a logical as opposed to a math operator, and the logical negation of FALSE is TRUE, which is represented as -1. The final step is that the equals-sign will assure that you have -1 in whatever data type [Sales] happens to be.

 

[Ken Sheridan]

We are keeping some SKU or product lines around 45,000 in the product table now updating prices line by is very cumbersome and time consuming, and so I'm compelled to building a split form which shows all the products with the listed controls and commands:

(1) Percentage Change (This is where the percentage price change will be inserted)
(2) Check Action (Checking this box means all the products lines will be selected)
(3) The Run update Here (This command is used to take into account percentage change and increase all checked Product and prices by the same percentage)
(4) Refresh Data (This data is meant to reflect the new changes on the same screen)

Problem area

(1) Running the command does to increase the prices
(2) Check Action does not allow to deselect if some products are not required to have the price increase

VBA Code

Option Compare Database
Option Explicit

Private Sub CheckStatus_Click()
Dim strSQL As String
'make sure any unsaved changes are saved to avoid conflicts:
    DoCmd.RunCommand (acCmdSaveRecord)
    'build the update query SQL string:
    strSQL = "UPDATE [tblProducts]" _
    & " SET [Sales]=Not NZ([Sales],0)"
    'if the form has an active filter - include it:
    If Me.Filter <> "" And Me.FilterOn = True Then
        strSQL = strSQL & " WHERE " & Me.Filter
    End If
    'run the update query SQL string:
    CurrentDb.Execute strSQL
    'refresh the form so that changes in the table "bubble-up" to the form:
    Me.Refresh
exit_sub:
End Sub

I think the following is what you need:

1. Format the txtPrices control as Percent.
2. Change the CheckStatus control's Click event procedure to:

Private Sub CheckStatus_Click()

    Dim strSQL As String
    'make sure any unsaved changes are saved to avoid conflicts:
    DoCmd.RunCommand (acCmdSaveRecord)
    'build the update query SQL string:
    strSQL = "UPDATE [tblProducts] SET [Sales]= Not [Sales] "
    'if the form has an active filter - include it:
    If Me.Filter <> "" And Me.FilterOn = True Then
        strSQL = strSQL & " WHERE " & Me.Filter
    End If
    'run the update query SQL string:
    CurrentDb.Execute strSQL
    'refresh the form so that changes in the table "bubble-up" to the form:
    Me.Refresh
exit_sub:

End Sub

3. Change the CmdUpdatePrices control's Click event procedure to:

Private Sub CmdUpdatePrices_Click()

    Dim strSQL As String
    
    If Nz(Me.txtPrices, 0) <> 0 Then
        strSQL = "UPDATE tblProducts " & _
            "SET dftPrc = dftPrc * (1 + " & Me.txtPrices & ") " & _
            " WHERE Sales = TRUE"
        CurrentDb.Execute strSQL
    End If
    
    Me.Refresh

End Sub

An amended copy of your file is attached:

 

[Ken Sheridan]

As it stands, with my amendments, your form only allows prices to be increased. The form below is from one of my demo files and allows proces to be either increased or decreased by means of an option group. The options values are 1 for increasing prices, and -1 for decreasing them.

The code for the Update button's Click event procedure is:

Private Sub cmdUpdate_Click()

    Dim strSQL As String
    
    strSQL = "UPDATE NorthwindProducts " & _
        "SET UnitPrice = UnitPrice * " & _
         (1 + (Me.txtPercent * Me.optIncreaseDecrease))
    
    If Not IsNull(Me.txtPercent) Then
        CurrentDb.Execute strSQL, dbFailOnError
        Me.Refresh
    End If
    
End Sub