Calculating daily stock totals

[chrisdd]

Hi,



I've written this code and can't see for the life of me what is wrong with it - idea being that it is meant to check if monetary value for stock was calculated on the previous day and if it wasn't - work it out, it should run first thing in the morning by the first user to open the database.

addstockvalue = DSum("Subtotal", "Stock", "[Despatchno] Is Null")






latestdate = DMax("[StockDate]", "DailyStockValue")


yesterday = Date - 1




If latestdate <> yesterday Then


Dim DailyStockValue As DAO.Recordset


Set DailyStockValue = CurrentDb.OpenRecordset("SELECT * FROM [DailyStockValue]")


DailyStockValue.AddNew
DailyStockValue![StockDate] = Date - 1
DailyStockValue![StockTotal] = addstockvalue


DailyStockValue.Update
DailyStockValue.Close
Set DailyStockValue = Nothing

Any help would be greatly appreciated.

Thanks

[kd2017]

Are there any error messages showing up? What happens if your calls to DSum or DMax don't find any records and return null values? I don't immediately see any errors in your code but it's out of context. Maybe you could provide your database for review?

I've refactored your code a little bit but it doesn't do much good without context.

On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim addstockvalue As Variant
    Dim latestdate As Variant
    
    addstockvalue = DSum("Subtotal", "Stock", "[Despatchno] Is Null")
    latestdate = DMax("[StockDate]", "DailyStockValue")
    
    If IsNull(addstockvalue) Then
        'WHAT DO YOU WANT TO DO IF DSUM DIDN'T FIND ANY RECORDS AND RETURNS A NULL?
        
    End If
    
    If IsNull(latestdate) Then
        'WHAT DO YOU WANT TO DO IF DMAX DIDN'T FIND ANY RECORDS AND RETURNS A NULL?
        
    End If
    
    yesterday = Date - 1

    If latestdate <> yesterday Then
        Set db = CurrentDb
        db.Execute "INSERT INTO DailyStockValue (StockDate, StockTotal) VALUES (#" & (Date - 1) & "#, " & addstockvalue & ");", dbFailOnError
    End If

ExitHandler:
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , Err.Number
    Resume ExitHandler

Side note: Make sure you declare your variables and put Option Explicit on the top of all your code files

PS: Sometimes when you're using functions like DSum, DMax, etc. it can be helpful to wrap the functions in a Nz function. The Nz function gives you a convenient way to return a different value of the DMax or whatever function returns a null.

someVariable = Nz(DMax(expression, domain, criteria), 0) 'returns zero if DMax were to return null
someVariable = Nz(DMax(expression, domain, criteria), 100) 'returns 100 if DMax were to return null
someVariable = Nz(DMax(expression, domain, criteria)) 'The second parameter is optional and will return zero by default

https://docs.microsoft.com/en-us/pre...v%3Doffice.11)

[chrisdd]

Thanks - used code above and works fine, just stops working when I compile as .accde and I've no idea why!!