custom security in access/vb6...

[mrwendell]

Here is what i need some help with...

i have timesheet application in access...i cannot totally make everyones permission readonly...so

what i need to do is when there is a change made to a record i need that time and possibly user attached to that record.

tblRDM frmEmplRDM.... supv make changes to a record...i snatch the records an update other sys... 2 days later supv makes another change to the record... i want to know when and who made that change by timestamping another field.

to make it a little more difficult...

currently when a supv approves a record...they check a box...and another field(rdate)is timestamped(now). also...when i complete my process...a checkbox field (udate)is also timestamped (now)... supv can make changes after the fact using one form (frmemplrdm) and there is only one table (tblrdm)that holds all records but there is no audit trail for subsequent adjustments.

so my dilema is that when&who timestamp i need...should be ran after the above two processes.

afterupdate event= if checkbox1 is true and checkbox2 is true then when&who = (now)

also...is there a post out here that can show me how to setup who is currently loginto the db...

 

[BobStubbs]

First, here is my method to find out who is currently logged into a database. This assumes:

1. You are using the Access Switchboard form, or have your own form set up as a Switchboard (i.e. this form opens when the database opens, and is always open until the database closes)

2. Users have individual user names to log into the database.

First, set up a Control Table - mine is called tblControl1 with these fields:

RecordID - AutoNumber
RecordType - Byte
ItemName - Text, 20 chars
ItemValue - Text, 30 chars

Now, add this code to the Form_Activate event of the Switchboard form:

Private Sub Form_Activate()
    
Dim intCounter As Integer
Dim strSQL As String

'--------------------------------------------------
'- Record User Name in Control Table              -
'--------------------------------------------------
intCounter = DCount("[ItemName]", "tblControl1", "[ItemName]='" & CurrentUser & "'")

If intCounter = 0 Then
    strSQL = "INSERT INTO tblControl1 (RecordType, ItemName, ItemValue) "
    strSQL = strSQL & "VALUES (1, '" & CurrentUser & "', '" & Format$(Now, "dd/mm/yyyy @ hh:nn") & "')"
Else
    strSQL = "UPDATE tblControl1 SET tblControl1.ItemValue = '" & Format$(Now, "dd/mm/yyyy @ hh:nn") & "' "
    strSQL = strSQL & "WHERE tblControl1.[ItemName] = '" & CurrentUser & "'"
End If

DoCmd.SetWarnings (False)
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings (True)
   
'--------------------------------------------------
'- Make sure the Switchboard form is maximised    -
'--------------------------------------------------
DoCmd.Maximize
    
End Sub

I place this in the Activate event, so the date and time information updates every time the user visits the Switchboard. I find this useful for identifying people who leave a database open for hours without using it ...

This code goes in the Form_Close event of the Switchboard form:

Private Sub Form_Close()
    
Dim intCounter As Integer
Dim strSQL As String
    
'--------------------------------------------------
'- Record user logout                             -
'--------------------------------------------------
intCounter = DCount("[ItemName]", "tblControl1", "[ItemName]='" & CurrentUser & "'")

If intCounter > 0 Then
    strSQL = "DELETE * FROM tblControl1 "
    strSQL = strSQL & "WHERE (tblControl1.ItemName)='" & CurrentUser & "'"
End If

If strSQL > "" Then
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings (True)
End If

End Sub

Now, you can see who is logged into your database, and also whether they have used it recently, by writing a query or report which lists all the tblControl1 records which have '1' in the RecordType field.

The RecordType is there, since I use the Control Table to hold other information as well. You don't need it if you just want to record logged in users.
__________________________________________

My method to 'date stamp' record changes, uses two fields in the table, which I display as 'protected fields' on the form so the user can see them.
These are:

LastUpdate - date, short date format
LastUpdatedBy - text, 20 chars

Set these field properties on the [Data] tab:

Locked = Yes
Enabled = No

Now add this code to the BeforeUpdate event of the form:

'------------------------------------------
'- Set the 'last update' fields,          -
'------------------------------------------
    Me.LastUpdate = Date
    Me.LastUpdatedBy = CurrentUser
    DoEvents

________________________________________________

To create a full Audit Trail of changes to records, you need an Audit Trail table:

RecordID - AutoNumber
TableName - Text, 30 chars
RecordKey - Text, 20 chars
ChangeDate - Date, short date format
ChangedBy - Text, 20 chars

Add this code to the BeforeUpdate event of the form:

dim strSQL as String

'----------------------------------------------------
'- Write the audit record                           -
'----------------------------------------------------
    strSQL = "INSERT INTO tblAuditTrail (TableName, RecordKey, ChangeDate, ChangedBy) "
    strSQL = strSQL & "VALUES ('tblYourTable', '" & RecordKey
    strSQL = strSQL & "', '" & strLogDate & "', '" & CurrentUser & "')"
        
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings (True)

You can now record changes to any records in the Audit Table, as required.

Bob Stubbs