Use form code on records created with append query

[ringer]

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.

[MMcCarthy]

I'm sorry I don't really understand what you mean by code running on records created by the append query.

When do you run the append query?
How is the form then opened?
What code are you trying to apply?
When do you want to trigger this?

Remember we can't see your database and it can be very difficult to follow someone elses logic.

[ringer]

When do you run the append query?

My thought was that it should run when the db opens. That way the changes to existing records, addition of new records, and changes to the new records are all done before I can launch any reports or forms.

How is the form then opened?

The form where I manually create the records is in the Forms section of the db, but it wouldn't need to be opened if there were no records needing to be manually created. Automating record creation should mean I only manually create 25% of the records instead of 100%.

What code are you trying to apply?

The code that runs behind the form when I manually create a record does several things as I'm creating the record, depending on what control I'm editing at that time. In some places it takes dates that were input and generates dates for other fields. In other places it does math based on numbers and amounts and dates in other controls, and then writes the results to the appropriate fields. The code actually automates a lot of the tasks that need to be done on data that is input into the form.

When do you want to trigger this?

The code that finishes setting up the automatically appended records should probably run right after the append query runs when opening the db.

[ringer]

Any other thoughts? Thanks

[NeoPa]

Ringer,

I'm going to have a look at this for you, but could I ask that you do something for me.
As Mary explained, it's very difficult to follow someone's explanation when it gets complicated (as yours is) unless it's explained in a very organised manner. I appreciate that you've already done a lot of work answering Mary's questions and that's a great attitude. However, I'm still finding it hard to sift out the information. I'm sure it's all there, but I actually struggled to put it all together into something that made sense.

What I'd like you to do (if you don't mind) is to redo it from scratch (in this thread). Bearing in mind the point Mary made about how much we know before you tell us (zilch) and how confusing it can be when talking about a database to refer to "the records..."
Take us through what you want it to do and how you currently envisage this taking place.
Try always to remember to give a token to a set of records that you can refer to later - as well as the tables. I think you're copying records from one table to another. Neither table is named yet and that helps people to understand things (names).

If you can manage to do this for us I'm sure we can spend a little more time on working out the answer. The hard part is generally understanding the question (you're certainly not unique in that respect). I suspect this is a question with a perfectly sensible answer. Let's see if we can find it.

[Denburt]

If I may step in here (I know a little late) I think what you are looking to do ringer is run your append query then run some VBA code to manipulate the data that was added, or add more records depending on what was initially entered with the append query. Am I correct?

I think you answered Mary's question quite well but some pieces of this puzzle are still missing and that is what we actually need more info on.

[MMcCarthy]

OK ringer

I'm going to post what I think is going on and you can tell me if I'm right or wrong.

You have a table that has records added to it in two different ways.

1. By use of a data entry form
2. by an append query

When you use the data entry form to add these records you use code to calculate certain values

You are looking for a way to duplicate this process when using the append query.

If I am right then there are two ways this can be done. You cannot force the records through the process of the form code which is what I think your question was asking. Your two options are:

• to change the append query to one written in code using recordsets.
• to try to build the actions into the append query.

We can help with this depending on which route you want to go down.

Due to other commitments I'm not always around but Denburt and NeoPa can also help.

However, if what I have outlined above is not correct then please clarify.

Mary