Audit Trail

  • Thread starter Thread starter jacob
  • Start date Start date
J

jacob

Does anyone have any ideas how to log changes in an Access 2k DB? I found
one on Allen Browns site that does not seem to be working.
 
First off, I am sorry about multi posting. I will not do that in the
future. Kinda new to this. Thanks for the patience. Doug, the problem I am
having is I have put in all the code to the step and when I open the form
that is linked to the table, I can make changes just fine, navigate away
from the records, navigate back to them. however, there are no changes being
stored.
 
1. Choose Compile from the Edit menu to ensure the code compiles okay.

2. Temporarily comment out the error handler at the top of each routine,
i.e.:
'On Error Goto ...

3. Create a break point in (say) AuditEditBegin(), by pressing F9 at the
first line after the declarations, i.e.:
Set db = ...

4. Now begin an edit in your form. When you go to save the record, if the
code is being called you will find yourself in break mode. Press F8 to
single-step through the code, and you can see what is executing. You can
also pause the mouse over any variable to see its value at that time.

Follow that process to trace what is happening. The only problems reported
from this routine have been if the tables are not set up correctly, or if
the DAO reference is missing.
 
Doug, I followed your steps and here is what I come up with. I F8 in to the
procedure as it is being called on the AudEditBegin. This walks through all
that part and continues to the Networkusername. When it comes to the end of
the Networkusername function, it errors out saying "Runtime Error '3601'
there are too few Parameters. Expect 1." Any way I need to look from here?
 
Which line produces the error?

The code calls the error logging function explained in:
http://allenbrowne.com/ser-23a.html
Did you include that as well?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

jacob said:
Doug, I followed your steps and here is what I come up with. I F8 in to the
procedure as it is being called on the AudEditBegin. This walks through all
that part and continues to the Networkusername. When it comes to the end of
the Networkusername function, it errors out saying "Runtime Error '3601'
there are too few Parameters. Expect 1." Any way I need to look from
Click to expand...
here?
 
It stops on this line "db.Execute sSQL, dbFailOnError" And when I mouse
over the dbFailONError, I get "=128" And no, I have not yet implemented
the error Logging function
 
Good. You have traced it down now to the SQL statement not executing.

There is something wrong with your SQL statement as it stands. The mostly
likely cause is that your tables do not match exactly the same fields, of
the same type, in the same order, and with the temp fields as specified in
the article.

Debug.Print sSQL to see its contents, and then trace what is wrong with it.
 
I am sorry to be so annoying, but here is the sSQL that is supposedly
erroring out. I have checked and rechecked the spellings and cannot find
what is happening. Can you review and see if there is something that maybe I
have starred at too long?


sSQL = "INSERT INTO " & AudTmpInvoice & " ( audType, audDate,
audUser ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName()
AS Expr3, " & tblEquipment & ".* " & _
"FROM " & tblEquipment & " WHERE (" & tblEquipment & "." &
EquipmentID & " = " & lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
 
jacob, that's the line from the code.

Between the "sSQL = ..." line and the "db.Execute ..." line, enter:
Debug.Print sSQL

Run the code.
When it fails, open the Immediate window (Ctrl+G).
Copy the statement that printed there.
Create a new query.
Switch it to SQL View (View menu), and paste the statement in.
Try to execute the query.
Can you see what's wrong?
 
Ok, it is now looking for a DataSource. I am not hooked up through a data
connection. I simply have a front end and a back end stored on the network.
This is the line it printed that I made a query of...

INSERT INTO audTmpInvoice ( audType, audDate, audUser ) SELECT 'EditFrom' AS
Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, tblEquipment.* FROM
tblEquipment WHERE (tblEquipment.EquipmentID = 2823);

I copied that in to a new Query and when I ran it, it popped up a "Select
Data Source" box.
 
Well thanks Allen. I sure appreciate what you have done so far. I will just
research other ways of getting this done. I have completed everything step
by step as outlined and it still does not seem to work. If you happen to
think of something else from here, please let me know.


Thanks again
Jacob
 
Back
Top