Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Erie, PA
    Posts
    1

    Wink Unanswered: Storing the output of a calculation in a table

    Hope someone can help me out with this.

    I need some help in identifying a function in VBA which takes the calculated output and stores it in a table. For example, in my code (pasted below), I want 'Todays Date', ' currentLyValue' and 'prevLyValue' stored in a table that I created in the same database. Everytime I run this calculation, the first record of the table should read the latest values. This way, I hope to maintain a running history of my values. Is there a VBA function which basically creates a new record and stores the values I want in a table?

    Thanks.

    Regards,
    Sid


    '_________________________________________________ _________
    '**************Code Begins Here****************************
    '_________________________________________________ __________
    Private Sub Calculate_LY_Click()
    On Error GoTo Err_Calculate_LY_Click
    'Declarations
    Dim prevLyDate As Date, todaysDate As Date, tempDateVar As Date
    Dim prevLyValue As Single, currentLyValue As Single
    Dim db As DAO.Database, rst As DAO.Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Fleet List")

    prevLyDate = InputBox("Enter the Date when the LY value was last calculated (mm/dd/yy):")
    prevLyValue = InputBox("Enter the value of LY when it was last calculated:")
    todaysDate = Now

    If rst.BOF And rst.EOF Then
    MsgBox "The Fleet List table is empty. There are no records to process"
    Else
    rst.MoveFirst
    Do Until rst.EOF
    tempDateVar = rst.Fields(5)
    If (tempDateVar > prevLyDate) Then 'Means its a new shipped unit
    currentLyValue = prevLyValue + ((DateDiff("ww", prevLyDate, tempDateVar) * (1 / 52.5)))
    If (rst.EOF = True) Then
    currentLyValue = currentLyValue 'try to output the current value from here itself
    Else
    prevLyValue = currentLyValue
    End If
    Else 'Means its an old unit
    currentLyValue = prevLyValue + ((DateDiff("ww", prevLyDate, todaysDate) * (1 / 52.5)))
    If (rst.EOF = True) Then
    currentLyValue = currentLyValue
    Else
    prevLyValue = currentLyValue
    End If
    End If
    rst.MoveNext
    Loop

    MsgBox "Today's Date is " & Now
    MsgBox "The current LY value is " & currentLyValue
    MsgBox "The previous LY value is " & prevLyValue

    Set rst = Nothing
    Set db = Nothing

    End If

    Exit_Calculate_LY_Click:
    Exit Sub

    Err_Calculate_LY_Click:
    MsgBox Err.Description
    Resume Exit_Calculate_LY_Click

    End Sub
    '_________________________________________________ _________
    '**************Code Ends Here*******************************
    '_________________________________________________ __________

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Storing the output of a calculation in a table
    Hope someone can help me out with this.

    I need some help in identifying a function in VBA which takes the calculated output and stores it in a table. For example, in my code (pasted below), I want 'Todays Date', ' currentLyValue' and 'prevLyValue' stored in a table that I created in the same database. Everytime I run this calculation, the first record of the table should read the latest values. This way, I hope to maintain a running history of my values. Is there a VBA function which basically creates a new record and stores the values I want in a table?

    Thanks.

    Regards,
    Sid
    No.

    But you can write some code to do it tho ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •