Make field value static

[asol]

Hello I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top. :banghead:

Private Sub ProductID_AfterUpdate()

    Dim qflPrice As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlQry As String
    Dim instID As Integer
    
    instID = Me.Form!ProductID.Value
    sqlQry = "SELECT Products.Price FROM Products WHERE Products.ProductID = " & instID & ""
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlQry)
    Me.flPrice.Value = rs!Price
    
End Sub
Private Sub ExtrasID_Change()
    Dim extrID As Integer
    Dim addNum As Integer
    Static floorPrice As Integer
    Static sumPrice As Integer
    
    extrID = Me.ExtrasID.Value
    floorPrice = Me.flPrice.Value
          
    Select Case extrID
    
        Case Is = 1
            addNum = 5
            sumPrice = floorPrice + addNum
        Case Is = 2
            addNum = 10
            sumPrice = floorPrice + addNum
        Case Is = 3
            addNum = 15
            sumPrice = floorPrice + addNum
    End Select
    
    Me.flPrice.Value = sumPrice
    
End Sub

 

[Minty]

I don' t think you have the case statement correct. It can be simplified to;

Select Case extrID
    
        Case 1
             sumPrice = floorPrice + 5
        Case 2
            sumPrice = floorPrice + 10
        Case 3
            sumPrice = floorPrice + 15
    End Select
    
    Me.flPrice.Value = sumPrice

Also why have you declared sumPrice and floorPrice as Static Variables?

 

[asol]

I do understand it can be simplified but it is not the issue, I have declared floorPrice as static so that the its value doesn't change when I add 5, 10 or 15. The problems come out when I assign new value by making change to ExtrasID for some reason Me.flPrice.Value starts to accumulate values of 5, 10, 15. Lets say the price of the floor is 45. If case of extrID at first is 1 and than gets changes to case 2 the value of the floor becomes 60, when it has to be 55 as I want to add 10 as per case 2 towards the original value of the floor price. Somehow when doing maths with variable floorPrice it assign new value to Me.flPrice.Value which causes issues.

 

[asol]

Lets say I modify my code this way

Private Sub ExtrasID_Change()
    Dim extrID As Integer
    Static floorPrice As Integer
    Static sumPrice As Integer
    
    extrID = Me.ExtrasID.Value
    floorPrice = Me.flPrice.Value
          
    Select Case extrID
    
        Case Is = 1
           sumPrice = floorPrice + 5
           MsgBox sumPrice
        Case Is = 2
           sumPrice = floorPrice + 10
           MsgBox sumPrice
        Case Is = 3
           sumPrice = floorPrice + 15
           MsgBox sumPrice
    
    End Select
    
    'Me.flPrice.Value = sumPrice
    
End Sub

MsgBox gives me the correct values of sumPrice, but as soon as I assign these value to Me.flPrice.Value they start to acumulate I need to find they way how to reset the Me.flPrice.Value to initial value which has been assign earlier through sql querry.

 

[asol]

Have fixed the problem here is how. Thank for trying anyway.

Option Compare Database
Public rsqryPrice As Integer
Option Explicit
Private Sub ProductID_AfterUpdate()

    Dim qflPrice As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlQry As String
    Dim instID As Integer
    
    instID = Me.Form!ProductID.Value
    sqlQry = "SELECT Products.Price FROM Products WHERE Products.ProductID = " & instID & ""
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlQry)
    Me.flPrice.Value = rs!Price
    rsqryPrice = rs!Price
    
End Sub
Private Sub ExtrasID_Change()
    Dim extrID As Integer
    Static floorPrice As Integer
    Static sumPrice As Integer
    Static numPrice As Integer
    
    extrID = Me.ExtrasID.Value
    
    floorPrice = Me.flPrice.Value
    numPrice = rsqryPrice
    
    Select Case extrID
    
        Case Is = 1
           Me.flPrice.Value = numPrice + 5

        Case Is = 2
           Me.flPrice.Value = numPrice + 10

        Case Is = 3
           Me.flPrice.Value = numPrice + 15
    
    End Select

 

[spikepl]

The code does exactly what you told it to do. I have difficulties understanding what you want. Also, calling your code in On Change does not make much sense - that type of things is normally done in AfterUpdate, instead of repeated on each keystroke in OnChange.

Your use of Static also makes no sense because you assign a value to them on each call, which then means that there is no reason to declare them as Static because they do not have to remember anything between calls (which is the main raison d'être of a Static variable).

Set a break point and step through your code to see exactly what it does. If you do not know how then follow the "Step through code" section of this post: http://www.access-programmers.co.uk/forums/showthread.php?t=149429