• Create Future Records for a item


    I am looking to create an annual record for a list of assets, beginning on when the asset is purchased and going forward. For example, if I bought a car in 2008, I would need a record for 2008, 2009, etc, up to a fixed year for all records, ie 2015. The start date will be different for each item in the list, but the end date will be the same for all records. Any suggestion?

    Basic Table Structure is:

    Year Acquired
    Asset Description

    Viewing 0 reply threads
    • #1170405

      A belated welcome to the Lounge!

      Will the first record for each asset be in that table, or do you need to read the list of assets from another table?

      • #1170613

        Thanks, I joined a while back and have searched the forum to solve many problems. However, this is one I haven’t been able to resolve yet.

        Not being a database “Expert”, I’ll use an example..

        The base table lists ID, date aquired, description and cost:

        B0342, 2008, Honda, 23000

        I need to create a query that adds an annual record:

        ID, Date Aquired, Year, Cost, Annual Expenses
        B0342, 2008, 2008, Honda, 23000, 1400
        B0342, 2008, 2009, Honda, 23000, 1450
        B0342, 2008, 2010, Honda, 23000, 1500
        B0342, 2008, 2011, Honda, 23000, 1300
        B0342, 2008, 2012, Honda, 23000, 1350

        The Annual Expenses for past years will be pulled from another table that has ID, Year, Annual Expenses. The costs for future years will be calculated based on a % of the cost (I can do that part with iif statements).

        Does that help explain the question?

        • #1170619

          You should be able to use the following code as a starting point. You’ll have to modify it for your situation. The code requires a reference to the Microsoft DAO 3.6 Object Library.

          [codebox]Sub AddRecords()
          Dim dbs As DAO.Database
          Dim rstIn As DAO.Recordset
          Dim rstOut As DAO.Recordset
          Dim lngYear As Long

          On Error GoTo ErrHandler

          Set dbs = CurrentDb
          Set rstIn = dbs.OpenRecordset(“tblBase”, dbOpenDynaset)
          Set rstOut = dbs.OpenRecordset(“tblData”, dbOpenDynaset)

          Do While Not rstIn.EOF
          For lngYear = rstIn![Year Acquired] To 2015
          rstOut!ID = rstIn!ID
          rstOut![Year Acquired] = rstIn![Year Acquired]
          rstOut!Year = lngYear
          rstOut![Asset Description] = rstIn![Asset Description]
          rstOut!Cost = rstIn!Cost
          Next lngYear

          On Error Resume Next
          Set rstOut = Nothing
          Set rstIn = Nothing
          Set dbs = Nothing
          Exit Sub

          MsgBox Err.Description, vbExclamation
          Resume ExitHandler
          End Sub
          I have attached a sample database with two tables and a code module containing the above code. If you run the procedure AddRecords, the table tblData will be populated using the records from tblBase.

          • #1170637

            Hans, Thank you. I have not used modules before (I am a dangerous novice). A couple of questions about this: It is saving the data to a table. Is there a way to do this and have the results in a query? The reason being that the base table is updated monthly (completely replaced from an external source). Also, can the input into the module be a query? The base data has dates YYYYMMDD, but I create a year field based upon different criteria (it may or may not be the same year). I have a query that provides the base year, but not a table.

            While I don’t understand everything in the module, I can see enough as to how it works and how I can use it. I appreciate your help!

            One last question, I am using Access 2000 – Will this work with older versions of access?


            • #1170678

              The input for the code can be a query as well as a table: in the line

              Set rstIn = dbs.OpenRecordset(“tblBase”, dbOpenDynaset)

              you can replace the name tblBase with that of a query that returns the records to be processed. The output must be a table, though – a query is merely a way to display data from one or more tables, it doesn’t store data itself.

              The code will work in earlier versions of Access too.

    Viewing 0 reply threads
    Reply To: Create Future Records for a item

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: