Saving calculated valued in an archive table

[Swiftraven]

Hello,
I accidentally posted this in the Access Forms forum when it really belongs here. I just have to figure out how to delete it from that forum

Here is my problem:
The database I am working on is used to forecast inventory needs for the program I am on. When one of two different data tables has new data imported over the old data, I need to run the calculations on the old data, save the calculated values to an "archive" table along with an autonumber key field, the part number field, and the date updated as a field, then overwrite the old data table with the new data. The user then has the option to look up a part and see the new forecasted inventory needs or look up what the forecasted need was on a specific date last week/month/year.

I am not clear on how to loop through the query results to gather each records data, perform the calculations on that data, then store it in a different table. This should all happen without the user seeing it going on.

Any suggestiong/help is greatly appreciated.

Thanks
Jason

 

[RickSpr]

I have notified the Tek-Tips staff that you want the other post deleted. (If you should have the problem again, just go back into the post you want deleted and click on the link at the bottom of your message: "Inappropriate post? If so, Red Flag it!". A form will open for you to explain why want it deleted.)

You'll need to write VBA code to do this. What you'll need to use is the DAO Object Model, more specifically the Recordset object. This provides for looping through records in tables and queries from code. I'll give some sample code below, but you ought to look up the Recordset object in the Help system and study its properties and methods, and the examples. It's a bit of a steep learning curve, even if you already write VBA code, but the flexibility it gives you is tremendous.

    Dim rstUpdData As DAO.Recordset  ' inv updates
    Dim rstInv As DAO.Recordset      ' inv table
    Dim rstInvArchive As DAO.Recordset  ' archive table

    ' 'open' the tables for access by the program
    Set rstUpdData = CurrentDb.OpenRecordset("the query")
    Set rstInv = CurrentDb.OpenRecordset("Inventory")
    Set rstArchive = CurrentDB.OpenRecordset("InvArchive")
    ' loop through the new inventory data
    While Not rstUpdData.EOF
        ' find the corresponding inventory record
        rstInv.FindFirst "InvID = '" & rstUpdData!InvID & "'"
        If rstInv.NoMatch Then
            ' error: invalid inventory identifier
        Else
            ' access some fields, do some calcs
            varSomething = rstInv!SomeField + rstInv!OtherField
            ' create a new record in the archive tbl
            rstArchive.NewRec
            ' move data from Inventory to archive
            rstArchive!SomeField = rstInv!SomeField
            rstArchive!OtherField = rstInv!OtherField
            ' save the new archive record
            rstArchive.Update
            ' lock the inventory record so I can update it
            rstInv.Edit
            ' now update the inventory record
            rstInv!SomeField = rstUpdData!SomeField
            ' and save it
            rstInv.Update
        End If
        ' move to the next update record
        rstUpdData.MoveNext
    Loop
    ' always set DAO object variables to nothing when done
    Set rstUpdData = Nothing
    Set rstInv = Nothing
    Set rstArchive = Nothing

This should give you the idea.

It sounds as if the new inventory data is coming from a query. It's that query you name in the OpenRecordset call for rstUpdData above. Rick Sprague