update records in one table when new record added to main table

[murray83]

rather simple one i think

so i have a main table which has all the records but thoose that be want to have a little visulation on when it was updated last and by whom, no names just windows logins useing the

Me.txtUser.Value = Environ("username")

so, when the record count has increased i would like the other table called ImportedBy which just has 2 headings ImportedBy which will hold the username and DtOfImport which will as says dispaly the date of the import/update

thing is im not sure how i would run an update query in this situation, i think it would have something to do with record counting, maybe something like this ( this is just of top of my head )

 current_rec_count = main.reccount if current_rec_count > then DO SQL Update to ImportedBy

all and any help welcome, cheers

 

[theDBguy]

Hi. Could you just set the DefaultValue properties for those fields instead?

 

[The_Doc_Man]

OK, a couple of questions seem appropriate here.

Are all of the tables part of an Access database, perhaps in a back-end file?

If so, then here is the general rule: To remember when somebody does something, you make the code of the FE tell you when somebody does something. I.e. the best place to remember that something WAS done is to write the record WHEN it was done.

So if an update or insertion occurs to one of those Access-managed records you want to track, the person making the change should not only update the field that normally gets updated (that is the focus of this question) but should also update some other fields that tell you what you want to know.

This leads to a couple of comments at once.

1. What you are describing is a form of AUDIT management. So, do some searching in this forum for the topic of "AUDIT." When I did this, I kept a separate audit table from the tables in question. That way I didn't clutter up the main tables; I just had another table that told me who did what to whom on Friday night.

2. This implies, however, that the user does this through forms - because if your users can directly interact with your table, what you want isn't going to be easy - and for older versions of Access, perhaps not even possible. Look up the topic of "SECURING A DATABASE" in this forum to see loads of discussion on how to control and monitor what your users are doing to your data.

3. Your question about record counts again is an after-the-fact type of monitoring, and yes this is surely possible, but again ignores the idea that if this is entirely an Access DB, then the person making the change is ALSO running Access - and if the user is doing it through a form, the FORM can do this WHEN IT HAPPENS. You don't have to wait to detect it after the fact if you can update it simultaneously with the rest of the action.

Does this help you understand my viewpoint better? This is the old "strike while the iron is hot" viewpoint. Why put off until later what you can do right now while it is fresh?

 

[murray83]

yeah the person at the moment updates the table by accessing the table which isn't visible unless a password is inputted then the table is displayed.

and no the tables arent in a back end ( it isn't split )

and yes my ImportedBy table is separate like you suggested with your Audit example so would be best at looking at using a form to update the table/tables in your opinion

 

[The_Doc_Man]

If this is a shared database but isn't split, you are courting disaster, Murray83. If there are non-zero odds of simultaneous usage of this unsplit DB, you are truly playing with fire. Just one case of simultaneous access to that table and you will have the worst case of destructive interference you could ever imagine. Corruption and data loss are lurking around every corner for unsplit shared databases.

DEFINITELY in such circumstances, you NEVER EVER IN A GAZILLION YEARS let a user directly interact with a table. Always use forms because the event routines in those forms will be your workhorses, guard dogs, and security blanket rolled up into a small bundle.

I know it is a pain in the toches to get it properly shared and every DB is different, but let me just say that it is vanishingly rare to see a long-term viable non-split shared database. The ONLY time it might work is if it is shared in series rather than in parallel because it is only on a single computer that only one person at a time could use. Even in that case, using forms to control what is done would still be the right way to go.

 

[murray83]

DEFINITELY in such circumstances, you NEVER EVER IN A GAZILLION YEARS let a user directly interact with a table. Always use forms because the event routines in those forms will be your workhorses, guard dogs, and security blanket rolled up into a small bundle.

so a subform would be the way to do that yeah paste in the details to subform press a dohickie and then updates main table, updates sereate audit/importedby table and evreyone is happy ?

and ps i have just split it

 

[murray83]

and done cheers for the pep talk guys much appreciated and not spent time trying to count dam records he-he

 

[theDBguy]

and done cheers for the pep talk guys much appreciated and not spent time trying to count dam records he-he

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[The_Doc_Man]

paste in the details to subform press a dohickie and then updates main table, updates sereate audit/importedby table and evreyone is happy ?

I might have said that in a slightly different way, but ... yeah, that's the idea! Long-term, this kind of thing is always the way to go.

It is something I learned a long time ago. Here this hunk of hardware sits to do your bidding if only you would tell it what to do. And the "what to do" is only limited by your imagination and whatever you can fit in 2 GB of .ACCDB file.

You need to record when somebody does something? Don't ask them to do it. Do it for them behind the scenes when they are not even looking because a form can sneak in a LOT of work behind a simple event routine.

That machine is there to serve your needs and fulfill your requirements. Teach it to do what you want done and then as the phrase goes, "set it and forget it." It's a mind-set that will take you far in your boss's estimation because with that mind-set, you can become incredibly efficient. OK, takes work to set it up the first time. But if you have the setup time, it never takes a lot of work again. And that is more time for you to do what you want/need to do.