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 forms screen shoot is also attached.
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
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.