Create Records Automatically

[CajunPaul]

Hi, Newbie here.
I've been building databases for many years, using Lotus Approach.
I am now required to use Microsoft Access 2003 running on Windows XP Pro, and am having some trouble. (Imagine that!)
I realize that Lotus is probably a 4-letter word on this forum, so please excuse my foul langue.

Anyway, I've set up two tables, with a 1 to many relationship.
I've built a form using these two tables. On the left, I have the fields from the "Parent" table and on the right, I have a sub-form with the fields from the "Child" table.

My quest is this:
Calculate and store depreciation on fixed assets.
After keying in information into the "Parent" fields, I would like a macro to perform the calculations and fill in the "Child" fields. This macro would have to loop until the asset is completely depreciated using a rather complex formula. I use the formula in excel with very good results. But Access can't "Parse" the formula and tells me I should check my syntax. I've checked and re-checked the syntax, and can't find anything wrong. It is the exact formula that runs perfectly in excel with the exception of using field names instead of cell references.

So, I pared down the formula to do only the first step in the calculation. Access seems to be able to handle that. But the first step in the macro is to create a new record in the "Child" table. It can't even do that! It tells me that the table is not open. How can the table not be open when I am looking at it on the form?

Please excuse my ignorance.
Let me paraphrase how I feel:
Let's pretend I want to put a swimming pool in my back yard.
Using access, I see that first, I need a hole in the ground.
But access doesn't have any hole "Objects".
So I have to dig the hole with a crane.
But access doesn't have any crane "Objects".
So I have to build a crane myself.
But access doesn't have any crane parts.
So I have to pour molten steel into hundreds of molds to make some crane parts.
Then I have to build my crane piece by piece.
Then I have to perform multiple tests to make sure my crane works.
Once I have spent many hours building and testing my crane, I can now dig my hole.
But now, access tells me that not only do I not have permission to use my crane to dig a hole, I'm also not allowed to have a hole where I want to put my swimming pool!
And all through this process, I keep thinking that if I could only use Lotus Approach to dig my pool, all I would have to do is click the "hole" button and click where I want the hole to be.

Sorry about the long winded description. I am very frustrated.
Can any one help me?

 

[mcirvine]

If i would need to program this I would do the calculation for the assets depreciation in VBA. Make my child form fields unbound. Add a button on the parent part of the form that says calculate depreciation. When button clicked a VBA program runs and does the calculation and inserts the fields in the child table and shows the related fields on the form.

I'm sure the 'hole' button for your pool is there . Can you paste your formula so we can maybe find out why the syntax is giving problems?

 

[CajunPaul]

Depreciation Formula in Access:

MAX(0, MIN(([Assets]![AcquisCost] - [Assets]![SalvageValue] - [Assets]![FinalDeprAmount] - [Assets]![ExtraDepr]),

IIF([Assets]![DeprMethod] = “SL”, ([Assets]![AcquisCost] - [Assets]![SalvageValue]) / ([Assets]![UsefulLife] * 12),

IIF([Assets]![DeprMethod] = “150% DDB”, MAX(([Assets]![AcquisCost] - [Assets]![SalvageValue] - [Assets]![FinalDeprAmount]) / ([Assets]![UsefulLife] * 12) * 1.5, ([Assets]![AcquisCost] - [Assets]![SalvageValue]) / ([Assets]![UsefulLife] * 12)),

IIF([Assets]![DeprMethod] = “200% DDB”, MAX(([Assets]![AcquisCost] - [Assets]![SalvageValue] - [Assets]![FinalDeprAmount]) / ([Assets]![UsefulLife] * 12) * 2, ([Assets]![AcquisCost] - [Assets]![SalvageValue]) / ([Assets]![UsefulLife] * 12)))))))





This formula allows the choice of Straight Line, MACRS 150%, or MACRS 200% methods.
If MACRS 150% or 200% are chosen, Straight line takes over once it is greater.

The field [Assets]![FinalDeprAmount] is a “holding bin” for calculating the running subtotal of depreciation for each child record. I thought this would be easier than using a “previous record” command in the formula.
The results of the formula need to be stored with each child record rather than having a calculated field because the value of the variables change with each iteration and I need all the results to be static for past, present, and future reporting.

When using the formula builder, everything goes ok until I get near the end of the first “If” statement. I then have to manually select parts of the formula instead of just clicking on it. By the time I get to the middle of the second “If” statement, the builder no longer accepts keyboard input. Is there a limit on the number of characters I can enter? Or is the builder just spazzing out?

 

[RV]

Paul,

a) you can't use the Max function the way you try to
b) your IIF statements do come with incorrect syntaxis

You'd be better of writing a user defined function in VBA the calculate your depreciations based on NBV and Depreciation Method.

RV

 

[CajunPaul]

I guess I'll have to do some serious studying. That formula runs perfectly in Microsoft Excel, Lotus 123, and Lotus Approach. Can't figure out what I'm doing wrong.