Solved How to changes thousands of products prices in a retail business (The backed is SQL Server AZURE)

[nector]

Hi

I’m developing a code to help change prices for a supermarket, now the code below pulls all the prices, and so making the form load slow.

Operations of the form

• The form loads the prices at one go and one can easily scroll down and up
• Prices can be changed by applying a percentage up/down or select one by one or a number of lines using a check box
• Please note this is a retail business we expect 300000 products or more which have their prices changes at any time due to competition.
• The business rule requires us to allow prices changes and once changed all the 50 plus stores must reflect new prices

Is there a way to filter 30 lines at a go use next button for the next 20 line just like that or there is another better way.

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 [tblPricing]" _
    & " SET [FinalStatus]=Not NZ([FinalStatus],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

The forms screen shoot is also attached.

 

[arnelgp]

maybe use Pagination code found in the below link:
pagination | Access World Forums (access-programmers.co.uk)
and here:
Pagination | Access World Forums (access-programmers.co.uk)

 

[CJ_London]

now the code below pulls all the prices, and so making the form load slow.

Always a bad idea to pull through all records to subsequently filter. As an alternative to pagination, use criteria rather than filter

set your subform recordsource to say

SELECT *
FROM tblPrices
WHERE FALSE

and in your ( I assume) refresh button where I presume you have the lines

subformname.form.filter=filterstr
subformname.form.filteron=true

just use

subformname.form.recordsource = "SELECT * FROM tblPrices WHERE " & filterStr

 

[nector]

Many thanks CJ this will sure go long way in helping me clearly solve this problem.

Regards

Chris