Append Query Triggered by New Record 1

[mekohler]

Hi, I'm working on a database for development permits. The main development permit table is linked to a table from our financial database which provides ownership and zoning information. We would like to maintain historical data for a development permit, who was the owner when the permit was issued etc. The data from the financial database is refreshed every night, it is brought into SQL from a proprietary database using a SSIS module. Is it possible on a form to trigger a query when a new record is created? That way when a new development permit is created, the corresponding record in the financial table is copied into a historical table. Is this the correct approach to this problem or should I be looking at other solutions?
Thank you,
Michael Kohler

 

[1DMF]

A form can be bound to a table and there are 'before' and 'after' insert events.

Is this what you mean?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[mekohler]

Yes, I would like the historical table updated after the form is updated.

 

[1DMF]

So simply add your append code on the 'After Insert' event of the form.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[lameid]

One may also argue the right solution is to track the owners of the property over time and instead have a query pull the owner when the permit was issued and not bother storing the owner with the permit data. This line of thinking comes from data normalization. On the other hand, there are pragmatic reasons not to do this like performance and that is a careful balancing act. Storing ownership separate does allow correcting the data in one place. If there is a backlog of ownership information being entered timely, your solution would maybe pull the wrong information if a new owner retrieves a permit near the time of purchase. Now the data has to be updated in two places if it is to be correct.