468,321 Members | 1,953 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,321 developers. It's quick & easy.

Track Changes in Form with sub-form & multiple tabs


I've made a multi user (4 end users) database in Access 2000. I've made data tables, reference tables etc which sits on a shared drive and I've made individual front ends for the end users which gives them specific views of the data.
I'd like to track all the changes made to a record which works find with the following code :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. Const cDQ As String = """"
  4. Sub AuditTrail(frm As Form, recordid As Control)
  5.   'Track changes to data.
  6.   'recordid identifies the pk field's corresponding
  7.   'control in frm, in order to id record.
  8.   Dim ctl As Control
  9.   Dim varBefore As Variant
  10.   Dim varAfter As Variant
  11.   Dim strControlName As String
  12.   Dim strSQL As String
  13.   On Error GoTo ErrHandler
  14.   'Get changed values.
  15.   For Each ctl In frm.Controls
  16.     With ctl
  17.     'Avoid labels and other controls with Value property.
  18.     If .ControlType = acTextBox Then
  19.       If .Value <> .OldValue Then
  20.         varBefore = .OldValue
  21.         varAfter = .Value
  22.         strControlName = .Name
  23.         'Build INSERT INTO statement.
  24.         strSQL = "INSERT INTO " _
  25.            & "Audit (EditDate, User, RecordID, SourceTable, " _
  26.            & " SourceField, BeforeValue, AfterValue) " _
  27.            & "VALUES (Now()," _
  28.            & cDQ & Environ("username") & cDQ & ", " _
  29.            & cDQ & recordid.Value & cDQ & ", " _
  30.            & cDQ & frm.RecordSource & cDQ & ", " _
  31.            & cDQ & .Name & cDQ & ", " _
  32.            & cDQ & varBefore & cDQ & ", " _
  33.            & cDQ & varAfter & cDQ & ")"
  34.         'View evaluated statement in Immediate window.
  35.         Debug.Print strSQL
  36.         DoCmd.SetWarnings False
  37.         DoCmd****nSQL strSQL
  38.         DoCmd.SetWarnings True
  39.       End If
  40.     End If
  41.     End With
  42.   Next
  43.   Set ctl = Nothing
  44.   Exit Sub
  46. ErrHandler:
  47.   MsgBox Err.Description & vbNewLine _
  48.    & Err.Number, vbOKOnly, "Error"
  49. End Sub
I call the above mentioned procedure in a single form with :
in event procedure : Before Update:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Call AuditTrail(Me, [Shipper ID])
  3. End Sub
Now here's the problem, the above mentioned code seems to work OK when using a single form but as soon as I add in a SubForm (in which ideally I want to track changes made too) everything comes to a halt with the mention of :
"The expression Before Update you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro."

Any help on this would be GREATLY appreciated!

Nov 12 '09 #1
3 3842
1,287 Expert 1GB
Not enough information here, especially related to the subform. If you copied this BeforeUpdate code to the subform, it probably can't find a sub called AuditTrail because it's in another module. Side note, why is recordid a Control rather than a String?
Nov 12 '09 #2
32,081 Expert Mod 16PB
I suspect this problem is related to something you've done (in the form setup somewhere) that you haven't actually told us about. Easy enough to do when you don't know what the problem is of course, but look at what's set up to link the code to the form.

As a separate point (unrelated to your problem), I would bring your attention to your use of cDQ within your SQL code. This should work, but doesn't produce very portable code (and it's actually not quite correct - even though Access itself still defaults to this way of quoting. See Quotes (') and Double-Quotes (") - Where and When to use them for a fuller explanation of the issues.
Nov 15 '09 #3
I've attached a watered-down version of the database (3 tables, 5 forms and 2 modules), as you open the database and try to update a field (in the OE_Small form) and click in the sub-form the error msg appears....
I've tried a work-a-round by using 2 modules (so that the main form and subform can call on a module each) but it didn't work either!
I'm pretty much at my wits end! Any help would be appreciated!
Attached Files
File Type: zip Audit_Errors.zip (164.8 KB, 142 views)
Nov 16 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by EzekiŽl | last post: by
5 posts views Thread by Daniel Walzenbach | last post: by
20 posts views Thread by John Sheppard | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.