Hi,
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 :
basAuditTrail: - Option Compare Database
-
-
Const cDQ As String = """"
-
Sub AuditTrail(frm As Form, recordid As Control)
-
'Track changes to data.
-
'recordid identifies the pk field's corresponding
-
'control in frm, in order to id record.
-
Dim ctl As Control
-
Dim varBefore As Variant
-
Dim varAfter As Variant
-
Dim strControlName As String
-
Dim strSQL As String
-
On Error GoTo ErrHandler
-
'Get changed values.
-
For Each ctl In frm.Controls
-
With ctl
-
'Avoid labels and other controls with Value property.
-
If .ControlType = acTextBox Then
-
If .Value <> .OldValue Then
-
varBefore = .OldValue
-
varAfter = .Value
-
strControlName = .Name
-
'Build INSERT INTO statement.
-
strSQL = "INSERT INTO " _
-
& "Audit (EditDate, User, RecordID, SourceTable, " _
-
& " SourceField, BeforeValue, AfterValue) " _
-
& "VALUES (Now()," _
-
& cDQ & Environ("username") & cDQ & ", " _
-
& cDQ & recordid.Value & cDQ & ", " _
-
& cDQ & frm.RecordSource & cDQ & ", " _
-
& cDQ & .Name & cDQ & ", " _
-
& cDQ & varBefore & cDQ & ", " _
-
& cDQ & varAfter & cDQ & ")"
-
'View evaluated statement in Immediate window.
-
Debug.Print strSQL
-
DoCmd.SetWarnings False
-
DoCmd****nSQL strSQL
-
DoCmd.SetWarnings True
-
End If
-
End If
-
End With
-
Next
-
Set ctl = Nothing
-
Exit Sub
-
-
ErrHandler:
-
MsgBox Err.Description & vbNewLine _
-
& Err.Number, vbOKOnly, "Error"
-
End Sub
I call the above mentioned procedure in a single form with :
in event procedure : Before Update: - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Call AuditTrail(Me, [Shipper ID])
-
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!
Thanks
3 4170 ChipR 1,287
Recognized Expert Top Contributor
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?
NeoPa 32,564
Recognized Expert Moderator MVP
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.
Hi,
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!
Thanks
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: LC |
last post by:
hi,
i worry about people doing something they shouldn't to my db and I
would like to track any structural changes (who and which)to my db. I
am using oracle 8.0.6.0.0 and 9.2.0.2.0.
regards,
Lee Cho
|
by: Ezekiël |
last post by:
hello,
I was wondering how to track all changes on tables by using some sort of a
history table.
What i would like is a generic history table where i can see who updated,
inserted, deleted or executed(stored procedures, triggers) what value in
what table with a date when it was occured.
Could somebody help me with this?
|
by: JLuppens |
last post by:
Does anyone know if there
is a way to track changes in a text field like word does? I am using
a SQL2000 database and can use either VB.Net or Access. The field is
either ntext or Varchar. I would like to show users the changes
other users have made to a text field using a other font or font
color.
Thanks
|
by: Colleyville Alan |
last post by:
I have some data in a table structured like this:
Date Cust_ID CUSIP Amount
01/31/2005 060208 02507M303 27,061.84
02/28/2005 060208 02507M303 32,049.00
Is there a way to construct a query that will give me the difference by
customer...
|
by: Tim D |
last post by:
Hi,
I originally posted this as a reply to a rather old thread in
dotnet.framework.general and didn't get any response. I thought it might be
more relevant here; anyone got any ideas? My questions are below...
"David Good" wrote:
> We have a network running both Win2k and Win2k3 webservers and our web sites
> reside on a UNC network...
| |
by: |
last post by:
Is anyone aware of an elegant method to 'track' changes (additions,
deletions, edits) to an online server on my web host...in this case various
sub folders in the /IMAGES/ directory...
The reason I ask is that I need to be able to alert internal staff to to new
images added to various boat models on our site without reverting to sending
out...
|
by: Daniel Walzenbach |
last post by:
Hi,
I need to track all changes made to an object. Consider the following class:
Public Class Dog
|
by: metaperl |
last post by:
I'm actually taking Microsoft's 2779 and just finished a lab where we
kept track of our changes to the database.
However, I'm not happy with the scripts interface because it does not
tell me the chronological order of my changes to the database.
Could someone share with me their technique for keeping track of
database changes?
I'm...
|
by: PJ6 |
last post by:
What's the best way to, in the general case, track changes to an object's
public fields and properties? In a lot of applicatoins I've seen bahavior
consistant with simple periodic checking from a timer, or checking otherwise
initiated from a user event. I don't believe I can make that approach work
in the project I'm working on... are there...
|
by: John Sheppard |
last post by:
Hello there,
Does anyone know of a way to track changes to an SQL Server database so that
I can easily run those changes at a later date?
That is, I want to make schema changes, and record those changes so that I
can execute them 6 months later on a copy of the orignal database.
Thank you kindly for any ideas anyone may have
John
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |