Automatic Revision # Update

[puforee]

I am developing a DB for our company. The reports generated will keep a revision # and date at the bottom of the report. I would like the revision # to be automatically updated anytime the data on the report has changed. The data for the report is stored in two tables..one called Major and one called Minor. The data is entered from a form (Major) with a supform (Minor)...fairly basic.

Has anyone heard of a process to control the Revision number automatically, only if something changes on the Major or Minor Table/Form? I was thinking about storing the Revision number in the Major table/Form

Thanks,

 

[cdck]

You could create Before Update or After Update events for the specific fields which should trigger a revision bump to update the revision. You'd need to store the revision in data somewhere, as well. The table you'd want to use would depend on how far down the data changes need to impact revision - if a change in the minor data is not supposed to change the revision, then it should go into the major table.

If multiple fields can impact the revision, you may just want the events to:
[tt]If the checkbox is not checked then
--bump the revision in the data
--Make sure the checkbox is checked
End if
[/tt]which would make it so that the second, third, etc. fields which are changed would do nothing.

Just make sure you have coding in place to uncheck the box if you move to a new record. If you are not committing data changes until the user performs a specific action, you can skip the entire checkbox and associated routine and just add the revision bump to that action's event.

On the reports, you may have to set the footer output to use one of your grouping footers instead of the page footer, or set up a query to pull the latest revision for each item and use a DLookup to connect to the correct one.

I hope this is helpful.

Cheryl dc Kern