Run code on records appended automatically with query

[Guest]

I need to run an append query when I open my db that will look at a date in
the records and copy the records when date() >= [PeriodEndDate] and some
other criteria.
I have the append query working perfectly. But the records still need to
have some code run on them to set up fields in the new records that should
not be copied from the old records. It is mostly math and date manipulation
using values in some fields to compute with and write the results to other
fields. When I set the records up manually all the code runs behind the form
to set up these other fields.
What I'm trying to do here is automate the creation of some of the records.
How do I get the code to run on the automatically appended records to get
them set up like the ones I set up manually?
Thank you.

 

[Larry Daugherty]

We aren't the Relational cops.but many of us here have taken pledges
not to help others shoot themselves in the foot. To that end it is
necessary that you are planning to break a couple of very important
rules. Please visit

www.mvps.org/access

and read and heed the dozen or so relational rules. They're for real.
(that site is also a very valuable resource for just about all Access
developers).

It is almost *never* necessary to store data redundantly (copy from
one table to another table) in a relational database. It is very
likely that you believe yours is a case that belies the rule. Without
having seen your data nor heard your rationale I already believe it is
not.

It is also almost *never* necessary to store the results of
calculations on data in a relational database within that same
database. The rest of this paragraph should read like the one just
above.

In the first case, data stored redundantly *will* get out of sync.

In the 2nd case, the calculation can be performed any time it is
necessary to view or to print the result. The stored result *will*
get out of sync with current data. Sometimes resource conservation
such as disk space and speeds of disk access versus processor speed
are cited to lend additional weight to the argument. As technology
evolves those mechanical issues become less relevant. The
synchronizing issue doesn't go away nor diminish in the least.

If you'll post back with an explanation of what you're trying to
achieve in real-world terms then we may be able to help you. Please
include the details of your tables and forms; an reports if you've
gotten that far along. As for the direction you're already going, if
you hear a really loud bang, look down and count your toes.

HTH

 

[Guest]

Larry, thanks for your reply and for the advice, but I don't believe I'm
storing any redundant data. I'm only writing time markers to use for
computations and current balances for use on reports, AFAICS. Maybe I'm
missing something you're seeing. Could you tell me what gives the impression
I'm storing anything redundant?

 

[Larry Daugherty]

What you wrote in your initial post and in your last post tells me
that it's very likely (almost certain) that you are storing redundant
data.

The records about which you have time concerns should have time
markers in the records themselves. Then when you want to create a
time bounded report you simply enter values for the time bounds for
the current report in the query for the report - there are various
ways of doing that at various levels of sophistication.

Consider that if the initial records contain time information then you
don't need to figure out each and every report that you will ever want
well in advance of its need. You can create reports that show what
has happened between any two dates since you started keeping records.

Please do the things I've asked of you in my earlier post. If you
want help then give us the information we need to help you.

A couple of great newsgroups to start to lurk:

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

The idea of these Access newsgroups is that developers of any level
can seek help with an issue and others who may have solutions can post
specific solutions or make suggestions and give advice. Sharing the
questions and answers is at the heart of the motivation for the
groups. In order to address issues it is sometimes necessary (and
always a good idea) to provide information about what you are trying
to accomplish in the real world and give some details about your
tables and fields. With that kind of information you can often get
some very specific solutions for your issue. By sharing the initial
issue and the solutions and results many people benefit. Hundreds of
people read the posts and learn from them. Everyone here is a
volunteer. While it's possible to get free help the more important
benefit is the knowledge that is passed along for a lot of budding (or
even advanced) developers.

HTH

 

[Guest]

Thanks for your reply. I went back and reread your first reply and picked up
on something that I failed to notice and address in my subsequent post, so I
will now: I am not copying any records from one table to another, I am only
copying records (that represent savings accounts) whose expiration date has
passed, then with an update query am changing the start and end dates to a
make new saving period. I want to keep the old one and start a new one.
Along the life of the current one I make (but do not store) "deposits" which
raise the "balance" of the savings account (which I do store). It is these
deposits and the raising of the balances, as well as changes to the statuses
of the "accounts" (which is determined by their start and end dates) that I
need to use code to automate and run when I enter the db.
So as far as I can tell, I am not storing any redundant data. I do not wish
an argument, and if you still don't believe me that is your choice, but if
that is the case, would you mind telling me exactly what data you're seeing
as redundant so I can either know if you're right or explain to you why that
is not correct.

 

[Larry Daugherty]

I can't see your data! My conclusions were based on your own
statements in your posts.

Your last clarification doesn't clarify much from your earlier posts
but did inform us of the nature of what you are doing. It would save
time and effort and enable those of us who would do so to help if you
would describe what you are trying to achieve in real-world terms
rather than what you're trying to get Access to do. Also, please list
your tables and their fields and data types.

Since you seem to be dealing with financial accounts it would seem
that you'd want to save deposit and withdrawal records forever. Since
those events will always have dates as well as amounts you can always
calculate account balances from a starting balance plus the sum of all
deposits minus the sum of all withdrawals.

HTH

 

[Guest]

Thanks anyway.

/punt