Aplying the code to my DB 2

[malbreht]

Hi,

I need my DB to store history data and have already found what it seems to me as a perfect solution at

http://allenbrowne.com/AppAudit.html.

The code itself is at

http://allenbrowne.com/AppAuditCode.html.

I've followed the instructions carefully, but since I'm not a programmer, I got stuck.

My question is: when pasting the code to a new module, what do I need to change? For instance, here's a piece of the code:

Function AuditDelBegin([b]sTable[/b] As String, [b]sAudTmpTable[/b] As String, [b]sKeyField[/b] As String, [b]lngKeyValue[/b] As Long) As Boolean
'On Error GoTo Err_AuditDelBegin
    'Purpose:    Write a copy of the record to a tmp audit table.
    '            Copy to be written to real audit table in AfterDelConfirm.
    'Arguments:  sTable = name of table to be audited.
    '            sAudTmpTable = the name of the temp audit table.
    '            sKeyField = name of AutoNumber field in table.
    '            lngKeyValue = number in the AutoNumber field.
    'Return:     True if successful.
    'Usage:      Call from a form's Delete event. Example:
    '                Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
    'Note:       Must also call AuditDelEnd in the form's AfterDelConfirm event.
    Dim db As DAO.Database           ' Current database
    Dim sSQL As String               ' Append query.

    ' Append record to the temp audit table.
    Set db = DBEngine(0)(0)
    sSQL = "INSERT INTO " & [b]sAudTmpTable[/b] & " ( audType, audDate, audUser ) " & _
        "SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & [b]sTable[/b] & ".* " & _
        "FROM " & [b]sTable[/b] & " WHERE (" & [b]sTable[/b] & "." & [b]sKeyField[/b] & " = " & [b]lngKeyValue[/b] & ");"
    db.Execute sSQL, dbFailOnError

Exit_AuditDelBegin:
    Set db = Nothing
    Exit Function

Err_AuditDelBegin:
    Call LogError(Err.Number, Err.Description, conMod & ".AuditDelBegin()", , False)
    Resume Exit_AuditDelBegin
End Function

Is it OK to adapt all the bolded names to my DB? Because I've done that - but everything went wrong: the form (for which I'm doing this) doesn't show previous entries no more and also it is not possible to enter anything new...

If I change the lngKeyValue in the first line accordingly with the instructions into Me.Sell_ID, the whole first line goes red and a message appears: Compile error, Expected: identifier. If I don't change it, the whole line stays black, but by decompiling I get the message: Compile error: Invalid use of Me keyword...

Please, I really need help on this one...

As always, thanks for any advice!

 

[Remou]

You do not change anything. This
'Arguments: sTable = name of table to be audited.
' sAudTmpTable = the name of the temp audit table.
' sKeyField = name of AutoNumber field in table.
' lngKeyValue = number in the AutoNumber field.
Explains what to do.

You call the function like so, from the proper form:
AuditDelBegin("TableName", "AuditTableName", "Sell_ID",Me.Sell_ID)
Change TableName and AuditTableName to real names in you database.

 

[missinglinq]

To expand on Remou, you never change anything when copying a function(s) like this into a module! And if not given instructions on the naming of the module, always make up a name, never use the same name as the function, it confuses the Access Gods! I may get my fingers rapped for this, but if you continue to have more problems with this you might try posting it on

http://www.accessmonster.com/.

This is a forum where Allen Browne frequents daily and he'll probably answer you.



The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[malbreht]

As always - you guys are the best...thank you very much!!!

 

[malbreht]

Hi, finally managed to correct my mistake, so I pasted the original code to the module, however, the problem stays the same: the form doesn't display previous entries neither is it possible to enter new records. At one combo it is impossible to select any of the records, in another it is possible, but I'm getting the error message "Field cannot be updated" twice.

The form's code is as suggested (it does contain however two extra double
click procedures and one procedure, that starts the form with a new record):

Dim bWasNewRecord As Boolean

Private Sub Form_AfterDelConfirm(Status As Integer)

Call AuditDelEnd("audTmpSell", "audSell", Status)

End Sub

Private Sub Form_AfterUpdate()

    Call AuditEditEnd("tblSell", "audTmpSell", "audSell", "Sell_ID", Nz(Me!Sell_ID, 0), bWasNewRecord)

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

   bWasNewRecord = Me.NewRecord
   Call AuditEditBegin("tblSell", "audTmpSell", "Sell_ID", Nz(Me.Sell_ID, 0), bWasNewRecord)

End Sub

Private Sub Form_Open(Cancel As Integer)

    DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub Employee_ID_DblClick(Cancel As Integer)

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEmployee"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Private Sub Product_ID_DblClick(Cancel As Integer)

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmProduct"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Private Sub Form_Delete(Cancel As Integer)

Call AuditDelBegin("tblSell", "audTmpSell", "Sell_ID", Nz(Me.Sell_ID, 0))

End Sub

Again, many thanks for any advice...

 

[Remou]

I think these problems are due to something else. How are you opening the form? Have you checked that Allow Edits is set to yes? What is the Record Source for the form? It may be best to post the other bits of code, just in case.

 

[malbreht]

The funny thing is, that before experimenting with this auditing data, everything worked just fine. The form then opened by selecting it from a custom switchboard. Data editing was enabled, as well as entering new records. The form opened at the new record (DoCmd.GoToRecord , , acNewRec), both combo boxes worked perfectly, ...

The record source is a SQL statement that selects a number of fields from 4 different tables: tblSell, tblSellProduct, tblEmployee, tblProduct.

Because of all my experimenting with apllying the Audit Log, I'll try from a previous version of DB to apply it once again. Hope it works...

Thanks,
Mare

 

[malbreht]

Ha, it actually works now...I just did the same thing all over again, without my previous try outs...

Here's another question: Now I've got it working for 1 table & 1 form. How do I adapt the code if I want to save the log for 2 tables?

One of the steps goes like this:
Locate the form's BeforeUpdate property. Choose [Event Procedure]. Click the Build button. Enter these two lines:

   bWasNewRecord = Me.NewRecord
   Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID, 0), bWasNewRecord)

Now, I've got it like this:

   bWasNewRecord = Me.NewRecord
   Call AuditEditBegin("tblSell", "audTmpSell", "Sell_ID", Nz(Me.Sell_ID, 0), bWasNewRecord)

And it works

I would like it also for another table (but on the same form), tblSellProduct (Sell_ID, Product_ID, Quantity).

Where in the Call function do I call the second table and its ID?

Many, many thanks!

 

[Remou]

You don't. Start again from the beginning, that is, create the two tables and add a line to each event, so you end up with two Call lines.

 

[malbreht]

Well, I've already created two more tables (not a problem), but events are in the form properties... So this is what I'm not sure about: if the form already contains for instance before update procedure - how do I add another one?

Oh, and just 1 more question, then I'll stop, I promise

The limitations of these solution are among others "each table to be audited must have an AutoNumber primary key". In my case, the second table tblSellProduct has 2 keys, Sell_ID (autonumber) and Product_ID (text). Does anybody think it would be a good solution to simply add another autonumber field as a key and remove key from previous two fields in that table?

Thanks a lot!
Mare

 

[Remou]

Not another before event, another call line.

Event
Call Audit...
Call Audit...

Adding another ID depends on a number of things, it may suit depending on how far along you have got with development.

Have a look at the auditing idea offered by MichaelRed in the Access FAQs before you make up your mind.

 

[malbreht]

Will do, thanks a lot again!