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

nector

Member
Local time
Today, 20:47
Joined
Jan 21, 2020
Messages
417
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.

Code:
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.


Prices Changes.png
 
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

Code:
SELECT *
FROM tblPrices
WHERE FALSE

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

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

just use
Code:
subformname.form.recordsource = "SELECT * FROM tblPrices WHERE " & filterStr
 
Many thanks CJ this will sure go long way in helping me clearly solve this problem.

Regards

Chris
 

Users who are viewing this thread

Back
Top Bottom