IS THIS FORM IDEA POSSIBLE?

[435]

Im having trouble figuring out the following

TABLE 1 I WANT TO CREATE A FORM WERE IT ASKS FOR
UserID #1 THE USER ID NUMBER. I WANT THIS TO
UserID #2 DO THE FOLLOWING: If UserID #1 modifies
UserID #3 Customers(TABLE2)and Transaction(TABLE3)
UserID #4 then record UserID #1 on those two tables.

TABLE 2 The idea is to keep
Customers track of who makes transactions and have
the record in case needed.
TABLE 3 Just like cash registers,have clerk numbers
Transaction or user numbers i want to create a form
which will ask for the UserID # and that form will then take to make the change the tables but
leaving the UserID # on the tables edited

Can anyone suggest me or point me on how to come about this
and or if this is possible.

Any comments are apreciated!

 

[pelegs]

This is not so hard. When you first create the new row, assuming you can read the UserID, just make sure you insert that value in a column in the table.

Now, if you want to keep an audit trail of who made a change, then you are in a more complicated situation. Do you want to keep track of just the last change to the row? Then that's not so bad. Just update the column with the current UserID whenever you modify a row.

If you want an audit trail of *everyone* who changed the row, then, you're at the next level of complexity. You need a separate audit trail table for each table you want to audit. This audit trail table needs to have a row added everytime a row is created or changed. The audit table should have these columns: the primary key of the table (to identify which row got changed), the UserID, and the date the change was made.

If you want to also keep track of what changes were made, then you have to add columns to hold the column name and the value that was entered or changed to the audit table.

Complete audit trails are nasty. If you really, really need a complete audit trail, it would almost be worthwhile migrating to SQL Server and use triggers. Try writing the code in VBA and you might get some sense of what I say that.

Peleg
PelegNOSPAM@PStrauss.net

 

[Veep]

1. Do you have a Network?
2. Do users login to the Network (or...are you going to base the "truth" of what ever you capture on someone using the honor system and "Entering" their UserID.

If you answer "no" to both of these questions then you're at the mercy of your users. Whatever scheme you develop will result in not being sure.

If you answer "yes" then, yes you have them (as long as you disable 'bypass' (shift) key (only if they're Access savy).

There are a bunch of code examples out there. If you can't find them I'd be happy to send some links.

 

[MichaelRed]

see faq 181-291 and you DO NOT need a seperate audit table for each table in the db!!!!!

On the other hand, any / audit (transaction log) WILL grow faster than the actual ("Live&quot date, so be sure to make arrangeemnts to 'prune' the tranasaction log regularly. If the audits are of long term importance, the "clippings' should be transfered to a seperate and equally secure facility.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[435]

Thanks!, you guys sure know this stuff.

What i want to create is somethig simple.

I made a diagram. Security is not really what
im focused on right now but i did make the login
form which pops up asking for password for users
from a drop down menu just for fun. Well the diagram
is at the following address, hopefully you guys understand
it.

http://tec-serv.1colony.com/images/grayscale_project.jpg

On the diagram what i Need to figure out is
code or something I have no idea what in order to
put the user# in the (customer) form.
I mean the user that logs in. Therefore
leaving the user# on all the customers
updates.

The db will not be on a network at the
moment but do plan on doing that
later whe i get more experience with
access.


Pelegs, about the question i wan to just track
of the last change to the row.

About Uptdating the column with the current
UserID whenever a row is modified
how do i do that.?

 

[pelegs]

"About Uptdating the column with the current
UserID whenever a row is modified
how do i do that.?"

If you are using a bound form, this is not very difficult. Say the column is called "UserID" and that the current UserID is in a variable strUserID. (How it gets into that variable is a whole 'nuther story.) Then, when you update the row, perhaps in the BeforeUpdate event, do something like this:

[tt]
dim rs as dao.recordset

set rs = me.recordsetclone ' make a copy of the records
' behind the form
set rs.bookmark = me.bookmark ' go to the copy of the
' current row in the clone
rs.edit
rs.UserID = strUserID ' update it
rs.update

set rs = nothing ' close clone

[/tt]

I'm not sure which event handler is the one you really want, so if this doesn't work, try some other likely ones. I never get them right on the first try. The exact sequence that the events are triggered is something that I never seem to be able to remember or understand very well.



Peleg
PelegNOSPAM@PStrauss.net