Create Future Records for a item

Topic

New Reply

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
Cost

Reply | Quote

Replies

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?

Reply | Quote

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?

Reply | Quote

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.AddNew
rstOut!ID = rstIn!ID
rstOut![Year Acquired] = rstIn![Year Acquired]
rstOut!Year = lngYear
rstOut![Asset Description] = rstIn![Asset Description]
rstOut!Cost = rstIn!Cost
rstOut.Update
Next lngYear
rstIn.MoveNext
Loop

ExitHandler:
On Error Resume Next
rstOut.Close
Set rstOut = Nothing
rstIn.Close
Set rstIn = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
[/codebox]
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.

Reply | Quote

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?

David

Reply | Quote

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.

Reply | Quote